While there are many configuration options in SQL Server that can help tweak the performance when RAM or CPU is causing a slowdown, when the disk is the culprit, your options are much more limited. Assuming that best practices are followed in regards to file locations and tempdb optimizations, you may be left scratching your head while considering upgrading your hardware. What is not as well known is there are ways you can setup disk configuration optimized for for SQL Server. Microsoft released an article geared toward SQL 2008; however, the guidance is still valid for newer versions.
(https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx)
A single I/O request coming into an array controller can turn into multiple I/Os if the request crosses one or more stripe unit boundaries. In all cases, similar principles are at work: Due to misalignment clusters of data are written across physical boundaries requiring unintended, unnecessary I/Os that result in performance degradation.
Beginning in Server 2008, the operating system attempts to align the disks out of the box. This means that there is a high probability that the disks will be created with an offset that will align with the underlying raid stripe units. For new drives, this “auto-alignment” is great but is only half of the solution. If you have moved disks or used existing drives in a SAN, it is possible that even on newer operating systems the disks may not be aligned properly. Even in a virtual environment, disk alignment is important. Starting in VMware ESXI 5.0 automatically aligns the partitions along the 1MB boundary, however, if you have disks created in earlier versions it may not adhere to the same alignment specifications. If you are using a hosted solution you may be more limited in the changes you can affect. Regardless of whether the machine is physical, virtual, hosted or local, all of the items mentioned here should be verified and addressed.
There are two correlations which show if the disk is configured optimally for I/O performance. The result of these calculations must result in a whole number for optimal performance.
Partition_Offset ÷ Stripe_Unit_Size
Stripe_Unit_Size ÷ File_Allocation_Unit_Size
To find the starting offset (partition_Offset) of your drives, you can run the following command in a Command Prompt:
wmic partition get BlockSize, StartingOffset, Name, Index
For Dynamic disks, you can run the following command:
dmdiag -v
To get the stripe Unit size and the File Allocation Size, you can run the following for each drive:
fsutil fsinfo ntfsinfo c:
Windows does not have a reliable way to determine stripe unit sizes. These values can be obtained from vendor disk management software or from your SAN administrator. Dell will commonly use a 64KB stripe size as a default for its controllers, so this partition would be aligned assuming we have a 64KB stripe size. (1048576/65536 = 8) While having the drives aligned is important, the first of the 2 equations above being by far more critical than the second, we also want to adjust the file allocation unit size (Bytes per cluster) to coincide with the best practices as prescribed by Microsoft.
Microsoft recommends that the File allocation unit size (Bytes per cluster) be set to 64KB. For existing drives, this requires reformatting of the drive, setting the allocation unit size to 64KB. You can accomplish via the GUI formatting tool or you can use a command line.
Formatting your drive will destroy the data on the drive. Be sure to backup all of your files to another drive before formatting the original drive.
Microsoft states that an average response time of over 15 milliseconds indicates a problem that should be investigated
I recently ran into a situation where users were reporting slowness on one of the servers, and I determined that while the disks were aligned, the file allocation unit size was not set correctly. Below you can see the difference in the graphs for the before and after responses when setting the cluster size to 64KB per Microsoft’s recommendation.
Before Disk Configuration Optimized
After Disk Configuration Optimized
As you can see, this one setting brought the disks back into the range Microsoft sees as acceptable. In this particular case, making this change not only resolved the issues we were seeing, it also resulted in positive feedback. They stated it was much faster.
If you need more help Disk Configuration Optimized for SQL Server please get in touch with our Microsoft SQL Server DBA Experts today!