Jul 28

Written by: Stephen Frost
28/07/2010 8:33 AM 

One of the production Microsoft Server 2003 R2 boxes at work (a Dell R610 with 8GB RAM running Microsoft SQL Server 2005) had a C: drive with a RAID1 mirrored pair of 15,000rpm 73GB SAS drives and a D: drive with a RAID5 array of 4 x 10,000rpm 300GB SAS drives.  To determine whether the partitions were correctly aligned, I referred to the Microsoft "Disk Partition Best Practices for SQL Server" guide (see: http://msdn.microsoft.com/en-us/library/dd758814(SQL.100).aspx).  After running the recommended commands:

 

    wmic partition get BlockSize, StartingOffset, Name, Index
    fsutil fsinfo ntfsinfo d:

 

and getting the RAID stripe unit size from Dell's Open Manage software (the stripe was 64KB) I was able to use the calculations:

 

    Partition_Offset / Stripe_Unit_Size

    Stripe_Unit_Size / File_Allocation_Unit_Size

 

and found that I was getting a non-integer value.  This indicates partition misalignment.  It was caused by the partition starting at offset 32,256 bytes (sector 63).  I also found that the disk cluster size was 4KB and not the SQL recommended value of 64KB.  Our process for correcting the misalignment was as follows:

1. check that we had good backups from the night before!
2. reboot server and run benchmarks (raw hard disk performance, SQL reports)
3. take SQL databases offline
4. stop all SQL services
5. take backup of all D: drive data on to the C: drive (using Windows Backup)
6. use DISKPART to delete old D: partition
7. use DISKPART to create new D: partition
   1. stripe size was 64KB (as shown in Dell Open Manage)
   2. offset of new partition to be 1024KB
   3. cluster size to be 64KB (see next format command)
8. format drive D:    format d: /FS:ntfs /V:Data /A:64K
9. check alignment now correct::  wmic partition get BlockSize, StartingOffset, Name, Index
10. restore data from backup
11. restart SQL services
12. bring databases back online
13. reboot server and rerun benchmarks

After this process, which took 2-3 hours, our benchmark of raw disk performance (made using the freeware version of HD Tune) showed an increase of 14%.  SQL Server performance (a combination of raw disk and new cluster size) improved 36%.