The Recommended NTFS Cluster Size for SQL Server

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.

disk fire

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:

or

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.

Advertisements

1 thought on “The Recommended NTFS Cluster Size for SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s