Troubleshooting SQL Server 2022 IOPs for Optimal Performance

SQL Server 2022 IOPS (Input/Output Operations Per Second) performance is one of the most critical — and most frequently misdiagnosed — bottlenecks in production database environments. SQL Server 2022 introduces significant architectural enhancements, yet I/O subsystem performance remains foundational to achieving optimal query latency, high transaction throughput, and predictable SLA delivery. Whether you are running SQL Server 2022 on bare metal, a SAN, NVMe-attached storage, or a cloud-based VM, understanding how SQL Server 2022 IOPS consumption behaves is essential to database performance engineering. This guide walks database administrators, performance engineers, and infrastructure architects through a systematic, step-by-step approach to diagnosing, analyzing, and resolving SQL Server 2022 IOPS-related performance issues.

Key Takeaways: SQL Server 2022 IOPS Performance at a Glance

Before diving into the detailed SQL Server 2022 IOPS troubleshooting methodology, here are the essential performance thresholds every DBA should keep in mind. SQL Server 2022 IOPS benchmarks vary by workload type, but these baselines serve as reliable starting points for performance assessment:

• Average read latency above 20–25 ms for data files indicates SQL Server 2022 IOPS bottleneck requiring immediate investigation.
• Average write latency above 1–2 ms for transaction log files signals critical SQL Server 2022 IOPS saturation on the log volume.
• Page Life Expectancy (PLE) below 300 seconds (baseline) indicates buffer pool pressure driving excessive SQL Server 2022 IOPS from physical disk reads.
• PAGEIOLATCH_SH and WRITELOG in the top five wait statistics are definitive indicators of SQL Server 2022 IOPS-related workload bottlenecks.
• TempDB IOPS are among the most underestimated SQL Server 2022 IOPS consumers, particularly in environments with heavy ad-hoc workloads.

What Are IOPS and Why Do They Matter in SQL Server 2022?

IOPS measure the number of read and write operations a storage subsystem can handle per second. For SQL Server 2022, IOPS directly impact query latency, transaction throughput, checkpoint performance, log write latency, and tempdb contention. Poor IOPS performance manifests as high PAGEIOLATCH_SH, PAGEIOLATCH_EX, WRITELOG, and IO_COMPLETION wait types in SQL Server wait statistics, causing cascading performance degradation across workloads. SQL Server 2022 workloads have two primary I/O patterns: sequential I/O (full table scans, index scans, backup/restore, log writes) and random I/O (OLTP row-level reads, index seeks, tempdb allocations). Your storage subsystem must be tuned for both, and each demands different optimization strategies.

Step 1: Identify IOPS-Related Wait Statistics

The first step in troubleshooting SQL Server 2022 IOPS problems is to query sys.dm_os_wait_stats and identify I/O-bound wait types. This DMV provides cumulative wait statistics since the last SQL Server restart, giving you a high-level view of where time is being spent.

-- Query I/O-related wait statistics in SQL Server 2022
-- Run this to identify the top I/O bottlenecks
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms          AS resource_wait_time_ms,
    CAST(100.0 * wait_time_ms
        / SUM(wait_time_ms) OVER ()             AS DECIMAL(5,2)) AS pct_total_waits
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'PAGEIOLATCH_SH',
    'PAGEIOLATCH_EX',
    'PAGEIOLATCH_UP',
    'WRITELOG',
    'IO_COMPLETION',
    'ASYNC_IO_COMPLETION',
    'BACKUPIO',
    'BACKUPBUFFER',
    'DISKIO_SUSPEND'
)
AND wait_time_ms > 0
ORDER BY wait_time_ms DESC;

High PAGEIOLATCH_SH waits indicate that SQL Server is waiting for a data page to be read from disk into the buffer pool — a classic symptom of insufficient IOPS or an undersized buffer pool. WRITELOG waits signal that the transaction log write path is the bottleneck, which is often related to log file placement on slow storage or insufficient log disk IOPS.

Step 2: Measure SQL Server 2022 IOPS in Real Time with sys.dm_io_virtual_file_stats

