SQL Server Disk Storage Best Practices: Layout, Formatting, File Settings and Latency

Most SQL Server performance problems that look like “the server is slow” end up at storage. Memory hides slow disks for a while, because pages already in the buffer pool don’t need to be read again. But every commit waits for a log write, every cache miss waits for a data read, and every tempdb spill, backup and CHECKDB competes for the same I/O. When storage is wrong, no amount of query tuning fully compensates.

This guide covers the decisions that matter: how to lay out data, log, tempdb and backup files, how to format the volumes, what changes on SAN, virtual machines and the cloud, the database file settings that cause most storage incidents, and how to measure whether your storage is actually fast enough.

How SQL Server Uses Storage

Different file types have very different I/O patterns, which is why they belong on different volumes:

The transaction log deserves special attention. A data page can be written later by a checkpoint, but a transaction can’t commit until its log records are on disk. Slow log writes therefore slow down every insert, update and delete, no matter how fast the data disks are. You’ll see this as the WRITELOG wait type.

Disk Layout: What to Separate

• Keep databases off the C: drive. A full system drive can stop Windows and SQL Server together, and system disks are rarely the fastest storage in the server.
• Separate data, log and tempdb volumes. It isolates their I/O patterns, makes latency easier to diagnose per file type, and stops one runaway file from filling the others’ space.
• Never store backups on the same storage as the databases. If that storage fails, you lose the databases and their backups together. Write backups to separate storage and copy them off the server.
• Put tempdb on the fastest local storage you have. Its contents are recreated at every restart, so local NVMe is a good fit, even in a failover cluster instance.

Formatting Volumes: The 64 KB Rule

SQL Server reads and writes in 8 KB pages and 64 KB extents. Format data, log and tempdb volumes with a 64 KB allocation unit size instead of the 4 KB Windows default. Check existing volumes before you trust them:

# Allocation unit size of every volume
Get-Volume | Where-Object DriveLetter |
  Select-Object DriveLetter, FileSystemLabel, FileSystem, AllocationUnitSize, SizeRemaining, Size

# Or for one volume: look for "Bytes Per Cluster : 65536"
fsutil fsinfo ntfsinfo E:

The allocation unit can’t be changed in place. To fix a volume formatted with 4 KB clusters, add a new correctly formatted volume, move the database files to it in a maintenance window, and retire the old one.

Partition alignment, a common problem on older systems, is handled automatically by Windows Server 2008 and later, which align new partitions at 1 MB. It only needs checking on volumes created long ago or by third-party tools.

RAID, SSD and Flash

• Use enterprise-grade SSD or NVMe for production. Consumer drives lack power-loss protection and have far lower write endurance.
• For direct-attached disks, RAID 10 is the safest choice for logs and write-heavy data. RAID 5 or 6 pays a write penalty that matters for logs and tempdb.
• On all-flash arrays the RAID level is usually chosen by the array. What matters to SQL Server is the measured latency at your peak load.
• Make sure the controller’s write cache is battery- or flash-backed. A volatile write cache can lose acknowledged writes on power failure, which SQL Server sees as corruption.

Virtual Machines and the Cloud

• VMware: put data, log and tempdb disks on separate paravirtual (PVSCSI) controllers instead of all on one, so they don’t share one queue.
• Hyper-V: use fixed-size or pass-through disks for heavy I/O, spread across multiple virtual SCSI controllers.
• Azure VMs: use premium SSD or Premium SSD v2 / Ultra disks, set host caching to read-only for data disks and to none for log disks, and size the VM for its storage throughput limit, not only its cores. A VM size can cap I/O well below what its disks could deliver.
• Snapshots and replication: storage-level snapshots aren’t backups unless they’re application-consistent and tested for restore.

Database File Settings That Cause Storage Incidents

Autogrowth

Set growth in fixed megabytes, never as a percentage. Percentage growth on a large file means huge, slow growth events; tiny fixed growth means thousands of fragments and, for logs, too many virtual log files. Size files for expected growth in advance, and treat autogrowth as a safety net. Our autogrowth best practices and VLF count guide cover the sizing in detail.

Instant file initialization

