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:
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:
If you are having disk related performance issues, you may want to consider reformatting your drives to NTFS 64KB cluster size for better performance.
Pingback: Installing and configuring SQL Server for Configuration Manager | Steve Thompson [MVP]
Pingback: ConfigMgr and SQL – NTFS allocation unit size
Pingback: ConfigMgr and SQL – NTFS allocation unit size – Chad's Tech
Pingback: ConfigMgr and SQL - NTFS allocation unit size - Catapult Systems