Tuesday, June 23, 2009

Disk alignment best practices for SQL Server

There comes a time for most developers when they are in a project without a DBA and they are stuck as the involuntary DBA (Paul Randal has a great blog post about the matter), yes, there are those cases. In fact for most small to mid-sized applications there’s a chance that you will end up in that position.
I was recently given the task to set up a new SQL server instance and decided to get it right from the start. I had earlier listened to several Run As Radio shows where they talked about the importance of disk arrays and the way disks are partitioned. The way the sectors and stripes are configured from start is of major significance to the performance that the SQL server will be able to give. There is no way of using code to get around the fact that the sectors in the spindles are misaligned. This paper written by Jimmy May, Denny Lee explains the basics of disk alignment and gives you some tips of how to set up your system before you start installing.

The whitepaper gives two small equations on how to know if you disks are misaligned, when you use this two equations the result should be an integer, if not, then you have a misaligned drive

Partition_Offset ÷ Stripe_Unit_Size

Stripe_Unit_Size ÷ File_Allocation_Unit_Size

“Of the two, the first is by far the most important for optimal performance. The following demonstrates a common misalignment scenario: Given a starting partition offset for 32,256 bytes (31.5 KB) and stripe unit size of 65,536 bytes (64 KB), the result is 0.4921875. This is not an integer; therefore the offset & strip unit size are not correlated. This is consistent with misalignment.
However, a starting partition offset of 1,048,576 bytes (1 MB) and a stripe unit size of 65,536 bytes produces a result of exactly 8, an exact integer, which is consistent with alignment.”

To get the first part, the offset write this line into the console

wmic partition get BlockSize, StartingOffset, Name, Index

The second part of the equation is harder to get. Windows it self does not provide a good way to get the stripe unit size. To get the actual numbers you'll have to check with your vendor.

On the other hand, the File allocation unit size can be found by typing

fsutil fsinfo ntfsinfo c:
You will have to do this for each drive.After you know this then you know if you need to repartition the drive to the recommended cluster size for SQL server of 64KB.

Check out the links and resources in the white paper and Paul Randal’s blog for more information.