Improving SQL Disk Performance #2 Windows Stripe Size

  • Article

There are some urban legends around the Windows stripe size that you should set for SQL Server. I have seen recommendations of 64k for both data and log internally at Microsoft by some heavy hitting data Dudes, so that is good enough for me ;-)

Generally a Storage Area Network will have a 64k block size anyway - so I like to match the size of the windows stripe to the block size of the underlying disk subsystem.

How to Change the Windows Strip Size

This can only be selected when you format the disk. Also know as "Allocation Unit Size". beware as the default is only 4k.

One thing to be aware of is that NTFS compression is not supported with a block size of 64. But no one uses that with SQL Server - right !!

How to Query the Stripe Size?

fsutil fsinfo ntfsinfo F:

Sample output shows 4k windows format stripe size

NTFS Volume Serial Number : 0xe660d46a60d442cb
Number Sectors : 0x00000000010ea04f
Total Clusters : 0x000000000021d409
Free Clusters : 0x00000000000f222a
Total Reserved : 0x0000000000003550
Bytes Per Sector : 512
Bytes Per Cluster : 4096
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x0000000003eab000
Mft Start Lcn : 0x0000000000000004
Mft2 Start Lcn : 0x000000000010ea04
Mft Zone Start : 0x0000000000003ea0
Mft Zone End : 0x0000000000004700