To obtain per-file I/O statistics including read/write stall times, use sys.dm_io_virtual_file_stats. This DMV is essential for identifying which database files are experiencing the highest I/O latency, enabling targeted remediation at the file and volume level.

-- Analyze per-file I/O stall times and IOPS consumed
-- io_stall > 20ms for data files or > 1ms for log files indicates storage pressure
SELECT
    DB_NAME(vfs.database_id)                    AS database_name,
    mf.physical_name,
    mf.type_desc                                AS file_type,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms,
    vfs.io_stall,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.num_of_bytes_read / 1048576             AS mb_read,
    vfs.num_of_bytes_written / 1048576          AS mb_written,
    CASE WHEN vfs.num_of_reads = 0 THEN 0
         ELSE vfs.io_stall_read_ms / vfs.num_of_reads
    END                                          AS avg_read_stall_ms,
    CASE WHEN vfs.num_of_writes = 0 THEN 0
         ELSE vfs.io_stall_write_ms / vfs.num_of_writes
    END                                          AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)   AS vfs
JOIN sys.master_files                           AS mf
    ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY vfs.io_stall DESC;

The key thresholds to watch are: average read stall above 20–25 ms for data files indicates a significant IOPS bottleneck; average write stall above 1–2 ms for transaction log files is a critical signal that log write performance is degrading transaction throughput. These values should guide your storage tier selection and IOPS provisioning decisions. For detailed documentation on sys.dm_io_virtual_file_stats (Microsoft Learn), refer to the official SQL Server 2022 documentation.

Step 3: Identify Top SQL Server 2022 IOPS-Intensive Queries

Once you have identified the files and databases under I/O pressure, the next step is to find the specific queries driving that I/O load. sys.dm_exec_query_stats provides cumulative logical and physical I/O statistics for cached query plans, making it your primary tool for workload-level I/O analysis.

-- Identify top I/O-consuming queries from the plan cache
-- Focus on physical_reads as these represent actual disk IOPS
SELECT TOP 25
    qs.total_physical_reads,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.execution_count,
    qs.total_physical_reads / qs.execution_count   AS avg_physical_reads,
    qs.total_logical_reads / qs.execution_count    AS avg_logical_reads,
    qs.total_elapsed_time / qs.execution_count     AS avg_elapsed_time_us,
    SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(qt.text)
              ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1
    )                                               AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats                        AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)    AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.total_physical_reads > 0
ORDER BY qs.total_physical_reads DESC;

High physical reads (as opposed to logical reads) indicate that query data is not being served from SQL Server's buffer pool, causing the engine to go to disk repeatedly. This pattern is a strong indicator of either a buffer pool that is too small for the working dataset, missing or suboptimal indexes, or poorly written queries performing large scans on frequently accessed tables.

Step 4: Diagnose Buffer Pool Pressure and Memory-to-IOPS Relationship

One of the most effective ways to reduce IOPS in SQL Server 2022 is to ensure the buffer pool is large enough to cache the working set of data pages. When the buffer pool is undersized, SQL Server's lazy writer and checkpoint processes must constantly evict dirty pages to disk and read pages back in, generating unnecessary IOPS. Use the following queries to assess buffer pool health:

-- Assess buffer pool usage per database
-- Databases consuming large buffer pool chunks drive most I/O when memory is constrained
SELECT
    DB_NAME(database_id)                        AS database_name,
    COUNT(*) * 8 / 1024                         AS buffer_pool_mb,
    SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 8 / 1024 AS dirty_pages_mb
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4   -- Exclude system databases
GROUP BY database_id
ORDER BY buffer_pool_mb DESC;

-- Check current SQL Server memory configuration and pressure indicators
SELECT
    physical_memory_in_use_kb / 1024            AS memory_used_mb,
    page_fault_count,
    memory_utilization_percentage
FROM sys.dm_os_process_memory;

