Set the NTFS allocation unit size to 64 KB - AWS Prescriptive Guidance

Set the NTFS allocation unit size to 64 KB

The atomic unit of storage in SQL Server is a page, which is 8 KB in size. Eight physically contiguous pages make up an extent (which is 64 KB in size). SQL Server uses extents to store data. Therefore, on a SQL Server machine, the NTFS allocation unit size for hosting SQL database files (including tempdb) should be 64 KB.

To check the cluster (NTFS allocation) size of your drives, you can use PowerShell or the command line.

Using PowerShell:

Get-wmiObject -Class win32_volume | Select-object Label, BlockSize | Format-Table –AutoSize

The following illustration shows example output from PowerShell.

Checking NTFS allocation size: output from PowerShell

Or use:

$wmiQuery = "SELECT Name, Label, BlockSize FROM win32_volume WHERE FileSystem='NTFS'" 
Get-wmiObject -Query $wmiQuery -ComputerName '.' | Sort-Object Name | Select-Object Name, Label, BlockSize

Using the command line:

$ fsutil fsinfo ntfsinfo C:

The following illustration shows example output from the command line. The Bytes Per Cluster value displays the format size in bytes. The example output shows 4096 bytes. For the drives that host SQL Server database files, this value should be 64 KB.

Checking NTFS allocation size: output from the command line

In some cases, SQL Server performance doesn’t depend on the block size when you use SSD storage on Amazon EC2. For more information, see the blog post Do AWS customers benefit from 64KB block size for SQL Server storage?