Why Disk Configuration Is the Foundation of SQL Server Performance

Most SQL Server performance problems get blamed on queries, indexes, or memory. Often, the real culprit is sitting much further down the stack: the disk configuration. If your drives weren't partitioned and formatted correctly before SQL Server was installed, you're leaving performance on the table every single day, regardless of how well-tuned everything else is.

The short answer: SQL Server drives should be formatted with a 64KB allocation unit size, and disk partitions must be aligned to the correct offset (typically 1MB on modern systems) to avoid read/write penalties that degrade IO performance. Getting both of these right is one of the highest-value, lowest-cost optimisations you can make to a SQL Server environment.

What Is Partition Alignment and Why Does It Matter?

Partition alignment refers to where a disk partition starts on the physical storage medium. When a partition boundary doesn't align with the underlying storage geometry, a single SQL Server IO operation can span two physical sectors or RAID stripe units instead of one. This means the storage controller has to perform two physical reads or writes to satisfy what should be a single operation. At scale, this doubles your IO workload for no benefit.

On older systems (pre-Windows Vista / Windows Server 2008), Windows defaulted to a partition offset of 63 sectors, which was misaligned for virtually every RAID configuration and SAN storage array. This caused significant performance degradation that was often invisible unless you knew what to look for.

Modern Windows versions default to a 1MB partition offset, which aligns correctly with typical RAID stripe sizes (64KB, 128KB, 256KB). If you're running SQL Server on hardware that was provisioned before Windows Server 2008, or on a system migrated from older infrastructure, it's worth verifying the offset is correct.

What Is the Correct Allocation Unit Size for SQL Server?

The allocation unit size (also called the cluster size) controls the minimum amount of disk space Windows uses when writing a file. SQL Server reads and writes data in 8KB pages by default, but it performs IO in 64KB extents when doing bulk operations, checkpoint writes, read-ahead reads, and log flushes.

If your volume is formatted with the Windows default of 4KB allocation units, SQL Server's 64KB extent operations require 16 separate allocation units per write. Format with 64KB allocation units and you get a 1:1 match. The result is fewer IO operations, better throughput, and reduced fragmentation at the filesystem level.

Microsoft's own documentation recommends 64KB allocation unit size for SQL Server data and log volumes. This recommendation applies to all storage types: local SAS/SATA, SAN-attached LUNs, and even many NVMe configurations.

How to Check Your Current Partition Offset and Allocation Unit Size

Before making any changes, verify the current state of your disks. You can do this without any third-party tools.

Check partition offset using WMIC:

wmic partition get BlockSize, StartingOffset, Name, Index

Divide the StartingOffset value by 4096 to confirm it's a whole number. If it is, the partition is aligned to 4KB boundaries. For modern storage, you also want it to be divisible by 65536 (64KB) or 1048576 (1MB). A StartingOffset of 1048576 (1MB) is the standard and correct value for most environments.

Check allocation unit size using fsutil:

fsutil fsinfo ntfsinfo D:

Look for the "Bytes Per Cluster" value. You want to see 65536 (which is 64KB). If you see 4096, the volume is using the default 4KB cluster size and should be reformatted.

Check allocation unit size using PowerShell:

Get-WmiObject -Class Win32_Volume | Select-Object Name, BlockSize | Sort-Object Name

A BlockSize of 65536 confirms correct 64KB formatting.

What Happens If the Configuration Is Wrong?

The performance impact varies depending on your workload and storage hardware, but misaligned partitions and incorrect cluster sizes consistently cause measurable problems.

On spinning disk (SAS/SATA), misaligned partitions can increase IO latency by 20-30% for random workloads. On SAN storage with RAID-5 configurations, the penalty is worse because misalignment triggers read-modify-write cycles at the RAID controller level. On modern NVMe and all-flash arrays, the impact is less severe but still present, particularly for sequential workloads.

Wrong allocation unit sizes affect throughput. A busy OLTP database with 4KB allocation units will generate significantly more filesystem-level IO than the same workload on a 64KB formatted volume, because SQL Server's extent-based writes are split across multiple clusters.

In practice, we've seen environments where fixing partition alignment and reformatting drives delivered a 15-25% reduction in disk IO wait times without any other changes. That's a meaningful improvement for a configuration change that takes less than an hour on a new system.

How to Fix Misaligned Partitions and Incorrect Cluster Sizes

The honest answer here is that fixing these issues on an existing, live volume is disruptive. You cannot reformat a volume with data on it, and you cannot change partition alignment without deleting and recreating the partition.

The correct remediation process is:

  1. Back up all data on the affected volume to a separate location.
  2. Delete the existing partition.
  3. Recreate the partition using diskpart with the correct offset: create partition primary offset=1024 (this sets a 1MB offset).
  4. Format the new volume with 64KB allocation units: format fs=ntfs unit=64K label="SQLData" quick
  5. Restore the data to the reformatted volume.
  6. Verify the configuration using the fsutil and wmic commands above.

For new SQL Server deployments, this should be part of the build checklist before SQL Server is installed. It's far easier to get it right during provisioning than to fix it after the fact.

If you're working with SAN or virtualised storage, coordinate with your storage team. Some SAN vendors have their own alignment requirements, and some virtualisation platforms (particularly older VMware configurations) add their own alignment considerations on top of the OS-level settings.

Does This Apply to SSDs and NVMe Drives?

Yes, though the impact is different. SSDs and NVMe devices don't have the rotational latency that makes misalignment so costly on spinning disk. However, most enterprise SSDs and NVMe drives still have internal page sizes of 4KB or larger, and RAID controllers managing SSD arrays still have stripe units. Misalignment can still cause write amplification and unnecessary controller overhead.

The recommendation remains the same: 1MB partition offset, 64KB allocation unit size for SQL Server volumes. There's no downside to correct configuration, and it ensures optimal performance regardless of the underlying storage technology.

Key Takeaways

  • SQL Server data and log volumes should be formatted with 64KB allocation unit size to match SQL Server's extent-based IO operations, reducing filesystem overhead.
  • Partition offset should be set to 1MB (1048576 bytes) to align with modern RAID stripe sizes and avoid split IO penalties.
  • Use fsutil fsinfo ntfsinfo and wmic partition get StartingOffset to verify your current configuration without third-party tools.
  • Fixing misaligned or incorrectly formatted volumes requires deleting and recreating the partition. It's disruptive to fix after the fact, so get it right during provisioning.
  • The performance benefit is real and measurable: environments with correct disk configuration consistently show lower IO wait times, particularly under heavy write workloads.

Disk configuration is one of those foundational items that rarely gets attention after a server is built, but it has a lasting impact on every IO operation SQL Server performs. If you're not sure whether your SQL Server environment is configured correctly, DBA Services includes disk alignment and allocation unit verification as part of our SQL Server Health Check. We'll identify configuration issues that are quietly degrading your performance and give you a clear remediation plan. Get in touch to find out more.