-- Identify page life expectancy (PLE) -- values below 300 seconds indicate buffer pressure
SELECT
    object_name,
    counter_name,
    instance_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Page life expectancy',
    'Lazy writes/sec',
    'Checkpoint pages/sec',
    'Page reads/sec',
    'Page writes/sec'
)
AND object_name LIKE '%Buffer Manager%'
ORDER BY counter_name;

Page Life Expectancy (PLE) below 300 seconds (or below your calculated threshold based on buffer pool size) is a reliable early warning indicator of buffer pool pressure. A low PLE means pages are being evicted rapidly, driving unnecessary read IOPS. The formula for a healthy PLE baseline is: (Buffer Pool Size in GB / 4) × 300 seconds.

Step 5: Analyze Tempdb IOPS Contention

TempDB is one of the most IOPS-intensive components of SQL Server 2022, particularly in environments running complex OLTP workloads, large sorts, hash joins, or heavy use of temporary tables and table variables. SQL Server 2022 inherits the tempdb metadata optimization from SQL Server 2019, but IOPS contention can still occur if tempdb is improperly sized or placed on slow storage.

-- Analyze tempdb I/O stall and file-level IOPS consumption
SELECT
    mf.name                                     AS tempdb_file,
    mf.physical_name,
    mf.type_desc,
    mf.size * 8 / 1024                          AS file_size_mb,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms,
    CASE WHEN vfs.num_of_reads = 0 THEN 0
         ELSE vfs.io_stall_read_ms / vfs.num_of_reads
    END                                          AS avg_read_stall_ms,
    CASE WHEN vfs.num_of_writes = 0 THEN 0
         ELSE vfs.io_stall_write_ms / vfs.num_of_writes
    END                                          AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL)      AS vfs   -- DB ID 2 = tempdb
JOIN sys.master_files                           AS mf
    ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY vfs.io_stall DESC;

-- Check tempdb version store usage -- excessive version store drives IOPS
SELECT
    reserved_page_count * 8 / 1024              AS version_store_mb,
    reserved_space_kb / 1024                    AS reserved_mb
FROM sys.dm_db_file_space_usage
WHERE database_id = 2;

Best practices for SQL Server 2022 tempdb IOPS optimization include: placing tempdb data files on the fastest available storage (NVMe preferred), creating one tempdb data file per logical CPU core (up to 8 files), ensuring all tempdb data files are equally sized to enable proportional fill, and enabling trace flag 1117 behavior (now default in SQL Server 2016+) for uniform autogrowth. In cloud deployments, consider using local ephemeral NVMe-backed storage for tempdb.

Step 6: Investigate Transaction Log Write Performance

Transaction log write performance is one of the most critical IOPS paths in SQL Server 2022. Every committed transaction must have its log records hardened to disk before the COMMIT returns to the application. High WRITELOG waits directly translate to increased transaction latency and reduced throughput. SQL Server 2022 supports Accelerated Database Recovery (ADR) which can reduce log-related recovery times, but log write IOPS remain equally important.

-- Monitor transaction log write performance in real-time
-- WRITELOG waits above 1ms avg indicate log I/O bottleneck
SELECT
    DB_NAME(database_id)                        AS database_name,
    mf.physical_name                            AS log_file_path,
    mf.size * 8 / 1024                          AS log_file_size_mb,
    vfs.num_of_writes                           AS total_log_writes,
    vfs.io_stall_write_ms                       AS total_write_stall_ms,
    CASE WHEN vfs.num_of_writes = 0 THEN 0
         ELSE vfs.io_stall_write_ms / vfs.num_of_writes
    END                                          AS avg_log_write_stall_ms,
    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 vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
WHERE mf.type_desc = 'LOG'
ORDER BY avg_log_write_stall_ms DESC;

-- Check WRITELOG wait accumulation over a 60-second interval
-- Use two snapshots to compute deltas
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    CAST(wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0) AS DECIMAL(10,2)) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'WRITELOG';

If average log write stall exceeds 1–2 ms consistently, immediately investigate: whether the log file resides on shared storage with competing workloads, whether write-back caching is enabled and properly protected by a battery-backed unit or capacitor, whether the storage volume's IOPS queue depth is saturated, and whether group commit batching via the DELAYED_DURABILITY database option is appropriate for your RPO/RTO requirements.

