Does disk partition alignment matter to SQL Server? Without a doubt. When new disk partitions are created, there could be a reserved set of sectors that could differ across disks because of the way that the hardware interacts. In the white paper, Disk Partition Alignment Best Practices for SQL Server, there is an image that helps to explain this:
Starting with Windows 2008, the disk partitions are automatically aligned to help improve performance. In a decade, it is unlikely that this will be a problem for most systems as most of the installed systems will be running Windows 2008 or later, at least for SQL Server.
However now there are still lots of Windows 2003 and Windows 2000 systems out there at this time. For those systems, they could be experiencing a degradation of up to 30% according to the testing done by Microsoft. Which means that you can get a quick performance improvement in your systems, if they are disk I/O bound, if you can realign partitions.
How can you do this? The hard part is that you must move everything off the partition (all data), delete, and then recreate the partition and restore data. That can be a time consuming exercise, but it is really a time effort. It doesn’t cost anything if you have extra disk to hold your data and can handle the downtime. In the white paper, there is a section that explains how to align your partitions in Windows 2000 and Windows 2003 using diskpar.exe and diskpart.exe, respectively.
Note that this is mentioned in another white paper on SQL Server Best Practices. This gives you a number of I/O related pre-deployment best practices that you ought to consider before installing SQL Server on your systems. If you are building a system of any importance, this is a great article to understand, and apply many of these practices before SQL Server is installed.
This can cause a delay in the deployment of a new server, but performing these tests and establishing a baseline cannot be done later, and discovering potential problems early can help you to build a better performing server from day one. Finding these problems later will ultimately result in way more embarrassment and hassle than implementing a short delay before deployment.
If you have a group of people responsible for installing Windows and possibly SQL Server as part of your build process, have them review the article, or even give them a checklist that will help them to incorporate this testing and benchmarking into their routine.
It’s just a couple weeks away, on Super Bowl weekend. I can’t believe this is event #60, still in shock that this amazing franchise has grown to large.
