sys.dm_db_index_operational_stats - Usage & Examples

The sys.dm_db_index_operational_stats DMV aggregates index-level operational counters maintained by the Storage Engine's Buffer Manager and Lock Manager components. Each index operation increments specific counters tracked in memory structures associated with the index's metadata.

The DMV captures four categories of statistics: leaf-level operations (singleton lookups, range scans), non-leaf operations (index navigation), leaf-level I/O (page splits, page allocations), and locking patterns (row locks, page locks, lock escalations). These counters are maintained in memory structures associated with each index and reset when SQL Server restarts, the database is detached, or the index is dropped.

Critical behavioral differences exist across versions. SQL Server 2019 enhanced the page_io_latch_wait_count and page_io_latch_wait_in_ms columns to better track buffer pool contention. SQL Server 2022 added more precise tracking of forwarded_fetch_count for heap operations.

The Buffer Manager updates I/O-related counters (page_latch_wait_count, page_io_latch_wait_count) when pages are read from disk or written during checkpoint operations. The Lock Manager increments locking counters (row_lock_count, page_lock_count, lock_escalation_count) during transaction processing. Page split counters increment when the Storage Engine allocates new pages due to insufficient space in existing pages.

Unlike sys.dm_db_index_usage_stats which tracks usage frequency, this DMV focuses on operational costs and contention patterns. The data persists until SQL Server restart, making it valuable for identifying chronic performance issues rather than point-in-time problems.

-- Identify indexes with excessive page splits
SELECT 
    DB_NAME(database_id) AS database_name,
    OBJECT_NAME(object_id, database_id) AS table_name,
    index_id,
    leaf_allocation_count,
    leaf_page_merge_count,
    CASE WHEN leaf_allocation_count > 0 
         THEN (leaf_allocation_count - leaf_page_merge_count) * 100.0 / leaf_allocation_count 
         ELSE 0 END AS page_split_ratio
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL)
WHERE leaf_allocation_count > 1000
  AND (leaf_allocation_count - leaf_page_merge_count) > leaf_allocation_count * 0.1
ORDER BY page_split_ratio DESC;

-- Find indexes with high lock escalation frequency
SELECT 
    DB_NAME(database_id) AS database_name,
    OBJECT_NAME(object_id, database_id) AS table_name,
    i.name AS index_name,
    ios.lock_escalation_count,
    ios.row_lock_count,
    ios.page_lock_count,
    CASE WHEN ios.row_lock_count > 0 
         THEN ios.lock_escalation_count * 100.0 / ios.row_lock_count 
         ELSE 0 END AS escalation_percentage
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i ON ios.object_id = i.object_id AND ios.index_id = i.index_id
WHERE ios.lock_escalation_count > 100
ORDER BY escalation_percentage DESC;

-- Detect indexes with significant latch contention
SELECT 
    DB_NAME(database_id) AS database_name,
    OBJECT_NAME(object_id, database_id) AS table_name,
    index_id,
    page_latch_wait_count,
    page_latch_wait_in_ms,
    page_io_latch_wait_count,
    page_io_latch_wait_in_ms,
    CASE WHEN page_latch_wait_count > 0 
         THEN page_latch_wait_in_ms / page_latch_wait_count 
         ELSE 0 END AS avg_page_latch_wait_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL)
WHERE page_latch_wait_count > 1000
  AND page_latch_wait_in_ms / NULLIF(page_latch_wait_count, 0) > 10
ORDER BY avg_page_latch_wait_ms DESC;

-- Identify inefficient range scan patterns
SELECT 
    DB_NAME(database_id) AS database_name,
    OBJECT_NAME(object_id, database_id) AS table_name,
    i.name AS index_name,
    ios.range_scan_count,
    ios.singleton_lookup_count,
    CASE WHEN (ios.range_scan_count + ios.singleton_lookup_count) > 0 
         THEN ios.range_scan_count * 100.0 / (ios.range_scan_count + ios.singleton_lookup_count) 
         ELSE 0 END AS range_scan_percentage
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i ON ios.object_id = i.object_id AND ios.index_id = i.index_id
WHERE ios.range_scan_count > 10000
  AND ios.range_scan_count > ios.singleton_lookup_count * 3
ORDER BY range_scan_percentage DESC;

-- Monitor index compression effectiveness through operational patterns
SELECT 
    DB_NAME(database_id) AS database_name,
    OBJECT_NAME(object_id, database_id) AS table_name,
    i.name AS index_name,
    p.data_compression_desc,
    ios.leaf_insert_count,
    ios.leaf_update_count,
    ios.leaf_delete_count,
    ios.page_compression_attempt_count,
    ios.page_compression_success_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i ON ios.object_id = i.object_id AND ios.index_id = i.index_id
INNER JOIN sys.partitions p ON ios.object_id = p.object_id AND ios.index_id = p.index_id
WHERE ios.page_compression_attempt_count > 0
ORDER BY ios.page_compression_attempt_count DESC;

Reduce Page Splits with Better Fill Factor Addresses excessive page splits by implementing appropriate fill factor settings based on operational patterns.

-- Calculate recommended fill factor based on insert/update patterns
DECLARE @RecommendedFillFactor INT;
DECLARE @ObjectId INT = OBJECT_ID('YourTableName');
DECLARE @IndexId INT = 2; -- Replace with target index_id

SELECT @RecommendedFillFactor = 
    CASE 
        WHEN leaf_insert_count = 0 THEN 100
        WHEN leaf_insert_count * 1.0 / (leaf_insert_count + leaf_update_count + leaf_delete_count) > 0.5 THEN 80
        WHEN leaf_insert_count * 1.0 / (leaf_insert_count + leaf_update_count + leaf_delete_count) > 0.2 THEN 90
        ELSE 95
    END
