How to Improve Application and Database Performance up to 40% in One Easy Step

For some reason, the concept of disk alignment partitioning is not widely known in the SQL Server world and yet it can easily yield between 30-40% better performance in most database applications and much higher performance improvements for ETL applications.  (Also for some reason, Exchange people seem to know about this much better than SQL Server people do.)  You can see imperical evidence of this performance hit at Linchi Shea's blog here.

Now before you begin thinking that this has something to do with SANs or with SQL Server partitioning (added in SQL Server 2005), think again.  This is actually a server OS problem that applies to all of Microsoft's pre-Windows Server 2008 server OS'es, plus Windows XP, on both 32- and 64-bit architectures.  An oversimplified explanation is that these server OS'es like to write data in 64k chunks onto disks with 64k sectors.  However, the OS'es create their very first chunk at only 63k in size.  That means every subsequent chuck writes at least 1k to the previous sector, resulting in every read and write going to two sectors and resulting in two I/Os per operation instead of one I/O.

If you've never fixed the problem then your databases running on Windows Server 2000 or Windows Server 2003 are giving up 30-40% in performance without any idea that it's happening.  On the other hand, fixing the problem isn't difficult.  You can fix this problem by issuing a simple command - DISKPAR.EXE for Windows Server 2000 or DISKPART.EXE for Windows Server 2003.  (I've only scratched the surface here.  To fully understand the problem as well as see syntax for these commands, read Predeployment I/O Best Practices and this Microsoft Knowledge Base article.  DISKPART.EXE is also explained here on TechNet.)  When you realign your disks with DISKPAR or DISKPART, the disks will be wiped clean.  So you need to fully backup your databases first.  Once the disks are realigned, you can restore your databases to them.  Arrays configured under Windows Server 2000/2003 will remain misaligned even if you later upgrade the OS to Windows Server 2008.  You'll have to manually rebuild them using Windows Server 2008 or realign them with DISKPART to get the disks running with the correct sector alignment.

I encourage you to look at your disk subsystem very closely to make sure you're not missing out on this important performance improvement opportunity.  You can use the DISKPART -i command to see what the current sector offset is or you can use the WMI object written by Bob Duffy located here.

So get in there and realign all of those misaligned disks!

Enjoy,

-Kevin