ConfigMgr and SQL – NTFS allocation unit size | Quisitive

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.

Microsoft recommends this for User Databases and the TempDB:

Steve Thompson [MVP]:

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

Get-WmiObject -Namespace 'root\CIMv2' -Class Win32_Volume -Filter "DriveType = 3 and Label <> 'Recovery'" | Select Name, Label, BlockSize

OR from a Command Prompt

fsutil fsinfo ntfsinfo s: | findstr "Bytes per"

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

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.  Your mileage may vary though.


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.

Import-Module SQLPS
$SQLDrivesInUse = @(Invoke-Sqlcmd -Query 'SELECT DISTINCT left(physical_name,3) [Drive] from master.sys.master_files')
Write-Output 'SQL Drives In Use'
$SQLDrivesInUse | Format-Table -AutoSize

$SQLDriveDefaults = @(Invoke-Sqlcmd -Query "SELECT Left(Convert(varchar(255), SERVERPROPERTY('instancedefaultdatapath')),3) [Drive] UNION SELECT Left(Convert(varchar(255), SERVERPROPERTY('instancedefaultlogpath')),3)")
Write-Output 'SQL Drive Defaults'
$SQLDriveDefaults | Format-Table -AutoSize

$DiskDrives = @(Get-WmiObject -Namespace 'root\CIMv2' -Class Win32_Volume -Filter "DriveType = 3 and Label <> 'Recovery'" | Select Name, Label, BlockSize, ExpectedBlockSize, IsOK)
ForEach ($DiskDrive in $DiskDrives) {
   $SQLDrivesInUse.Drive | ForEach-Object {
      If ($_ -eq $DiskDrive.Name) { $DiskDrive.ExpectedBlockSize = 8*8*1024 }
   $SQLDriveDefaults.Drive | ForEach-Object {
      If ($_ -eq $DiskDrive.Name) { $DiskDrive.ExpectedBlockSize = 8*8*1024 }
   If ($DiskDrive.ExpectedBlockSize -eq $null) {
      $DiskDrive.ExpectedBlockSize = 4*1024
   If ($DiskDrive.BlockSize -eq $DiskDrive.ExpectedBlockSize) {
      $DiskDrive.IsOK = $true
   } else {
     $DiskDrive.IsOK = $false
$DiskDrives | Select Name, Label, BlockSize, ExpectedBlockSize, IsOK | Format-Table -AutoSize