Index Usage Snapshots: Reject Invalid Counter Deltas

Index usage snapshots need a known counter window before their differences can represent activity. A restart can remove the earlier counters. Missing rows, changing identities and lifecycle gaps can make subtraction misleading.

Capture raw index usage snapshots without inventing zeroes

The capture below selects ordinary rowstore indexes in the current database. Its LEFT JOIN preserves indexes without a usage row. CounterPresent distinguishes a missing row from a recorded counter. Raw missing values stay NULL.

CaptureSequence is a placeholder for a unique sequence issued by your collector. LifecycleEpoch must come from independently maintained lifecycle evidence. Leave it NULL when continuity is unknown. Repeatedly running this template is not a complete scheduled collector.

-- Read-only capture. Set context values from your collector's own durable records.
DECLARE @CaptureSequence bigint=1, @LifecycleEpoch nvarchar(64)=NULL;
DECLARE @CaptureUtc datetime2(7)=SYSUTCDATETIME();
SELECT @CaptureSequence AS CaptureSequence, @CaptureUtc AS CaptureUtc,
 CONVERT(sysname,SERVERPROPERTY('ServerName')) AS ServerName,
 s.sqlserver_start_time AS ServerStartTime, @LifecycleEpoch AS LifecycleEpoch,
 DB_ID() AS DatabaseId, DB_NAME() AS DatabaseName,
 t.object_id AS ObjectId, t.create_date AS TableCreatedAt,
 SCHEMA_NAME(t.schema_id) AS TableSchema, t.name AS TableName,
 i.index_id AS IndexId, i.name AS IndexName, i.type_desc AS IndexType,
 i.is_unique AS IsUnique, i.is_disabled AS IsDisabled,
 i.has_filter AS HasFilter, i.filter_definition AS FilterDefinition,
 CASE WHEN u.index_id IS NULL THEN 0 ELSE 1 END AS CounterPresent,
 u.user_seeks AS UserSeeks, u.user_scans AS UserScans,
 u.user_lookups AS UserLookups, u.user_updates AS UserUpdates
FROM sys.tables AS t JOIN sys.indexes AS i ON i.object_id=t.object_id
CROSS JOIN sys.dm_os_sys_info AS s
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 i.index_id>0 AND i.type IN(1,2)
 AND i.is_hypothetical=0
ORDER BY t.object_id,i.index_id;

The template is read-only, so it does not change the instance. Supply the collector context yourself.

The capture selects counters, startup identity, table creation time and current index metadata. It does not make the catalog and DMV an atomic historical snapshot. Busy workloads can change during collection. Preserve collection failures and their times rather than copying the previous sample.

Keep the counter window and permissions visible

The usage view begins empty at engine startup. Database shutdown or detach can remove its rows within the same engine session. Capture startup time, but also track database lifecycle events. An unchanged startup value alone cannot prove continuity.

On SQL Server 2022 and later, these server-state diagnostics require VIEW SERVER PERFORMANCE STATE. Earlier supported versions require VIEW SERVER STATE. Catalog visibility also limits the observed objects. Validate collection access before describing absent activity.

Reject invalid pairs before reporting any delta

The demonstration below uses made-up histories, not server workload measurements. It orders each history by a unique capture sequence. The same timestamp can then retain a definite observation order. A zero-duration interval still cannot produce a meaningful per-second rate.

First, a temporary table holds the made-up counter histories. Run this setup before the query that follows.

DROP TABLE IF EXISTS #CounterResult;
DROP TABLE IF EXISTS #CounterSnapshot;
CREATE TABLE #CounterSnapshot
 (CaseName varchar(32) NOT NULL, CaptureSequence int NOT NULL,
  CaptureUtc datetime2(7) NOT NULL, ServerStartTime datetime NOT NULL,
  LifecycleEpoch int NULL, CounterPresent bit NOT NULL,
  UserSeeks bigint NULL, UserScans bigint NULL,
  UserLookups bigint NULL, UserUpdates bigint NULL,
  PRIMARY KEY(CaseName,CaptureSequence));
INSERT #CounterSnapshot VALUES
 ('First sample',1,'2025-01-01T10:00:00','2025-01-01',1,1,10,2,3,4),
 ('Valid delta',1,'2025-01-01T10:00:00','2025-01-01',1,1,10,2,3,4),
 ('Valid delta',2,'2025-01-01T10:01:00','2025-01-01',1,1,12,3,5,5),
 ('Equal timestamp',1,'2025-01-01T10:00:00','2025-01-01',1,1,10,2,3,4),
 ('Equal timestamp',2,'2025-01-01T10:00:00','2025-01-01',1,1,12,3,5,5),
 ('Missing current',1,'2025-01-01T10:00:00','2025-01-01',1,1,10,2,3,4),
 ('Missing current',2,'2025-01-01T10:01:00','2025-01-01',1,0,NULL,NULL,NULL,NULL),
 ('Missing previous',1,'2025-01-01T10:00:00','2025-01-01',1,0,NULL,NULL,NULL,NULL),
 ('Missing previous',2,'2025-01-01T10:01:00','2025-01-01',1,1,12,3,5,5),
 ('Counter decrease',1,'2025-01-01T10:00:00','2025-01-01',1,1,10,2,3,4),
 ('Counter decrease',2,'2025-01-01T10:01:00','2025-01-01',1,1,12,1,5,5),
 ('Startup changed',1,'2025-01-01T10:00:00','2025-01-01',1,1,10,2,3,4),
 ('Startup changed',2,'2025-01-01T10:01:00','2025-01-01T10:00:30',1,1,12,3,5,5),
 ('Lifecycle changed',1,'2025-01-01T10:00:00','2025-01-01',1,1,10,2,3,4),
 ('Lifecycle changed',2,'2025-01-01T10:01:00','2025-01-01',2,1,12,3,5,5),
 ('Lifecycle unknown',1,'2025-01-01T10:00:00','2025-01-01',NULL,1,10,2,3,4),
 ('Lifecycle unknown',2,'2025-01-01T10:01:00','2025-01-01',NULL,1,12,3,5,5),
 ('Clock moved back',1,'2025-01-01T10:00:00','2025-01-01',1,1,10,2,3,4),
 ('Clock moved back',2,'2025-01-01T09:59:00','2025-01-01',1,1,12,3,5,5);

