SQL Server - Speed up your Disks!

  • Article

 

I've already blogged some time ago about Microsoft recommendation for  SQL Server Storage Best Practices.

 

Is there anything else you should be aware in order to maximise the performances of your disk subsystem?

 

Oh yeah. Volume Alignment (Or Sector Alignment) and NTFS Allocation Unit Size can provide significant benefits to your disk performances (30% throughput increase on average) and you should definitely consider implementing both on your box.

 

Volume Alignment (Or Sector Alignment)

 

Windows Xp, Windows Server 2000, Windows Server 2003, instantiate 63 hidden sectors in all new partitions.

 

As in a default situation we have:

  • NTFS default format size of 4KB.
  • 64 512B sectors > 32KB per track, so there are 8 format “chunks” per track.

 

This means that when we write the first data to a new partition we are actually writing some bytes in the first partition and some other on the next one!

Therefore, every time we are reading\writing our data, we need to access two tracks, and the mechanic of the disks is going to heavily impact on our performances!

 

To avoid this issue, yous hould then consider implementing Volume alignment, commonly referred to as sector alignment on the file system (NTFS) whenever a volume is created on a RAID device.

 

More info can be found at:

https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx#EPAAC

https://support.microsoft.com/default.aspx?scid=kb;EN-US;929491

https://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122,00.html#monitoring

 

 

NTFS Allocation Unit Size

 

When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb.

Be aware however, that using allocation unit sizes greater than 4 KB results in the inability to use NTFS compression on the volume.

 

 

Test Results

 

Following are the results of some tests performed on Latency & Duration on a RAID 10, 64KB file allocation unit.

  • 6 disks vs. 8 disks
  • Not Aligned vs. Aligned
  • 6 aligned disks performed as well as 8 non-aligned disks.
  • Thus, efficiencies of ~30% were achieved.

 

 

- Beatrice Nicolini -