SQL Server 2005 - RAID Disk / Partition Setup - Optimal Block/Cluster Size

Posted on 2010-01-04
Last Modified: 2012-06-27
We're setting up a new SQL 2005 Server (replacing an existing server) and are examining options for optimal RAID configuration as well as partition settings for block/cluster sizes. Existing server is also SQL 2005, running on older hardware. We have basic perfmon specs for the existing server. They are as follows:

Partition C/D - OS, Applications, Swap
Average Bytes per Read: 3,514 B
Average Bytes per Write: 6,529 B

Partition E - Transaction Logs
Average Bytes per Read: 33,951 B
Average Bytes per Write:  1,271 B

Partition F - SQL Data
Average Bytes per Read: 42,980 B
Average Bytes per Write: 10,656 B

New Server is W2K8 Enterprise x64. Spec'ing the OS/Apps partition isn't critical ... we're more concerned about nailing the best RAID settings for performance, as well as block/cluster sizes for the SQL Log and Data drives. The Log File drive will be created with four physical disks. The SQL Data drive will have 14 disks.

Any thoughts on calculating the optimal settings would be greatly appreciated. Also, if you have answers to this question, you might also have some thoughts on MBR vs. GPT initializations.

Thanks for any input -
Question by:accucom
    LVL 60

    Expert Comment

    LVL 5

    Expert Comment

    It is subjective. For heavier writes, we use raid 0+1 or 10. For heavier reads, use raid 5. I like 0+1 for tempdb and tranlog drives, but raid 5 for data drives. Also, if these partitions are being newly created under Windows Server 2008 you're OK, but if they were created with Win Server 2003 you should verify that the partition alignment is correct.

    Brent Ozar put together a good read on the subject:
    LVL 6

    Accepted Solution

    There is no single answer.  First you need more data about your environment and SQL use.  Second, the numbers seem fishy...  Writes to logs are generally larger than 1KB.  You also need to knwo the ratios and rates.

    I would recommend you take some more measurements.  When are your peak load times?  What kinds of I/O is occuring then?  What are the most important elements to optimize for YOUR environment?  (Backups, OLTP application, query times for ad-hoc user lookups, etc...)  A set of Perfmon logs for the machine that covers the time window you are trying to tune for would help.

    Disk alignment and RAID array volume/controller cache tuning are key.  As would be cluster size settings and RAID type, but there is not enough provided yet to really give a 1,2,3,4... answer...

    Disk alignment is very important and discussed here:

    Lots of MS Whitepapers here for SQL 2008 that might fit your enviroment.

    Long detailed discussion of 2008 performance options here.  Read especially starting on pages 48/49:

    Depending on the RAID controller and the stripe size/segment size, you might be better off with a different number of data drives than 14.  Some RAID controllers with some applications do better with power of 2+P RAID5, while some are going to do better with RAID10.  You should test your RAID to see or follow the OEM SQL 2008 tuning guide.  All the big vendors provide them...

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Backup folder managed by open source CMS 2 37
    EXCH 2007 VM and DEDUPE 13 57
    Set the max value for a column 7 21
    Basics of NetApp 10 28
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
    This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
    This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now