How to improve SQL Server’s IO performance by up to 40%

In my last article, I started off talking about checking various settings that make a performance difference prior to jumping into query tuning. This article continues the investigation into IO performance affecting settings, by jumping straight into the storage subsystem. How would you like to make a single change to your drives and have the IO operations improve by up to 40%?

Just reformat the drives that contain your database data and log files.

If you are running on a version of Windows prior to Windows Server 2008, or if the drives were migrated to a new system without reformatting them, then you really could improve IO performance simply by reformatting the drives. Older versions of Windows will create disk partitions that default to having the boundaries based upon the ancient Cylinder/Head/Sector addressing scheme that was common in disk controllers of that era. Once these boundaries are set, the only way to change them is to reformat the drive, so migrating this drive to a Windows Server 2008 or newer server alone does not fix the issue.

How reformatting the drive affects IO performance

File Allocation Size

On the hard drive, the minimum amount of data that can be read or written is called a sector, which historically was a mere 512 bytes (newer drives may have sector sizes up to 4KB). A group of sectors is a cluster (no not that type of cluster). A drive with a 64KB cluster size (at 512 bytes per cluster) will have 128 sectors, which is also known as the file allocation size. The first 63 sectors of a drive are reserved, or hidden, and include the master boot record (MBR). Simply multiplying the 63 sectors by the sector size of 512 bytes shows that this hidden area occupies 32,256 bytes, or 31.5KB. The drive dutifully starts storing data immediately after this.

SQL Server IO operations

Now, consider how SQL Server performs disk IO operations – an extent at a time (an extent is 8 pages, each of which are 8KB, for a total of 64KB). To store this information on disk, with the default settings, this 64KB will start immediately after the 31.5KB and continue for 64KB… and it will span two clusters. When the drive goes to read this data, it will have to read the first cluster, and then the second cluster, in order to get all of the data needed for the extent being read from disk. When the next extent is read, it will re-read the second cluster, and read the third cluster. The wasted IO operation should be obvious.

How to have SQL Server work better with disk

What we need to do is to offset the beginning of the data being stored on disk to a location more conducive to how the program is operating. This offset is known as the “Partition Alignment Offset”. To be in tune with SQL Server, this value should be an increment of 64KB. However, you also need to consider the entire storage subsystem – the disks, controllers and memory. Starting with Windows Server 2008, this offset is at 1024KB – a nice increment of 64KB that also works very nicely with most RAID disks/controllers. Prior to Windows Server 2008, partition alignment offset was not explicitly performed, so this will need to be performed.

Determining a drive’s partition alignment offset

To determine what the partition alignment offset is for a windows basic disk, there is a very simple wmic command to run:

Source code

wmic partition GET BlockSize, StartingOffset, Name, INDEX

The Index value is the same as the disk number reported in the Disk Management tool, and also from the wmic command wmic Volume. However, we tend to think in terms of drive letters, not drive indexes, so the following PowerShell script will return Partition Alignment Offset for all drives on the system that it is run on and perform the mapping from index to drive letter:

Source code

#
# -------------------------------------------------------------------------------
# Partition Information
# -------------------------------------------------------------------------------
#                                MODIFICATION LOG
# -------------------------------------------------------------------------------
# 2012-08-09 WGS Initial Creation.
# 2012-08-11 WGS Modified TO WORK WITH PowerShell v1.
# 2013-02-05 WGS Modified TO GET Drive Letter.
# 2014-01-27 WGS Modified TO RETURN volume blocksize.
# -------------------------------------------------------------------------------
#
<#
.PARAMETER OpenInNotepad
VALUE passed AS $True or $False. Defaults TO $False.
#>
 
param
(
[BOOLEAN] $OpenInNotepad = $false
)
 
cls
 
FUNCTION Get-DriveLetter($PartPath) {
#Get the logical DISK mapping
$LogicalDisks = Get-WMIObject Win32_LogicalDiskToPartition | `
Where-OBJECT {$_.Antecedent -eq $PartPath}
$LogicalDrive = Get-WMIObject Win32_LogicalDisk | `
Where-OBJECT {$_.__PATH -eq $LogicalDisks.Dependent}
$LogicalDrive.DeviceID
}
 
