SQL Server and Block Size: Allocation Unit, Sector Size, and Why 64 KB

Three Things Called "Block Size"

On top of that, SQL Server has its own units: a page is 8 KB, an extent is eight pages, so 64 KB. That is where the 64 KB number originally comes from.

What SQL Server Actually Reads and Writes

A common misunderstanding is that a 64 KB allocation unit makes SQL Server do 64 KB I/O. It does not. The allocation unit only controls how NTFS hands out disk space to files. SQL Server decides its I/O sizes itself:

• Data file reads are single 8 KB pages for lookups, and much larger requests (whole extents and more) for scans and read-ahead.
• Data file writes from checkpoint and lazy writer are grouped pages, often much larger than 8 KB.
• Transaction log writes vary from one sector up to 60 KB per log block, and are always aligned to the sector size of the log disk.
• Backup and restore use large sequential I/O, typically 1 MB or more per request.

So the I/O pattern is mixed, mostly in multiples of 8 KB and 64 KB, and often larger. A file system that allocates in 64 KB clusters fits that pattern well. A file system with 4 KB clusters also works, but manages 16 times as many clusters for the same database file.

Why 64 KB Is Still the Right Default

Microsoft recommends a 64 KB allocation unit size for volumes holding SQL Server data, log and tempdb files. The Azure SQL VM best-practices checklist says the same for every data disk, and SQL Server images from Azure Marketplace come with data disks formatted at 64 KB, with the Storage Spaces interleave set to 64 KB too.

The reasons, in order of practical importance:

1. It costs nothing at format time and a lot later. The allocation unit can only be changed by reformatting the volume, which means moving every database off it first. Getting it right before the first database lands there is free.
2. Larger maximum volume size. With 4 KB clusters, an NTFS volume is limited to 16 TB. With 64 KB clusters, the limit is 256 TB. Data volumes on large servers do reach 16 TB, and you do not want to discover the limit when a volume extension fails.
3. Less file-system overhead. Fewer, larger clusters mean fewer allocations when files grow and less file-system fragmentation for large, growing database files.
4. Consistency. Every SQL volume formatted the same way removes one variable when comparing performance between servers.

Which drives

• 64 KB: data, log and tempdb volumes, and backup volumes.
• Leave the default (4 KB): the OS drive. On Azure VMs the temporary disk (D:\) comes formatted at 4 KB; Microsoft's checklist explicitly excludes it from the 64 KB recommendation.

Large FRS: the other formatting switch

Very large, heavily fragmented files can run into an NTFS metadata limit. With SQL Server this typically shows up as operating system error 665 ("the requested operation could not be completed due to a file system limitation"), for example on the sparse files of database snapshots that DBCC CHECKDB creates. Formatting with large file record segments avoids it:

# Destroys all data on F:. Only for a new, empty volume.
Format-Volume -DriveLetter F -FileSystem NTFS -AllocationUnitSize 65536 `
              -UseLargeFRS -NewFileSystemLabel 'SQLData'

Sector Size: The Layer You Don't Choose but Must Check

The sector size is the smallest unit the disk reads or writes atomically. SQL Server supports exactly two: 512 bytes and 4 KB. Both are fine, with two caveats.

Mixed sector sizes between replicas

The transaction log is written in sector-aligned blocks. If the primary replica's log disk has 512-byte sectors and a secondary's has 4 KB sectors (in an Availability Group or with log shipping), the secondary has to handle log blocks that do not match its sector size, and synchronization slows down. Microsoft describes this in KB3009974; trace flag 1800 makes SQL Server write 4 KB-aligned log blocks on the 512-byte side. The better fix is to give all replicas the same sector size from the start, which is one more reason to check it before you build an AG.

Sector sizes larger than 4 KB

No released version of SQL Server supports sector sizes above 4 KB. Some newer NVMe devices report 8 KB or 16 KB, and with them SQL Server setup fails or an existing instance refuses to start. This is not theoretical: several current Azure VM series expose 8 KB sectors by default. The full story, symptoms and fix are in An Azure VM with 8 KB Sector Size Can Break SQL Server.

Stripe Size and Partition Alignment

When several disks are combined (RAID, SAN LUN, Storage Spaces), the stripe size (interleave) decides how much data goes to one disk before the next one takes over. For SQL Server, 64 KB or a multiple of it is the usual choice, so a 64 KB extent does not straddle two disks. On Azure with Storage Spaces, Marketplace images use a 64 KB interleave.

Partition alignment used to be the big performance trap: partitions created by Windows Server 2003 started at sector 63, so every cluster crossed a stripe boundary. Since Windows Server 2008, partitions start at 1 MB by default, which is aligned to any common stripe size. Check it anyway on old or cloned volumes: the partition offset divided by 65536 should be a whole number.

How to Check Your Servers

On one server

# Allocation unit size per volume
Get-Volume | Where-Object DriveLetter |
    Select-Object DriveLetter, FileSystemLabel, FileSystem, AllocationUnitSize

# Logical and physical sector size per disk
Get-PhysicalDisk | Select-Object FriendlyName, BusType, LogicalSectorSize, PhysicalSectorSize

# Partition offset (should be divisible by 65536)
Get-Partition | Where-Object DriveLetter | Select-Object DriveLetter, Offset

The classic tool still works too, from an elevated prompt: fsutil fsinfo ntfsinfo F: shows "Bytes Per Cluster", and fsutil fsinfo sectorinfo F: shows the sector sizes as the storage stack reports them. Sample output from a workstation with an NVMe SSD:

DriveLetter FileSystemLabel FileSystem AllocationUnitSize
----------- --------------- ---------- ------------------
          C Windows         NTFS                     4096

FriendlyName      BusType LogicalSectorSize PhysicalSectorSize
------------      ------- ----------------- ------------------
NVMe SSD 1TB      NVMe                  512               4096

That is a typical 512e device (512-byte logical, 4 KB physical sectors) with the default 4 KB clusters: fine for an OS drive, not what you want for a SQL data volume.

Across many servers with dbatools

# Reports every volume and whether it uses 64 KB clusters
Test-DbaDiskAllocation -ComputerName SQL01, SQL02, SQL03 |
    Where-Object IsBestPractice -eq $false

# Block size together with free space
Get-DbaDiskSpace -ComputerName SQL01 | Select-Object ComputerName, Name, Label, BlockSize, Capacity, Free

Test-DbaDiskAllocation also flags the OS drive as "not best practice"; filter out C: (and the Azure temp disk) before turning the result into a to-do list.

Changing It on an Existing Volume

There is no in-place conversion. The way out is always: create a new volume formatted with 64 KB, move the files, retire the old volume. For user databases that means detaching and attaching, a backup and restore, or ALTER DATABASE ... MODIFY FILE plus taking the database offline while the files are copied. System databases and tempdb have their own move procedures. Plan it like any other storage migration, and do it when you replace the storage anyway, not as a standalone project for a few percent of performance.

The Bottom Line

When people say "block size" for SQL Server, they mean the NTFS allocation unit size, and the answer is 64 KB for data, log, tempdb and backup volumes. It is free when you format, expensive to fix later, and lifts the volume size limit from 16 TB to 256 TB. The sector size is the layer you do not choose but must check: 512 bytes or 4 KB are supported, mixed sizes across AG replicas slow down synchronization, and anything above 4 KB stops SQL Server from starting at all. Check both on every new server before the first database is created.