Step 7: Use SQL Server 2022 DMVs for Storage I/O Subsystem Analysis

SQL Server 2022 exposes detailed I/O pending request data through sys.dm_io_pending_io_requests, which allows DBAs to detect I/O stalls in real time as they are occurring — rather than relying solely on cumulative statistics. This DMV is particularly valuable during active performance incidents.

-- Capture pending I/O requests to identify active storage bottlenecks
-- Run during a performance incident to catch in-flight I/O stalls
SELECT
    r.io_handle,
    r.io_type,
    r.io_pending,
    r.io_pending_ms_ticks,
    r.scheduler_address,
    DB_NAME(f.database_id)                      AS database_name,
    f.physical_name
FROM sys.dm_io_pending_io_requests              AS r
JOIN sys.dm_io_virtual_file_stats(NULL, NULL)   AS vfs
    ON r.io_handle = vfs.file_handle
JOIN sys.master_files                           AS f
    ON vfs.database_id = f.database_id
    AND vfs.file_id = f.file_id
ORDER BY r.io_pending_ms_ticks DESC;

-- Check for I/O frozen or long-running I/O requests
-- SQL Server reports these to the error log and Windows event log as well
SELECT
    DB_NAME(database_id)                        AS database_name,
    file_id,
    io_stall,
    num_of_reads + num_of_writes                AS total_io_operations,
    CAST(io_stall * 1.0
        / NULLIF(num_of_reads + num_of_writes, 0) AS DECIMAL(10,2)) AS avg_io_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE io_stall > 0
ORDER BY avg_io_stall_ms DESC;

Step 8: Optimize SQL Server 2022 Storage Configuration for Maximum IOPS

Beyond query and workload-level tuning, SQL Server 2022 IOPS performance is significantly influenced by storage and OS-level configuration. The following T-SQL and system-level checks help validate your storage configuration against best practices.

-- Verify SQL Server 2022 I/O-related configuration settings
SELECT
    name,
    value_in_use,
    description
FROM sys.configurations
WHERE name IN (
    'max server memory (MB)',
    'min server memory (MB)',
    'cost threshold for parallelism',
    'max degree of parallelism',
    'optimize for ad hoc workloads'
)
ORDER BY name;

-- Check trace flags that affect I/O behavior
DBCC TRACESTATUS(-1);

-- Validate database file autogrowth settings
-- Avoid autogrowth events during production hours as they cause I/O spikes
SELECT
    DB_NAME(database_id)                        AS database_name,
    name                                        AS file_name,
    physical_name,
    type_desc,
    size * 8 / 1024                             AS current_size_mb,
    CASE is_percent_growth
        WHEN 1 THEN CAST(growth AS VARCHAR) + '%'
        ELSE CAST(growth * 8 / 1024 AS VARCHAR) + ' MB'
    END                                          AS autogrowth_setting,
    max_size
FROM sys.master_files
ORDER BY database_id, type_desc;

Critical storage configuration recommendations for SQL Server 2022 IOPS optimization include: disabling NTFS last access time updates (fsutil behavior set disablelastaccess 1), formatting data volumes with 64KB NTFS allocation unit size, disabling Windows power management sleep states on storage controllers, enabling Windows Disk Write Caching only on battery-backed controllers, and validating that SQL Server service accounts have the "Perform Volume Maintenance Tasks" privilege (which enables Instant File Initialization to eliminate zero-fill IOPS on data file growth).

Step 9: Leverage SQL Server 2022 Query Store for Historical IOPS Analysis

SQL Server 2022 enhances the Query Store with improved regressed query detection and additional wait statistics capture. Query Store persists runtime statistics across server restarts, making it invaluable for correlating IOPS spikes with specific query plan changes over time.

-- Enable Query Store with wait statistics capture on a target database
-- SQL Server 2022 supports enhanced wait stats in Query Store
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,
    WAIT_STATS_CAPTURE_MODE = ON,   -- Capture wait stats per query (SQL 2017+)
    MAX_PLANS_PER_QUERY = 200
);

