I’ve worked with many SQL Server multi terabyte databases containing billions of rows running on Windows. With VLDB (Very Large DataBases), the performance almost always comes down to disk access in one way or another. Unfortunately Windows always creates the partition starting at the sixty-forth sector, therefore misaligning it with the underlying physical disk.
To make sure your disk alignment is correct, use diskpart.exe which is a disk partition tool. Diskpart.exe is a utility provided by Microsoft in the Windows Server 2003 Service Pack 1 Support Tools that can set the starting offset in the master boot record (MBR). By setting the starting offset, you can set the track alignment and improve disk performance. SQL Server writes data in multiples of 4 KB – 32 KB depending on your configuration, therefore, make sure that the starting offset is a multiple of 4 KB. If you don’t, you may cause a single I/O operation to span two tracks which will of course cause significant performance degradation.
Warning!
Diskpart is a data destructive utility. When used against a disk, all data on the disk will be wiped out during the storage track boundary alignment process. Therefore, if the disk on which you will run Diskpart contains data, backup the disk before performing the following procedure! We accept no responsibility for any loss of data!
Procedure
Using diskpart.exe to align Windows disk I/O with storage track boundaries
If the disk you are aligning is already blank (raw), carry on to Step 3, if the disk contains data, backup the disk before proceeding.
Delete all partitions on the disk.
Open a cmd prompt, and run diskpart.exe.
At the diskpart cmd prompt, type List Disk and hit Enter. If the disk you want to align doesn’t appear in the list, make sure it exists and it’s accessible using the Disk Management snap-in.
At the diskpart cmd prompt, type Select Disk X, where X is the number of the disk s shown in the output of the List Disk command.
Diskpart should return a message that shows Disk X is the selected disk.
At the diskpart cmd prompt, type Create Partition Primary Align=X, where X is either 32 or 64, if your storage vendor does not have any specific recommendations, we recommended you use 64.
At the diskpart cmd prompt, type Assign Letter=. E.g. to assign letter Z to the disk, type Assign Letter=Z.
Once the drive letter is assigned, type exit to exit out of the Diskpart utility.
Use the Disk Management snap-in or the Windows Format command to format the partition as an NTFS-formatted partition.
You’re now ready to move your database data and logs onto your new disk configurations
Maric Gregory
