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

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 -
accucomAsked:
Who is Participating?
 
CoreyMacCommented:
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:
http://msdn.microsoft.com/en-us/library/dd758814.aspx

Lots of MS Whitepapers here for SQL 2008 that might fit your enviroment.
http://msdn.microsoft.com/en-us/library/dd631807(SQL.10).aspx

Long detailed discussion of 2008 performance options here.  Read especially starting on pages 48/49:
http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx

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...
0
 
chapmandewCommented:
0
 
dbidbaCommented:
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:
http://www.brentozar.com/archive/2009/12/sql-university-where-we-store-stuff/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.