Without it, SQL Server writes zeros over every new data file region before using it, so creating, growing or restoring a large data file takes much longer. Grant the SQL Server service account the Perform volume maintenance tasks right to enable it. Transaction log files are always zeroed, with one exception: in SQL Server 2022 and later, log growth events up to 64 MB can use instant file initialization. See our instant file initialization guide.

-- Is instant file initialization enabled for the engine service?
SELECT servicename, service_account, instant_file_initialization_enabled
FROM sys.dm_server_services;

Page verification

Keep PAGE_VERIFY CHECKSUM on every database. It’s how SQL Server detects pages damaged by the storage stack, and it’s the default for databases created on SQL Server 2005 and later, but databases upgraded from very old versions may still use TORN_PAGE_DETECTION or NONE.

Antivirus exclusions

Exclude the folders that hold data, log, tempdb and backup files, and the SQL Server process, from real-time antivirus scanning. Scanning database files can cause I/O delays, failed growths and even corrupted backups.

Filegroups for Large Databases

For large databases, filegroups turn storage into a management tool:

• Put historical data in its own filegroup and mark it read-only. Back it up once, then only back up the active filegroups.
• Move cold filegroups to cheaper storage and keep hot data on flash.
• Keep the PRIMARY filegroup small. In Enterprise edition, a piecemeal restore can bring PRIMARY and the current data online first and restore older filegroups while users are already working.

Spreading one filegroup across several files on the same volume doesn’t add I/O capacity for user databases by itself. It helps when the files are on different volumes, and for tempdb, where multiple files reduce allocation contention.

Measuring Storage Latency

sys.dm_io_virtual_file_stats shows cumulative reads, writes and stall time for every database file since the last restart. Average latency per file is the single most useful storage metric in SQL Server:

SELECT
    DB_NAME(vfs.database_id)                                        AS database_name,
    mf.type_desc                                                    AS file_type,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    CAST(vfs.io_stall_read_ms  * 1.0 / NULLIF(vfs.num_of_reads, 0)  AS decimal(10,2)) AS avg_read_ms,
    CAST(vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0) AS decimal(10,2)) AS avg_write_ms,
    vfs.num_of_bytes_read    / 1048576 AS mb_read,
    vfs.num_of_bytes_written / 1048576 AS mb_written
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_read_ms DESC;

How to read the results:

• Log writes should be in low single-digit milliseconds on modern flash. Sustained log write latency in the tens of milliseconds will show up as WRITELOG waits and slow commits.
• Data reads: older Microsoft guidance treated sustained averages above 20 ms as slow. On flash storage you should expect a fraction of that; consistent double-digit numbers need investigation.
• Compare with the wait statistics: PAGEIOLATCH_* waits mean sessions are waiting for data pages to be read, WRITELOG for log flushes.

SQL Server also writes a warning to the error log when a single I/O takes more than 15 seconds. Any occurrence of this message deserves a call to the storage team:

SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\Data\SalesDB.mdf] in database id 7. The OS file handle is 0x0000000000000D3C. The offset of the latest long I/O is: 0x00000a3f4e2000

Errors 823, 824 and 825 are more serious: they mean the operating system or SQL Server detected a failed or damaged read. Run DBCC CHECKDB and follow our corruption recovery guide before doing anything else.

Common Storage Mistakes

• Databases, logs and backups on one volume, or on the C: drive.
• Volumes formatted with the default 4 KB allocation unit.
• Percentage autogrowth and undersized log files with thousands of VLFs.
• Instant file initialization not granted, so growths and restores take minutes.
• Real-time antivirus scanning database and backup folders.
• A VM whose throughput cap is lower than the workload needs.
• Judging storage by IOPS on a data sheet instead of measured latency at peak.
• Ignoring 15-second I/O warnings in the error log.

Storage Checklist

• Data, log, tempdb and backups are on separate volumes; nothing on C:.
• Volumes use a 64 KB allocation unit.
• Backups are on separate storage and copied off the server.
• Autogrowth is in fixed MB and files are pre-sized.
• Instant file initialization is enabled.
• PAGE_VERIFY CHECKSUM is on for every database.
• Antivirus exclusions are in place.
• Per-file latency and I/O waits are collected and alerted on.
• Error log is monitored for 833, 823, 824 and 825.