Finding Hot Tables With sys.dm_db_index_usage_stats

Before buying capacity, find which tables receive the work and which ones accumulate contention. Identifying hot tables starts with usage counters, then adds waiting behavior and storage context.

Define What Hot Tables Mean for This Workload

A table can be busy with reads, busy with writes, or a source of contention despite relatively few operations. Those conditions suggest different investigations. Decide whether the current problem concerns CPU, storage, blocking, or concurrency before producing one combined ranking. A table's size is another dimension, not a substitute for activity.

I start with the application's slow interval rather than the largest lifetime counter. A busy table that performs efficiently is different from a lightly accessed table whose few requests wait excessively. Keep successful work and contention visible together. The useful result is a set of investigation candidates, not a universal score that pretends every operation costs the same.

Run these queries in the reviewed database with appropriate metadata and DMV permissions. SQL Server 2022 and later uses VIEW SERVER PERFORMANCE STATE for relevant instance performance views. Check the documented permissions for the exact objects and version rather than granting broad administration rights to make an inventory query convenient.

Read Usage at the Index Level

The usage view records seeks, scans, lookups, and index maintenance operations. Join the current database's objects and indexes so names remain attached to the values. An index without a usage row has no recorded entry in the current counter lifetime; that is not proof that it has never mattered.

SELECT s.name AS SchemaName,t.name AS TableName,i.name AS IndexName,
       COALESCE(u.user_seeks,0) AS UserSeeks,
       COALESCE(u.user_scans,0) AS UserScans,
       COALESCE(u.user_lookups,0) AS UserLookups,
       COALESCE(u.user_updates,0) AS UserUpdates
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.indexes AS i ON i.object_id = t.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u
  ON u.database_id = DB_ID() AND u.object_id = i.object_id
 AND u.index_id = i.index_id
WHERE t.is_ms_shipped = 0
ORDER BY COALESCE(u.user_seeks,0) + COALESCE(u.user_scans,0)
       + COALESCE(u.user_lookups,0) DESC;

User updates counts maintenance operations, not the number of affected rows. Also, one statement can maintain several indexes. Summing those values across indexes describes index-maintenance activity, not a distinct count of table modifications or application requests. Preserve that distinction when creating a table-level rollup.

Summarize Hot Tables Without Inventing Cost

A table-level rollup helps locate the concentration of recorded index accesses. It deliberately labels the write total as maintenance activity. Compare this ranking with statement-level evidence before deciding whether the table causes the reported slowdown. Ten inexpensive accesses and one expensive access cannot be ranked accurately by count alone.

SELECT s.name AS SchemaName,t.name AS TableName,
       SUM(u.user_seeks) AS Seeks,SUM(u.user_scans) AS Scans,
       SUM(u.user_lookups) AS Lookups,
       SUM(u.user_updates) AS IndexMaintenanceOperations
FROM sys.dm_db_index_usage_stats AS u
JOIN sys.tables AS t ON t.object_id = u.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE u.database_id = DB_ID() AND t.is_ms_shipped = 0
GROUP BY s.name,t.name
ORDER BY SUM(u.user_seeks + u.user_scans + u.user_lookups) DESC;

A scan counter does not tell you how many pages each scan read. A seek can also read a large range. Use actual plans and workload evidence to investigate those shapes. Hot tables are useful starting points precisely because their counters describe where to look, rather than supplying the final tuning decision.

Add Lock and Latch Evidence

Operational counters show contention associated with table and index operations. Separate row-lock waits, page-latch waits, and page-I/O-latch waits. They describe different resources and need different follow-up evidence. Aggregate partitions consistently when producing a table summary.

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       SUM(row_lock_wait_count) AS RowLockWaits,
       SUM(row_lock_wait_in_ms) AS RowLockWaitMS,
       SUM(page_latch_wait_count) AS PageLatchWaits,
       SUM(page_latch_wait_in_ms) AS PageLatchWaitMS,
       SUM(page_io_latch_wait_count) AS PageIOLatchWaits,
       SUM(page_io_latch_wait_in_ms) AS PageIOLatchWaitMS
FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL)
WHERE OBJECTPROPERTY(object_id,'IsUserTable') = 1
GROUP BY object_id
ORDER BY SUM(row_lock_wait_in_ms) DESC;

These counters have a metadata-cache lifetime and can disappear independently of an engine restart. Preserve that limitation when comparing captures. Do not subtract two values blindly after an index rebuild, cache removal, or object replacement. A reset can make a negative delta, and a reset followed by new work can also produce an apparently positive but incomplete interval.

Put Activity Beside Allocated Size

A small table can be a contention hotspot, while a large archive receives little current work. Record both rows and allocation so the investigation does not confuse storage volume with request frequency. Base rows should be counted once; summing row_count across every nonclustered index inflates that measure.

SELECT s.name AS SchemaName,t.name AS TableName,
       SUM(CASE WHEN p.index_id IN (0,1) THEN p.row_count ELSE 0 END) AS ApproximateRows,
       SUM(p.reserved_page_count) * 8.0 / 1024 AS ReservedMB,
       SUM(p.used_page_count) * 8.0 / 1024 AS UsedMB
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.dm_db_partition_stats AS p ON p.object_id = t.object_id
WHERE t.is_ms_shipped = 0
GROUP BY s.name,t.name;

