?
Solved

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

Posted on 2010-01-04
4
Medium Priority
?
1,247 Views
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 -
0
Comment
Question by:accucom
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 26175944
0
 
LVL 5

Expert Comment

by:dbidba
ID: 26177396
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
 
LVL 6

Accepted Solution

by:
CoreyMac earned 2000 total points
ID: 26395673
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

571 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