A first sample opens a baseline, rather than a measured interval. Valid pairs require both counter rows, a matching startup and a known unchanged lifecycle. Every counter must also remain nondecreasing.

This query uses LAG to compare each sample with the one before it, then labels every pair.

WITH Previous AS
 (SELECT *,
  LAG(CaptureSequence) OVER(PARTITION BY CaseName ORDER BY CaptureSequence) AS PriorSequence,
  LAG(CaptureUtc) OVER(PARTITION BY CaseName ORDER BY CaptureSequence) AS PriorUtc,
  LAG(ServerStartTime) OVER(PARTITION BY CaseName ORDER BY CaptureSequence) AS PriorStart,
  LAG(LifecycleEpoch) OVER(PARTITION BY CaseName ORDER BY CaptureSequence) AS PriorEpoch,
  LAG(CounterPresent) OVER(PARTITION BY CaseName ORDER BY CaptureSequence) AS PriorPresent,
  LAG(UserSeeks) OVER(PARTITION BY CaseName ORDER BY CaptureSequence) AS PriorSeeks,
  LAG(UserScans) OVER(PARTITION BY CaseName ORDER BY CaptureSequence) AS PriorScans,
  LAG(UserLookups) OVER(PARTITION BY CaseName ORDER BY CaptureSequence) AS PriorLookups,
  LAG(UserUpdates) OVER(PARTITION BY CaseName ORDER BY CaptureSequence) AS PriorUpdates
  FROM #CounterSnapshot),
 Checked AS
 (SELECT *,CASE
  WHEN PriorSequence IS NULL THEN 'FIRST SAMPLE'
  WHEN LifecycleEpoch IS NULL OR PriorEpoch IS NULL THEN 'LIFECYCLE UNKNOWN'
  WHEN ServerStartTime<>PriorStart THEN 'STARTUP CHANGED'
  WHEN LifecycleEpoch<>PriorEpoch THEN 'LIFECYCLE CHANGED'
  WHEN CaptureUtc<PriorUtc THEN 'CLOCK MOVED BACK'
  WHEN CounterPresent=0 OR PriorPresent=0 THEN 'MISSING COUNTER'
  WHEN UserSeeks IS NULL OR UserScans IS NULL OR UserLookups IS NULL OR UserUpdates IS NULL
   OR PriorSeeks IS NULL OR PriorScans IS NULL OR PriorLookups IS NULL OR PriorUpdates IS NULL
   THEN 'MISSING COUNTER'
  WHEN UserSeeks<PriorSeeks OR UserScans<PriorScans
   OR UserLookups<PriorLookups OR UserUpdates<PriorUpdates THEN 'COUNTER DECREASE'
  ELSE 'VALID' END AS Validity
  FROM Previous)
 SELECT CaseName,CaptureSequence,CaptureUtc,Validity,
  CASE WHEN Validity='VALID' THEN UserSeeks-PriorSeeks END AS SeekDelta,
  CASE WHEN Validity='VALID' THEN UserScans-PriorScans END AS ScanDelta,
  CASE WHEN Validity='VALID' THEN UserLookups-PriorLookups END AS LookupDelta,
  CASE WHEN Validity='VALID' THEN UserUpdates-PriorUpdates END AS UpdateDelta
 INTO #CounterResult FROM Checked;

SELECT CaseName,Validity,SeekDelta,ScanDelta,LookupDelta,UpdateDelta
FROM #CounterResult WHERE CaptureSequence=2 OR CaseName='First sample' ORDER BY CaseName;

DROP TABLE #CounterResult;
DROP TABLE #CounterSnapshot;

The validity rule applies to all four reported deltas. It does not silently retain seeks after another counter decreases. The two accepted cases had identical tested differences. Every rejected pair leaves every delta NULL.

A larger counter does not prove an uninterrupted history

A reset followed by enough new activity can exceed an earlier captured value. Numeric nondecrease will not reveal that reset. Independently retained lifecycle evidence must identify the gap. Unknown continuity should produce an unknown interval, rather than an invented activity total.

Numeric object and index identifiers are not permanent deployment identities. Save the complete index definition and schema-change history alongside the quick capture. A recreated index can reuse familiar identifiers and names. A matching name alone is insufficient evidence for subtraction.

I would rather retain an unexplained gap than label it as a quiet period. That conservative choice also limits what this short example promises. It demonstrates validity decisions, without implementing durable collection or automatic lifecycle detection. Build those parts around the workload’s actual monitoring requirements.

Use the history to guide a review

Counters describe operations, rather than complete elapsed-time or row-cost measurements. UserUpdates does not count every changed row. Review the relevant reporting cycle, actual plans and index duties. A quiet observed interval does not authorize dropping an index.

Keep the gaps in your history and the quiet periods will be honest ones.

A counter difference is not automatically activity, it is a comparison that needs a valid window.

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