August 13, 2009 at 10:33 am
I've been attempting to learn as much as I can about properly configuring IO for SQL server on RAID/SAN configurations. I've read it's as much an art as a science and there certainly are many parameters to consider.
Does anyone know of a good book or article out there that clearly defines IO tuning process?
I'd like to be a master at this since I think IO is such a huge part of performance.
Some of those parameters I mentioned
1. Disk sector size
2. Stripe unit size - Must be obtained from SAN manager or possibly in OMSA for local RAIDs
3. Partition Offset - wmic partition get BlockSize, StartingOffset, Name, Index
4. File Allocation Unit Size (bytes per cluster) - fsutil fsinfo ntfsinfo [DriveLetter]:\
This setting, File Allocation Size, can be set to various settings. I'd like sort out best practices for LDF, MDF, tempDB, and BAK specific LUNs. I'm also interested in how to go about determining the best size for drives that are sharing various tasks.
To verify Partition Alignment, the results of the following calculations must result in an integer value:
Partition_Offset ÷ Stripe_Unit_Size
I've also see this version (Partition offset*Disk sector size) / Stripe unit size
Stripe_Unit_Size ÷ File_Allocation_Unit_Size
It looks like Windows Server 2008 handles partition alignment out-of-the-box.
RAID Configuration
One item of interest to me is the writes are discouraged on RAID 5's, yet all SAN configs I've see have been RAID 5 (6+1).
In addition there's configuration for SANs that can be done on the HBA connections including queue depth and cache ratios.
Here are some links on this topic for reference:
http://blogs.msdn.com/jimmymay/archive/2009/05/08/disk-partition-alignment-sector-alignment-make-the-case-with-this-template.aspx
http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
http://support.microsoft.com/kb/231619
http://support.microsoft.com/kb/929491
http://technet.microsoft.com/en-us/library/cc966412.aspx
http://technet.microsoft.com/en-us/library/cc966500.aspx
http://www.sqlhacks.com/index.php/Optimize/Server-Load
http://www.sqlservercentral.com/articles/Performance+Tuning/2573/[/url]
http://www.sqlservercentral.com/Forums/Topic474508-360-1.aspx
http://www.sqlteam.com/article/benchmarking-disk-io-performance-size-matters
August 14, 2009 at 12:02 am
Looks like you've got it covered 🙂
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 15, 2009 at 2:02 pm
Basically the SAN guys can give you some guidelines to follow. Or try DELL / EMC website.
If you have a SAN there are some tools on here for measuring the volume of what is hitting the san in throughput.
Or this can be done using permon. Once you determine the amount of disk io you can build your DISK IO on SAN
If you say one LUN is for 100IOPS and you are sending 1000IOPS there be the bottleneck right here.
Measure the current IOPS on SQL today and for a month to see where the problems lies. Once defined the volume you can then buy or build SAN Disks accordingly. Just because you have a SAN doesn't mean that is the final straw if it configured incorrectly SQL will run like slow.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply