Disk Latency Benchmarks

Overview

SQL Server performance is not solely dependent on CPU and RAM. A critical, often overlooked factor is Storage I/O Latency. Even with high-spec processors, a SQL Server will feel "sluggish" to end-users if the underlying storage cannot acknowledge read/write requests fast enough. This is especially critical for the TempDB, which acts as a global scratchpad for the entire instance.

Applicability: When to use this guide

These recommendations are specifically intended for SQL Server environments characterized by:

• High Query Complexity: Frequent use of temporary tables (#tempTables), PIVOT operations, HASH JOINS, or large SORT operations.
• Intensive Background Logging: Systems running Business Activity Monitoring (BAM) or other high-volume telemetry.
• High Storage Latency: Observed tempdb write latency exceeding 20ms (monitored via sys.dm_io_virtual_file_stats).
• Availability Group Overhead: Environments using Always On where row versioning (Version Store) adds pressure to tempdb.

Understanding Disk Latency

Disk latency (or I/O stalls) is the time (in milliseconds) it takes for a storage subsystem to complete a request. In SQL Server, we monitor this through the sys.dm_io_virtual_file_stats Dynamic Management View (DMV).

Performance Benchmarks

Latency thresholds vary depending on workload and storage tier. For example, transaction log writes are more sensitive to latency than data file reads.

Latency (ms)	Assessment	Impact
< 1ms	Elite	Typical for local NVMe or high-end All-Flash Arrays.
1ms - 10ms	Excellent	Normal range for Premium SSD / Production storage.
10ms - 20ms	Acceptable	Users might notice slight delays during peak loads.
20ms - 50ms	Poor	Common threshold for user complaints ("The system is slow").
50ms - 100ms	Bad	Heavy bottlenecking occurring.
> 100ms	Critical	Potential hardware failure, misconfiguration, or severe cloud throttling.

The Critical Role of TempDB

TempDB is used for temporary tables (#temp), sorting, hash joins, and row versioning.

• Write Latency in TempDB: If TempDB write latency is high (e.g., >100ms), queries that rely on TempDB operations may experience significant stalls while waiting for the disk.
• Resource Contention: Because TempDB is a shared resource, a bottleneck here affects all databases on the instance, regardless of their individual traffic levels.

Common Causes in Cloud Environments

When running SQL Server in a Cloud/Virtual environment, high latency is often caused by:

1. Throughput Throttling: Cloud disks often have IOPS/Throughput limits. Exceeding these limits causes the provider to artificially "slow down" I/O, resulting in massive latency spikes.
2. Shared Storage Latency: Virtual disks are often network-attached. Network congestion or high demand from other VMs on the same host can impact performance.
3. Misconfigured Autogrow: If TempDB is small and grows in small increments (e.g., 256MB), this can stall operations that need to write to the growing file while new space is initialized.

Monitoring Script (Diagnostic)

Run the following T-SQL to identify current I/O bottlenecks across all database files:

SELECT 
    DB_NAME(vfs.database_id) AS [Database Name],
    df.name AS [Logical Name],
    df.physical_name AS [Physical Path],
    CAST(vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads, 0) AS DECIMAL(10,2)) AS [Avg Read Latency (ms)],
    CAST(vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0) AS DECIMAL(10,2)) AS [Avg Write Latency (ms)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS df ON vfs.database_id = df.database_id AND vfs.file_id = df.file_id
ORDER BY [Avg Write Latency (ms)] DESC;

Wait Stats Correlation: Always validate whether latency issues are caused by storage (PAGEIOLATCH) or allocation contention (PAGELATCH).

High TempDB latency should always be correlated with wait statistics:

• PAGEIOLATCH_* indicates storage latency
• PAGELATCH_* indicates allocation contention (not disk-related)

⚠️ Misinterpreting PAGELATCH as a disk issue is a common mistake and can lead to incorrect mitigation (e.g., upgrading storage instead of resolving contention).

Recommendations

The primary and leading source of truth for tempdb configuration is the official Microsoft documentation. It is highly recommended to review the architectural guidelines provided there:

• Microsoft Docs: SQL Server tempdb database - Overview & Recommendations

1. Storage Infrastructure (Critical)

• Move to Local SSD/NVMe: Transfer tempdb files from network-based or shared cloud storage to local "Ephemeral" or "Local" SSDs. This eliminates network-induced storage latency.
• Target Latency: Aim for a write-latency of < 5ms. In high-usage scenarios, latency above 20ms acts as a global performance bottleneck for the entire SQL Server instance.

2. File Sizing & Pre-allocation

• Number of Files: Start with 4–8 data files and increase only if contention (e.g., PAGELATCH waits) is observed.
• Uniformity: Maintain identical initial sizes and growth increments for all files. This is mandatory for SQL Server's "Proportional Fill" algorithm to ensure SQL Server distributes allocations evenly across files based on available free space.
• Autogrowth: Configure growth increments to 512 MB or 1024 MB. Small increments (e.g., 64MB) lead to physical file fragmentation and frequent performance "hiccups."

3. Maintenance Best Practices

• Zero-Shrink Policy: Avoid shrinking TempDB in normal operations; only consider it as a corrective action after abnormal growth events.

• I/O Isolation: If tempdb latency remains high, ensure that other heavy write-intensive files (such as large Transaction Logs) are not residing on the same physical disk tier to prevent I/O contention.

• Monitoring: Use the following command to check if current tasks are waiting on tempdb resources:

  SELECT session_id, wait_type, wait_duration_ms, resource_description
  FROM sys.dm_os_waiting_tasks
  WHERE resource_description LIKE '2:%'; -- '2' is the Database ID for TempDB