FUNCTION Get-VolumeBlockSize($PartPath) {
$Drive = Get-DriveLetter($PartPath)
IF ($Drive -gt "") {
#Get the BlockSize OF the volume
$Volume = Get-WMIObject Win32_Volume | `
Where-OBJECT {$_.DriveLetter -eq  $Drive}
$Volume.BlockSize
}
}
 
FUNCTION Get-PartitionAlignment {
Get-WMIObject Win32_DiskPartition | `
Sort-OBJECT DiskIndex, INDEX | `
Select-OBJECT -Property `
@{Expression = {$_.DiskIndex};Label="Disk"},`
@{Expression = {$_.INDEX};Label="Partition"},`
@{Expression = {Get-DriveLetter($_.__PATH)};Label="Drive"},`
@{Expression = {$_.BootPartition};Label="BootPartition"},`
@{Expression = {"{0:N3}" -f ($_.SIZE/1Gb)};Label="Size_GB"},`
@{Expression = {"{0:N0}" -f ($_.BlockSize)};Label="Partition_BlockSize"},`
@{Expression = {Get-VolumeBlockSize($_.__PATH)};Label="Volume_BlockSize"},
@{Expression = {"{0:N0}" -f ($_.StartingOffset/1Kb)};Label="Offset_KB"},`
@{Expression = {"{0:N0}" -f ($_.StartingOffset/$_.BlockSize)}; Label="OffsetSectors"},`
@{Expression = {IF (($_.StartingOffset % 64KB) -EQ 0) {" Yes"} ELSE {"  No"}};Label="64KB"}
}
 
 
# Hash TABLE TO SET the alignment OF the properties in the format-TABLE
$b = `
@{Expression = {$_.DISK};Label="Disk"},`
@{Expression = {$_.Partition};Label="Partition"},`
@{Expression = {$_.Drive};Label="Drive"},`
@{Expression = {$_.BootPartition};Label="BootPartition"},`
@{Expression = {"{0:N3}" -f ($_.Size_GB)};Label="Size_GB";align="right"},`
@{Expression = {"{0:N0}" -f ($_.Partition_BlockSize)};Label="PartitionBlockSize";align="right"},`
@{Expression = {"{0:N0}" -f ($_.Volume_BlockSize)};Label="VolumeBlockSize";align="right"},`
@{Expression = {"{0:N0}" -f ($_.Offset_KB)};Label="Offset_KB";align="right"},`
@{Expression = {"{0:N0}" -f ($_.OffsetSectors)};Label="OffsetSectors";align="right"},`
@{Expression = {$_.{64KB}};Label="64KB"}
 
 
$a = Get-PartitionAlignment
 
 
# Display formatted DATA ON the screen
$a | Sort-OBJECT Drive, DISK, Partition | Format-TABLE $b -AutoSize
 
IF ($OpenInNotepad -eq $true) {
# Export TO a pipe-delimited FILE
$a | Sort-OBJECT Drive, DISK, Partition | Export-CSV $ENV:temp\PartInfo.txt -Delimiter "|" -NoTypeInformation
 
# OPEN the FILE in NotePad
Notepad $ENV:temp\PartInfo.txt
}

If you want to open the results in Notepad, pass in the parameter $True.

You may note that I said that using these wmic commands is for a Windows basic disk. If you are using a dynamic disk, you need to use alternative methods. With Windows Server 2008, you would use the tool diskdiag.exe (in earlier versions of Windows, this was called dmdiag). For either, you need to use the –V flag: dmdiag.exe –V or diskdiag.exe –V.

Changing the Partition Alignment Offset

To change the Partition Alignment Offset on Windows Server 2008 or higher, simply format the drive. On earlier systems, you will need to use the diskpart.exe tool to change the partition alignment offset, and then format the drive. In either case, this is a destructive operation – you will lose all data on the drive.

For more information on disk partition alignment, refer to this TechNet article: https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx.

These steps should yield a noticeable IO performance improvement. Test it for yourself and let me know what you experience.