Improving SQL Disk Performance #1 Partition Alignment


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 http://technet2.microsoft.com/windowsserver/en/library/ca099518-dde5-4eac-a1f1-38eff6e3e5091033.mspx?mfr=true

Comments (8)

  1. Bennett Scharf says:

    Thank you for the posting.  That WMI script is really handy.  

    I found a couple of related articles that may be of interest:  

    For those using EMC SAN storage there is an engineering whitepaper titled "Using diskpar and diskpart to align partitions on Windows basic and dynamic disks".

    The Predeployment I/O Best Practices article references another Technet article that really gets into the specifics of aligning partitions: http://support.microsoft.com/kb/929491

  2. Kevin Kline says:

    Kevin talks about the little known but very important issue of disk alignment partitioning.

  3. Kevin Kline says:

    Kevin talks about the little known but very important issue of disk alignment partitioning.

  4. Недавно Кевин Кляйн в очередной раз поднял тему выравнивания размеров кластера и блока, проблему, которая

  5. Недавно Кевин Кляйн в очередной раз поднял тему выравнивания размеров кластера и блока, проблему, которая

  6. Недавно Кевин Кляйн в очередной раз поднял тему выравнивания размеров кластера и блока, проблему, которая

  7. Declan Corcoran says:

    wmic partition get BlockSize, StartingOffset, Name, Index