Share via

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

Comments

  • Anonymous
    January 26, 2008
    PingBack from http://msdnrss.thecoderblogs.com/2008/01/26/improving-sql-disk-performance-1-partition-alignment/
  • Anonymous
    April 02, 2008
    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
  • Anonymous
    October 08, 2008
    Kevin talks about the little known but very important issue of disk alignment partitioning.
  • Anonymous
    October 08, 2008
    Kevin talks about the little known but very important issue of disk alignment partitioning.
  • Anonymous
    October 19, 2008
    Недавно Кевин Кляйн в очередной раз поднял тему выравнивания размеров кластера и блока, проблему, которая
  • Anonymous
    November 14, 2008
    Недавно Кевин Кляйн в очередной раз поднял тему выравнивания размеров кластера и блока, проблему, которая
  • Anonymous
    January 28, 2009
    Недавно Кевин Кляйн в очередной раз поднял тему выравнивания размеров кластера и блока, проблему, которая
  • Anonymous
    September 30, 2009
    wmic partition get BlockSize, StartingOffset, Name, Index