SQL Server NTFS Cluster Size

If you are using Windows Server drives dedicated for SQL Server database files, did you know that the NTFS cluster size should be 64KB?

The reason that SQL Server prefers 64KB NTFS cluster size, this happens to correspond to the way SQL Server allocates storage. One page = 8K, and SQL Server allocates an Extent, which is 8 pages in size. 8 pages x 8KB = 64KB/extent.

Now that we have the math out of the way…

Normally, a default NTFS, formatted drive is 4KB. How to check the current drive cluster size?

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 highlighted part below for the cluster size:

image

Another simple test, create a small text file on the disk, then open the file properties. The size on disk, will also indicate cluster size:

image

If you are having disk related performance issues, you may want to consider reformatting your drives to NTFS 64KB cluster size for better performance.

Filed under: SQL Server, SQL Server 2012

email

Written by , Posted .