A drive letter tells you nothing about latency. The storage settings that matter for SQL Server are the ones a workload test can confirm. Old checklists distract from those measurements. Start with the database’s I/O pattern, then verify the Windows and storage configuration that serves it.
Measure Latency Before Moving Files
Data files perform mixed reads and writes. Log files need reliable sequential writes for commits. tempdb can have its own allocation and spill patterns. Measure the actual file-level latency over intervals before reorganizing drives. A drive letter is an administrative label, not a performance guarantee. Several letters can share the same underlying array and contention.
I compare read and write latency during business peaks, not only an idle period. Sustained high latency on a log path can slow commits even when CPU is free. A fast benchmark outside SQL Server does not necessarily reproduce queueing under the real database workload. Do those separate drive letters lead to separate storage resources?
Use File-Level I/O Counters
sys.dm_io_virtual_file_stats reports cumulative reads, writes, bytes, and stall time by file. Take two samples and calculate differences over a known interval. Dividing lifetime stall time by lifetime operations can hide a recent incident. The snapshot below identifies files for the current database. Run it twice during a representative window for rates and interval latency.
A zero operation count needs careful handling before division. Join to sys.master_files for the path and file type. Match the result to the actual storage topology provided by operations.
SELECT mf.name, mf.type_desc, mf.physical_name,
vfs.num_of_reads, vfs.io_stall_read_ms,
vfs.num_of_writes, vfs.io_stall_write_ms,
vfs.num_of_bytes_read, vfs.num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID(), 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 mf.file_id;
Place Files by Workload
Separate data and log paths when the underlying storage resources are genuinely independent and the workload benefits. Putting files on different drive letters that map to the same busy volume does not create isolation. Likewise, placing tempdb on a separate high-performance path can help when tempdb is a real bottleneck, but it needs measurement and capacity planning.
Use supported redundancy and backup design for every path. A dedicated fast log device without resilience is a poor trade. I ask storage administrators about physical or virtual backing, cache policies, and competing workloads. SQL Server can report stalls. It cannot see every layer behind a virtual disk.
Pre-Size and Grow Predictably
Frequent small file growth events can interrupt work and fragment file layout. Size data and log files from observed usage and expected growth, then choose fixed-size autogrowth increments that fit capacity and recovery needs. Avoid percentage growth on very large files if it creates unpredictable jumps. Keep free space for busy periods and maintenance.
Log growth requires zero initialization. Instant file initialization can speed eligible data-file growth but does not remove the need for capacity planning. I monitor growth events and adjust initial size rather than treating autogrowth as the normal allocation strategy. A file that repeatedly grows during peak traffic is announcing a sizing mistake.
Understand Instant File Initialization
Instant file initialization allows eligible data-file allocation without zeroing the new space, when the SQL Server service account has the required Windows privilege. Transaction log files still need initialization. The feature helps restores and data-file growth, but it does not improve every read or write after the space exists.
Verify the SQL Server startup message or service account privilege according to the installed version. Coordinate the Windows setting with security policy, because it affects how reused disk space is handled. The goal is predictable file operations, not a claim that one privilege turns slow storage into fast storage.
Check Allocation Unit Size in Storage Settings
A 64 KB NTFS allocation unit is a common SQL Server data and log volume recommendation, but it is only one layer of storage design. Confirm the current formatting and underlying device alignment with Windows tools before changing anything. Reformatting a volume is disruptive and requires a migration plan, backup verification, and a clear expected benefit.
Do not reformat healthy storage merely because a checklist says so. Measure latency, throughput, and file behavior first. I have more confidence in an observed bottleneck than in an inherited setting copied from a server built a decade ago. The allocation unit is a factor, not the whole storage story.
Review tempdb Separately
tempdb supports temporary tables, sorts, hashes, version store, and other engine work. Its file count, size, and storage path should reflect actual contention and volume. Multiple equally sized data files can help allocation contention, but adding files blindly can complicate management. A spill-heavy query can overwhelm even a well-configured tempdb path.
Track tempdb file stalls, space use, and waits. Fix the query or memory grant issue when that is the cause. A faster disk can provide headroom, but it does not make unnecessary spills disappear. Include tempdb growth and cleanup in capacity planning, especially under row-versioning workloads.
Retire Outdated Shortcuts in Storage Settings
Rules such as one file per CPU, every index on a separate disk, or always placing data and log on separate letters ignore modern virtualized and shared storage. The useful questions are whether resources are actually independent and whether latency meets the workload’s needs. Hardware caches and SAN policies also require vendor-supported configuration.
I document the storage map, SQL file paths, growth settings, and measured peak I/O. That makes future moves testable. A topology diagram with actual backing is more useful than a list of drive letters. The storage team and database team need a shared view of the same path.
Validate Storage Settings After a Change
Compare file-level latency, throughput, log flush waits, query duration, and error events before and after. Include backup and restore behavior, not just interactive queries. Storage changes can move a bottleneck to CPU or memory. Check that maintenance windows and availability replicas still keep up.
Storage settings That Matter for SQL Server are practical and measurable. Size files, know the backing devices, verify initialization and formatting, then watch the workload. A clean configuration sheet is helpful. A predictable commit at peak time is the real proof.
SELECT name, type_desc, physical_name,
size * 8.0 / 1024 AS allocated_mb,
growth, is_percent_growth
FROM sys.database_files
ORDER BY file_id;Related reading on this blog: Monitoring Database Autogrowth Settings and SQL SERVER 2019: How to Turn On or Enable Instant File Initialization?.
A storage setting is not a drive-letter ritual, it is a measurable part of I/O behavior and capacity.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
