If you are using Windows Server drives dedicated for SQL Server database files, did you know that the recommended NTFS cluster size is 64KB? The reason that SQL Server is said to perform better when using 64KB NTFS cluster size is this happens to correspond to the way SQL Server allocates storage. One page = 8K, and SQL Server allocates 8 pages as an an Extent. 8 pages x 8KB = 64KB/extent, which is one 64KB NTFS cluster.
A default NTFS formatted drive (Windows 2008) uses 4KB clusters. How do you check the current drive cluster size on your drives?
There are two ways to check logical drives C & D. Open a command prompt as an administrator, copy/paste and run:
fsutil fsinfo ntfsinfo c:
fsutil fsinfo ntfsinfo d:
Note the “Bytes Per Cluster” part below for the cluster size:
D:\>fsutil fsinfo ntfsinfo c: NTFS Volume Serial Number : 0xa2060a7f060a54a7 Version : 3.1 Number Sectors : 0x00000000043c3f5f Total Clusters : 0x000000000008787e Free Clusters : 0x000000000008746e Total Reserved : 0x0000000000000000 Bytes Per Sector : 512 Bytes Per Cluster : 65536 Bytes Per FileRecord Segment : 1024 Clusters Per FileRecord Segment : 0 Mft Valid Data Length : 0x0000000000010000 Mft Start Lcn : 0x000000000000c000 Mft2 Start Lcn : 0x0000000000043c3f Mft Zone Start : 0x000000000000c000 Mft Zone End : 0x000000000001cf20
An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. If you are having disk related performance issues, you may want to consider reformatting your drives to NTFS 64KB cluster size for better performance.