sys.dm_db_index_usage_stats - Usage & Examples

The Query Optimizer maintains usage counters in memory for every index accessed by user queries. When the Storage Engine executes a query plan, it increments specific counters based on the access method: user_seeks for index seek operations, user_scans for index scan operations, user_lookups for key lookups on clustered indexes, and user_updates for INSERT, UPDATE, DELETE operations affecting the index.

These statistics accumulate in the sys.dm_db_index_usage_stats DMV through the Query Execution Engine's interaction with the Access Methods layer. The Buffer Manager records index page accesses, while the Lock Manager tracks modification operations that trigger update counters. Statistics reset occurs during SQL Server service restarts, database detach/attach (or close), or when the specific index is rebuilt. Note that DBCC FREEPROCCACHE does NOT reset these counters — only plan cache is cleared, not index usage statistics.

SQL Server 2016 introduced minor improvements to counter accuracy during parallel operations. SQL Server 2019 enhanced the precision of seek vs scan differentiation for columnstore indexes. SQL Server 2022 added better tracking for Intelligent Query Processing features like adaptive joins that may cause dynamic index usage pattern changes.

The DMV only tracks usage since the last reset event, creating a critical gap in historical analysis. Indexes with zero usage may still be essential for infrequent but critical queries, while high update counts with low read counts signal potential over-indexing problems affecting write performance.

-- Find completely unused indexes (candidates for removal)
SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ius.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS TotalReads,
    ius.user_updates AS Writes,
    (SELECT SUM(page_count) FROM sys.dm_db_index_physical_stats(DB_ID(), ius.object_id, ius.index_id, NULL, 'LIMITED')) AS PageCount
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE ius.database_id = DB_ID()
    AND ius.user_seeks = 0 
    AND ius.user_scans = 0 
    AND ius.user_lookups = 0
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
ORDER BY PageCount DESC;

-- Identify write-heavy indexes with minimal reads (over-indexing indicators)
SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ius.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS TotalReads,
    ius.user_updates AS TotalWrites,
    CASE 
        WHEN (ius.user_seeks + ius.user_scans + ius.user_lookups) = 0 THEN 999999
        ELSE ius.user_updates / NULLIF((ius.user_seeks + ius.user_scans + ius.user_lookups), 0)
    END AS WriteToReadRatio
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE ius.database_id = DB_ID()
    AND ius.user_updates > 1000
    AND i.is_primary_key = 0
ORDER BY WriteToReadRatio DESC;

-- Find most frequently accessed indexes for optimization prioritization
SELECT TOP 20
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ius.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS TotalReads,
    ius.last_user_seek,
    ius.last_user_scan,
    ius.last_user_lookup
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE ius.database_id = DB_ID()
    AND (ius.user_seeks + ius.user_scans + ius.user_lookups) > 0
ORDER BY TotalReads DESC;

-- Detect scan-heavy indexes that may need better selectivity
SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ius.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    CASE 
        WHEN ius.user_seeks = 0 THEN 100.0
        ELSE (ius.user_scans * 100.0) / (ius.user_seeks + ius.user_scans)
    END AS ScanPercentage,
    ius.last_user_scan
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE ius.database_id = DB_ID()
    AND ius.user_scans > 100
    AND i.type IN (2, 6) -- Non-clustered and columnstore indexes
ORDER BY ScanPercentage DESC, ius.user_scans DESC;

-- Check when statistics were last reset to validate data reliability
SELECT 
    sqlserver_start_time,
    DATEDIFF(day, sqlserver_start_time, GETDATE()) AS DaysSinceRestart,
    CASE 
        WHEN DATEDIFF(day, sqlserver_start_time, GETDATE()) < 7 THEN 'Recent restart - limited data'
        WHEN DATEDIFF(day, sqlserver_start_time, GETDATE()) < 30 THEN 'Moderate data collection period'
        ELSE 'Sufficient data collection period'
    END AS DataReliability
FROM sys.dm_os_sys_info;

Remove Unused Indexes (High Impact)

-- Generate DROP statements for unused indexes
-- WARNING: Test thoroughly in development first
-- Verify no application code references these indexes directly
SELECT 
    'DROP INDEX [' + i.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(ius.object_id) + '];' AS DropStatement,
    (SELECT SUM(page_count) FROM sys.dm_db_index_physical_stats(DB_ID(), ius.object_id, ius.index_id, NULL, 'LIMITED')) AS PageCount
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE ius.database_id = DB_ID()
    AND ius.user_seeks = 0 
    AND ius.user_scans = 0 
    AND ius.user_lookups = 0
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND DATEDIFF(day, (SELECT sqlserver_start_time FROM sys.dm_os_sys_info), GETDATE()) > 30
ORDER BY PageCount DESC;
-- Expected impact: Reduced storage usage, faster INSERT/UPDATE/DELETE operations

Disable High-Maintenance Indexes for Evaluation

-- Disable write-heavy, rarely-read indexes instead of dropping immediately
-- Allows for safer testing of removal impact
SELECT 
    'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(ius.object_id) + '] DISABLE;' AS DisableStatement
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE ius.database_id = DB_ID()
    AND ius.user_updates > (ius.user_seeks + ius.user_scans + ius.user_lookups) * 10
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0;
-- Expected impact: Immediate write performance improvement, ability to re-enable if needed

Create Historical Usage Tracking

-- Create table to track index usage over time
-- Run this script monthly via SQL Agent job
CREATE TABLE dbo.IndexUsageHistory (
    CaptureDate datetime2 DEFAULT GETDATE(),
    DatabaseName sysname,
    SchemaName sysname,
    TableName sysname,
    IndexName sysname,
    UserSeeks bigint,
    UserScans bigint,
    UserLookups bigint,
    UserUpdates bigint,
    LastUserSeek datetime,
    LastUserScan datetime,
    LastUserLookup datetime,
    LastUserUpdate datetime
);

-- Insert current usage statistics
INSERT INTO dbo.IndexUsageHistory (
    DatabaseName, SchemaName, TableName, IndexName,
    UserSeeks, UserScans, UserLookups, UserUpdates,
    LastUserSeek, LastUserScan, LastUserLookup, LastUserUpdate
)
SELECT 
    DB_NAME() AS DatabaseName,
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ius.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan,
    ius.last_user_lookup,
    ius.last_user_update
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE ius.database_id = DB_ID();
-- Expected impact: Historical trending data for better decision making

Implement automated monitoring to capture sys.dm_db_index_usage_stats data before SQL Server restarts reset the counters. Schedule monthly SQL Agent jobs to archive usage statistics into permanent tables, enabling long-term trend analysis that survives service restarts.

Establish index governance policies requiring business justification for new indexes and regular review cycles for existing ones. Set thresholds for automatic index removal consideration: zero reads for 90+ days, write-to-read ratios exceeding 50:1, or excessive maintenance overhead during peak hours.

Configure Extended Events sessions to capture index usage patterns during critical business periods, supplementing DMV data with real-time access tracking. Monitor fill factor settings on write-heavy indexes identified through usage statistics, adjusting values to reduce page splits and maintenance overhead.

Deploy proactive alerting when new indexes remain unused for extended periods or when existing indexes show dramatically changing usage patterns. Integrate index usage analysis into change management processes, requiring impact assessment before deploying new indexes to production environments.

Use Database Engine Tuning Advisor recommendations cautiously, cross-referencing suggestions against actual usage statistics rather than accepting automated recommendations blindly. Establish baseline measurements after major application deployments to identify usage pattern shifts requiring index strategy adjustments.