The allocation here includes the table's indexes, while the row estimate uses only its heap or clustered partitions. State those scopes together. A storage drawer does not become hot just because it is full; it needs evidence of current work or contention.

Save Lightweight Repeated Snapshots

Create a small history table in an approved monitoring database or schema. This compact example stores names, index identity, capture time, startup time, and the raw usage counters in the current database. Preserve raw values so a later report can recalculate intervals without relying on an old summary.

CREATE TABLE dbo.IndexUsageHistory
(
    SnapshotUTC datetime2 NOT NULL,
    StartupTime datetime2 NOT NULL,
    ObjectID int NOT NULL,IndexID int NOT NULL,
    SchemaName sysname NOT NULL,TableName sysname NOT NULL,
    IndexName sysname NULL,
    UserSeeks bigint NOT NULL,UserScans bigint NOT NULL,
    UserLookups bigint NOT NULL,UserUpdates bigint NOT NULL,
    PRIMARY KEY (SnapshotUTC,ObjectID,IndexID)
);
DECLARE @SnapshotUTC datetime2 = SYSUTCDATETIME();
DECLARE @Startup datetime2 = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info);
INSERT dbo.IndexUsageHistory
SELECT @SnapshotUTC,@Startup,t.object_id,i.index_id,s.name,t.name,i.name,
       COALESCE(u.user_seeks,0),COALESCE(u.user_scans,0),
       COALESCE(u.user_lookups,0),COALESCE(u.user_updates,0)
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.indexes AS i ON i.object_id = t.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u
  ON u.database_id = DB_ID() AND u.object_id = i.object_id
 AND u.index_id = i.index_id
WHERE t.is_ms_shipped = 0 AND t.name <> N'IndexUsageHistory';

Schedule the capture at a reviewed interval through an approved SQL Server Agent job and retain at least the week being investigated. Include failed-capture alerts and retention for the monitoring table itself. An unbounded snapshot table is an entertaining way to create a new table to investigate.

Compare a Real Week With Reset Checks

Choose actual starting and ending captures rather than subtracting a lifetime value from an imagined baseline. Match names and index definitions as well as numeric identities where objects can be replaced. Partition the analysis at restarts, rebuild-related resets, and missing coverage. Do not fill a missing capture with an invented zero.

Calculate ending minus starting values for comparable counters and reject decreasing values. Document any reset that can invalidate an apparently increasing delta too. Sum accepted index deltas into table-level activity with the same maintenance-count caution as the current view. Retain the interval's actual duration beside every ranking.

This example selects the earliest retained capture from the last seven days and the latest capture. Its output excludes unmatched identities, changed names, changed startup periods, and decreasing counters. Review the excluded population separately, because a newly created index or a missing boundary is unknown interval coverage rather than zero work. Also retain index-definition changes with the monitoring evidence.

DECLARE @First datetime2 =
    (SELECT MIN(SnapshotUTC) FROM dbo.IndexUsageHistory
     WHERE SnapshotUTC >= DATEADD(DAY,-7,SYSUTCDATETIME()));
DECLARE @Last datetime2 =
    (SELECT MAX(SnapshotUTC) FROM dbo.IndexUsageHistory);
SELECT a.SchemaName,a.TableName,a.IndexName,
       @First AS IntervalStartUTC,@Last AS IntervalEndUTC,
       b.UserSeeks-a.UserSeeks AS SeekDelta,
       b.UserScans-a.UserScans AS ScanDelta,
       b.UserLookups-a.UserLookups AS LookupDelta,
       b.UserUpdates-a.UserUpdates AS MaintenanceDelta
FROM dbo.IndexUsageHistory AS a
JOIN dbo.IndexUsageHistory AS b
  ON b.ObjectID=a.ObjectID AND b.IndexID=a.IndexID
 AND b.SchemaName=a.SchemaName AND b.TableName=a.TableName
 AND ISNULL(b.IndexName,N'')=ISNULL(a.IndexName,N'')
 AND b.StartupTime=a.StartupTime
WHERE a.SnapshotUTC=@First AND b.SnapshotUTC=@Last AND @Last>@First
  AND b.UserSeeks>=a.UserSeeks AND b.UserScans>=a.UserScans
  AND b.UserLookups>=a.UserLookups AND b.UserUpdates>=a.UserUpdates;

Turn Hot Tables Into Focused Investigation

I correlate the busiest candidates with Query Store statements and blocking evidence from the same interval. Then investigate access paths, transaction length, and repeated application requests. More hardware cannot repair an application that unnecessarily repeats the same large scan for each screen element.

Which table was active during the reported slowdown rather than merely active since startup? Answering that question makes the next tuning step smaller and more defensible. Hot tables become actionable when their activity, contention, and capture boundaries agree with the problem being investigated.

Related reading on this blog: Your Index Rebuild Maintenance Plan Is Rebuilding Indexes Nobody Uses and SSMS: Index Usage Statistics, Physical Statistics & User Statistics.

A large lifetime counter is not a tuning diagnosis, it is activity evidence that needs a comparable workload interval.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.