ConfigMgr and SQL – NTFS allocation unit size

No Gravatar

It’s been many years since I read that SQL databases should use an NTFS volume formatted with at 64KB file allocation unit size (block size). So long that I didn’t remember why or if it is still considered best/good practice. It appears that it is according to Microsoft and the foremost authority on SQL with ConfigMgr.

keep reading at http://blogs.catapultsystems.com/chsimmons/archive/2016/12/23/configmgr-and-sql-ntfs-allocation-unit-size

Microsoft recommends this for User Databases and the TempDB: https://docs.microsoft.com/en-gb/azure/virtual-machines/virtual-machines-windows-sql-performance?toc=%2fazure%2fvirtual-machines%2fwindows%2ftoc.json

Steve Thompson [MVP]: https://stevethompsonmvp.wordpress.com/2014/07/25/sql-server-ntfs-cluster-size

Steve explains, “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.”

To check the Block Size per drive/volume/partition…

From PowerShell, execute

image

OR from a Command Prompt

clip_image001

OR create a small file on the drive(s) and check it’s properties.  This can easily be done by…

  • open Notepad, hold any key for about 30 seconds, then save the file
  • open a Command Prompt and type  FOR /L %I (1,1,200) DO @echo %I>> %temp%\test.file  (or similar)

Using Windows Explorer, right-click the test file and notice the Size and Size on Disk info.

From testing, the file had to be more than 500 bytes on a 4K block size volume to register any size on the disk.  On the 64K block size disk it took about 800 bytes to register.  You mileage may vary though.

image

In addition, if the SQL PowerShell module installed or there is some other method to query the existing SQL server, PowerShell can check if the block size on all drives where a SQL files exist or will exist if using the default file locations.

 

image

December 23, 2016

Posted In: ConfigMgr 2012, Scripting, T-SQL