-- Query the Query Store for top I/O-generating queries with historical trend
SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    rs.avg_physical_io_reads,
    rs.avg_logical_io_reads,
    rs.avg_logical_io_writes,
    rs.avg_duration,
    rs.avg_rowcount,
    rs.count_executions,
    p.plan_id,
    TRY_CAST(p.query_plan AS XML)               AS query_plan
FROM sys.query_store_query                      AS q
JOIN sys.query_store_query_text                 AS qt
    ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan                       AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats              AS rs
    ON p.plan_id = rs.plan_id
ORDER BY rs.avg_physical_io_reads DESC;

Step 10: Monitor SQL Server 2022 IOPS with Performance Counters

SQL Server 2022 exposes IOPS-related metrics through both Windows Performance Monitor (PerfMon) and the sys.dm_os_performance_counters DMV. Establishing baseline IOPS metrics during normal operations is essential for capacity planning and anomaly detection.

-- Capture SQL Server 2022 performance counters relevant to IOPS
-- Establish baselines during normal workloads for comparison during incidents
SELECT
    RTRIM(object_name)                          AS perf_object,
    RTRIM(counter_name)                         AS counter_name,
    RTRIM(instance_name)                        AS instance_name,
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Page reads/sec',
    'Page writes/sec',
    'Lazy writes/sec',
    'Checkpoint pages/sec',
    'Page life expectancy',
    'Log Bytes Flushed/sec',
    'Log Flushes/sec',
    'Log Flush Wait Time',
    'Log Flush Waits/sec',
    'Disk Read Bytes/sec',
    'Disk Write Bytes/sec'
)
ORDER BY perf_object, counter_name;

SQL Server 2022 IOPS Troubleshooting: Decision Framework

Effective IOPS troubleshooting in SQL Server 2022 requires correlating findings across multiple layers. The following systematic approach ensures no root cause is missed:

• Start at the wait statistics layer — use sys.dm_os_wait_stats to confirm the workload is I/O bound before investing time in storage-level investigation.
• Identify the hottest files — use sys.dm_io_virtual_file_stats to find which database files and volumes are under the greatest IOPS pressure.
• Attribute IOPS to specific queries — use sys.dm_exec_query_stats and Query Store to find the top physical-read consumers and address them with indexing, query rewrites, or plan forcing.
• Validate buffer pool sizing — insufficient memory is the most cost-effective IOPS problem to solve; adding RAM reduces physical reads dramatically.
• Audit storage configuration — confirm NTFS allocation unit size, write caching, volume alignment, and queue depth settings are optimized for SQL Server workloads.
• Segregate workload I/O streams — place data files, log files, and tempdb on separate physical or virtual storage volumes to prevent I/O interference between competing workload types.
• Establish continuous IOPS baselines — use Query Store, PerfMon, or a purpose-built monitoring tool to capture IOPS trends so you can detect degradation before it impacts production SLAs.

Conclusion: Mastering SQL Server 2022 IOPS for Peak Database Performance

Troubleshooting SQL Server 2022 IOPS for optimal performance is a multi-layered discipline that spans T-SQL diagnostics, storage architecture, operating system configuration, and workload management. By systematically using SQL Server's rich set of DMVs — particularly sys.dm_os_wait_stats, sys.dm_io_virtual_file_stats, sys.dm_exec_query_stats, and the enhanced Query Store — database professionals can precisely identify, quantify, and resolve I/O bottlenecks before they escalate into production outages. Investing in proper IOPS baseline monitoring, right-sized buffer pools, optimized storage configuration, and disciplined index management will yield measurable improvements in SQL Server 2022 transaction throughput, query latency, and overall database reliability. For enterprise SQL Server 2022 performance engineering engagements, our team at MinervaDB provides expert-led IOPS analysis, storage architecture reviews, and end-to-end database performance optimization services. Contact us to discuss your SQL Server performance challenges.