This is the final post in the SQL Server Storage line of blog posts I’ve made. First we discussed Pages & Extents, then how to Read A Page using T-SQL, and finally this post will be about Disk Partition Offsets and Allocation.
Knowing that Pages are 8 KB and Extents are 64 KB, it’s understandable that SQL would have the best performance when the disks are aligned in the same manner. However, in earlier versions of Windows, this was never the case. Instead, Windows used an alignment configuration of 63 512 Byte sectors for a total of 31.5 KB. Since the way SQL Server reads and writes was not aligned with Windows, havoc ensued in the form of increased I/O operations. As of Windows 2008, partitions are aligned at 1024 KB by default, providing fewer I/O operations since 1024 is a multiple of 64.
I tried to keep the above explanation simple, but it still got wordy. The takeaway should be: you want SQL Server disks to have a Starting Offset that is a multiple of 64 KB, with the desirable default being 1024 KB. Higher values are fine for special circumstances, just so long as your Starting Offset is evenly divisible by 64.
As for your block size, that should also be 64 KB, or the size of a single Extent, to improve performance.
There is a single script to find both Block Size and Starting Offset, and it almost even works!
Get-WmiObject win32_DiskPartition | SELECT SystemName, Name, BlockSize, StartingOffSet | FT -Auto
That script returns the correct Starting Offset, but that Block Size is a screwy number that should not be trusted. To make matters worse, you get a Partition Number, but no Drive Letter. That doesn’t help me understand if my data disk is formatted correctly. In comes another query.
Get-WmiObject -Class win32_Volume |
FT DriveLetter, Label, BlockSize -Auto
Great, now we have an accurate BlockSize and a Drive Letter! The only problem is, now there is no Partition Number to match up with the OffSet from the first query. Getting ALL the information at one time is a pain. Luckily there are Custom Objects to help us smash results together and Win32_LogicalDisk to help us relate the drives and partitions.
$Report = @()
$Disks = Get-WMIObject Win32_logicaldisk | WHERE { $_.DriveType -eq '3' }
$Result = ForEach ( $Disk in $Disks)
{
$OffSet = Get-WmiObject -Query "Associators of {Win32_LogicalDisk.DeviceID='$($Disk.DeviceID)'} WHERE ResultRole=Antecedent"
$Report = [PSCustomObject] @{
ComputerName = $Disk.SystemName
DriveLetter = $Disk.DeviceID
Partition = $OffSet.Name
BlockSize = ( Get-WmiObject Win32_Volume | WHERE {$_.DriveLetter -eq "$($Disk.DeviceID"} | SELECT -expand BlockSize )
StartingOffSet = $OffSet.StartingOffSet
}
$Report
}
$Result | FT -Auto
Now you can determine the Block Size and Starting Offset of your drives easily. Changing those values, well that’s another story. Normally that will require formatting the drive, so its best to get it right in the first place. Make sure all your SQL Server drives are formatted correctly before you get started. Just educate your friendly Storage Admin on what SQL Server needs so everything is correct by the time you get it.
RAID 0 (Striping) This basic form of RAID stripes data across multiple disks. Reads and writes occur simultaneously across all disks involved and thus provides faster reads and writes compared to a single disk. The more disks involved, the faster the performance. This creates multiple points of failure though, and is not really recommended for database use due to the increased vulnerability.
RAID 1 (Mirroring) This RAID level mirrors, or duplicates, data between a minimum of two disks. Mirroring requires 50% more storage since the mirror is an exact copy of the original data. Read speeds are faster since any disk can respond to a read request. Write speeds are reduced due to copying the data to multiple locations. Read times can be as fast as the fastest drive, while write times are often as slow as the slowest drive. If you need a relatively cheap method to protect your data, this is a good option. If one drive fails, you still have a perfect copy of the data on the other.
RAID 5 (Striping & Parity) This is the likely the most common type of RAID used, but requires at least three disks. Data and parity calculations are striped across all the disks. Since data is not mirrored, less storage is ‘wasted’, resulting in only a minimum of 1/3rd (1 / total # of drives) of the storage space used for redundancy. In the event of a drive failure, the data can be reconstructed using the parity data, but at a cost. There is a significant impact when one disk fails due to the parity data reconstruction overhead. Losing the 2nd drive in a three disk RAID 5 configuration will result in the entire array going offline and data being lost. Always replace after the first failure as soon as possible! Since write speeds are slower with RAID 5, it is not the best choice for Transaction Logs or Data Files. Backup drives are a prime candidate for this RAID level though since write speed is not as important.
RAID 10 (Striping & Mirroring) Data is mirrored first and then striped with this method. As long as one drive from each side of the mirror remains, no outage will occur. With more fault tolerance, this is the preferred high-end method over RAID 0+1.
SQL Sanctum 
