Detecting volume/disk partition alignment issues

  • [アーティクル]

Hello everyone!

As a first practical post, today I will talk about disk partition alignment to configure storage system to achieve best performance out of SQL Server. It should not come as a surprise that tuning IO subsystem of any database engine is critical for building high performace database applications.

Modern storage systems (eg SAN, NAS) are very complex but in a nutshell they are just block devices. So, from Windows Operating System standpoint, they are just arrays of storage blocks. Historically, however, when Windows OS creates a new partition it allocates first 63 hidden sectors (31.5 KB for a default NTFS sector size of 512 bytes)  which may contain things like Master Boot Record (MBR). This means that useable portion of disk partition actually starts after these 63 sectors. Traditionally, disk track is 64 sectors, which means that 63 hidden sectors do not take the whole track. When write to disk happens, last 64th sector will be written first, then reamaining bytes will be written to other tracks. Same happens for reads. So single write/read will span two tracks. If you are using RAID arrays this translates to accessing two different stripe units on two separate disks. As you might expect, this leads to decreased overal IO performance. Microsot documents this problem for Windows 2000 and 2003 servers in KB article 929491. Windows 2008 server does not have it as its partitons are automatically aligned when created.

The solution to the problem is to perform disk partition alignment so that starting offset of the partition is multiple of sector size or the stripe unit size if you are using RAID. 

To check alignment of partitions use the following wmi command

wmic partition get BlockSize, StartingOffset, Name, Index

or the following VBScript (copy and paste this into nodepad and save file with extension .vbs and run from command line).

 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

and check the values for partitions where your data and log files reside (these should be separate!). Ask your storage engineers what the sripe unit size of underlying storage is if you are using RAID. It also worthwile mentioning here that for optimal performance of SQL Server partition should be formatted using block size of 64 KB. If starting offset of your data and log partitions are not multiple of stripe unit size then you have alignment problem. By fixing it you can easily boost performance of your IO subsystem by 15% or more. Unfortunately, this can not be remedied while SQL Server is running online and you have to take your databases offline, back them up and redo your storage subsystem. Fortunately, it does not take that long and once done your database application will perform faster.

Most customers, I worked with do not know about this issue. So, if you are running SQL Server on Windows 2000 or Windows 2003 servers, I strongly encourage you to find 15 minutes and check alignment of partitions where your existing databases live using scripts above.

KB article 929491 suggest detailed steps to remedy the problem. I also recommend you perform partition alignment before you deploy future pojects using SQL Server product.

If you want to know even more details about it please read Jimmy May's post which also includes PowerPoint presentation.

Predeployment IO best practices for SQL Server are well-documented in this white paper from SQL Server Customer Advisory team.

I hope you find this information useful and practical to your environment.

Until next time...

Comments