ConfigMgr and SQL – NTFS allocation unit size

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: https://docs.microsoft.com/en-gb/azure/virtual-machines/virtual-machines-windows-sql-performance?toc=%2fazure%2fvirtual-machines%2fwindows%2ftoc.json

[read-more-redirect urltext=”CatapultSystems.com” url=”https://www.catapultsystems.com/blogs/configmgr-and-sql-ntfs-allocation-unit-size”]

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

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 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.

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

 

[/read-more-redirect]