Improving SQL Disk Performance #1 Partition Alignment

  • Article

There is a great article that you should read before configuring a disk subsystem for SQL Server. Predeployment I/O Best Practices.

As mentioned, one of the common pitfalls is not ensuring that the offset of the first block is "64k" or a multiple thereof.

Longhorn Server and Vista resolve this issue by using am offset of 1024. if you are using windows 2003 and this is incorrectly configured you could be loosing 40% plus of your disk i/o!

How to check partition offset (vbs script)

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2") Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM Win32_DiskPartition",,48)

For Each objItem in colItems    

Wscript.Echo "Disk: " & objItem.DiskIndex & " Partition: " & objItem.Index & " StartingOffset: " & objItem.StartingOffset/1024 & "KB"    

Wscript.Echo

Next

you can also use diskpart -i

How to fix an incorrect offset:

If you have Windows 2003 SP1m the diskpart.exe command has an extra parameter /align that can be used to ensure that the partition is aligned. See https://technet2.microsoft.com/windowsserver/en/library/ca099518-dde5-4eac-a1f1-38eff6e3e5091033.mspx?mfr=true