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.

Advertisements
This entry was posted in SQL Server, SQL Server 2012. Bookmark the permalink.

3 Responses to SQL Server NTFS Cluster Size

  1. Pingback: Installing and configuring SQL Server for Configuration Manager | Steve Thompson [MVP]

  2. Pingback: ConfigMgr and SQL – NTFS allocation unit size

  3. Pingback: ConfigMgr and SQL – NTFS allocation unit size – Chad's Tech

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