FROM sys.dm_db_index_operational_stats(DB_ID(), @ObjectId, @IndexId, NULL);

-- Apply the recommended fill factor (TEST IN DEVELOPMENT FIRST)
DECLARE @SQL NVARCHAR(MAX) = 
    'ALTER INDEX ' + QUOTENAME((SELECT name FROM sys.indexes WHERE object_id = @ObjectId AND index_id = @IndexId)) + 
    ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectId)) + '.' + QUOTENAME(OBJECT_NAME(@ObjectId)) + 
    ' REBUILD WITH (FILLFACTOR = ' + CAST(@RecommendedFillFactor AS VARCHAR(3)) + ')';

PRINT @SQL; -- Review before execution
-- EXEC sp_executesql @SQL;

Reduce Lock Escalation Through Table Options Implements LOCK_ESCALATION settings to minimize lock escalation based on observed patterns.

-- Disable lock escalation for tables with frequent escalation issues
-- WARNING: Can increase lock memory usage, monitor sys.dm_os_memory_clerks
SELECT 
    'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name) + 
    ' SET (LOCK_ESCALATION = DISABLE)' AS alter_statement,
    ios.lock_escalation_count,
    ios.row_lock_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.objects o ON ios.object_id = o.object_id
WHERE ios.lock_escalation_count > 100
  AND ios.lock_escalation_count * 100.0 / NULLIF(ios.row_lock_count, 0) > 5
  AND ios.index_id IN (0, 1) -- Only for heaps and clustered indexes
ORDER BY ios.lock_escalation_count DESC;

-- Execute individually after testing in development

Optimize Index Usage Patterns Creates filtered indexes or adjusts existing indexes based on operational statistics showing inefficient access patterns.

-- Generate filtered index recommendations for range-heavy access patterns
WITH IndexAnalysis AS (
    SELECT 
        ios.object_id,
        ios.index_id,
        i.name AS index_name,
        ios.range_scan_count,
        ios.singleton_lookup_count,
        ic.column_id,
        c.name AS column_name
    FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
    INNER JOIN sys.indexes i ON ios.object_id = i.object_id AND ios.index_id = i.index_id
    INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE ios.range_scan_count > ios.singleton_lookup_count * 2
      AND ios.range_scan_count > 1000
      AND ic.key_ordinal = 1 -- First key column only
)
SELECT 
    'CREATE NONCLUSTERED INDEX IX_' + OBJECT_NAME(object_id) + '_' + column_name + '_Filtered ' +
    'ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) + 
    ' (' + QUOTENAME(column_name) + ') WHERE ' + QUOTENAME(column_name) + ' IS NOT NULL' AS create_statement
FROM IndexAnalysis
ORDER BY range_scan_count DESC;

-- Review and test each recommendation before implementation

Implement Index Compression Based on Operational Metrics Applies page or row compression based on operational patterns and compression success rates.

-- Apply page compression where compression attempts show good success rates
-- TEST PERFORMANCE IMPACT THOROUGHLY
WITH CompressionAnalysis AS (
    SELECT 
        ios.object_id,
        ios.index_id,
        i.name AS index_name,
        ios.page_compression_attempt_count,
        ios.page_compression_success_count,
        p.data_compression_desc,
        CASE 
            WHEN ios.page_compression_attempt_count > 0 
            THEN ios.page_compression_success_count * 100.0 / ios.page_compression_attempt_count 
            ELSE 0 
        END AS compression_success_rate
    FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
    INNER JOIN sys.indexes i ON ios.object_id = i.object_id AND ios.index_id = i.index_id
    INNER JOIN sys.partitions p ON ios.object_id = p.object_id AND ios.index_id = p.index_id
    WHERE ios.page_compression_attempt_count > 1000
      AND p.data_compression_desc = 'NONE'
)
SELECT 
    'ALTER INDEX ' + QUOTENAME(index_name) + ' ON ' + 
    QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) +
    ' REBUILD WITH (DATA_COMPRESSION = PAGE)' AS compression_statement,
    compression_success_rate
FROM CompressionAnalysis
WHERE compression_success_rate > 50
ORDER BY page_compression_attempt_count DESC;

-- Execute during maintenance window with careful monitoring

Implement automated monitoring of sys.dm_db_index_operational_stats through SQL Server Agent jobs that capture baseline metrics and alert on significant deviations. Create weekly reports comparing current operational statistics against historical averages to identify trending issues before they become critical.

Configure appropriate fill factors during index creation based on expected insert patterns rather than using default 100% fill factor. For tables with frequent inserts, start with 80-90% fill factor and adjust based on operational statistics. Monitor leaf_allocation_count and leaf_page_merge_count ratios to validate fill factor effectiveness.

Establish lock escalation thresholds by monitoring lock_escalation_count patterns. Consider partitioning large tables where lock escalation occurs frequently, or implement LOCK_ESCALATION = AUTO settings for partition-level escalation instead of table-level. Monitor lock memory usage through sys.dm_os_memory_clerks when disabling lock escalation.

Design indexes based on operational access patterns revealed by singleton_lookup_count versus range_scan_count ratios. Create covering indexes for frequent range scans and ensure appropriate key column ordering based on actual usage patterns rather than theoretical best practices.

Implement proactive index maintenance schedules informed by operational statistics. Schedule index rebuilds when page split ratios exceed 20% or when page latch wait times consistently exceed 10ms average. Use sys.dm_db_index_physical_stats in conjunction with operational stats to determine optimal maintenance timing.

Monitor buffer pool pressure through page_io_latch_wait metrics and correlate with sys.dm_os_buffer_descriptors to identify memory pressure issues before they impact performance. Adjust max server memory settings based on operational latch contention patterns rather than static formulas.