The index has no reads, but the counters cover only the recent restart. Index usage stats reset changes the meaning of zero. Establish the observation window before proposing an index removal.
Read the Counters With Their Scope
sys.dm_db_index_usage_stats reports seeks, scans, lookups, and updates by database, object, and index. Left join it to sys.indexes so missing rows remain visible. I include index definitions and constraints before reviewing apparently unused candidates.
Write down the question before changing the query. For this example, the useful distinction is between a cumulative usage counter and the actual observation window. Those are different pieces of evidence. A result without that distinction leaves you guessing about the next step. Keep the names and units beside the output. You want the next reader to understand the same thing you understood, without needing your memory of the query window.
Keep the database_id test in the ON clause, not in WHERE. In my test, a new table and index had no usage rows yet. The ON version listed both indexes with NULL counters. Moving database_id to WHERE turned the LEFT JOIN into an inner join, and the result came back empty.
SELECT i.name,i.index_id,i.is_unique,i.is_primary_key,u.user_seeks,u.user_scans,u.user_lookups,u.user_updates
FROM sys.indexes AS i 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 i.object_id=OBJECT_ID(N'dbo.Orders');
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;Check When Index Usage Stats Reset at Startup
sys.dm_os_sys_info.sqlserver_start_time gives the restart boundary. Database offline or detach also removes relevant usage history. Some older builds reset usage through rebuild behavior. Check the exact build when investigating that historical case.
Use a separate test database for the examples that create objects. Read each statement before running the next one. The sample values are deliberately small and illustrative. They explain the rule without claiming a production result. Replace them with a copy of your own data only after the basic behavior is clear. Keep rare business-cycle index requirements in view when you make that change. A larger input does not change the meaning of the rule.
A missing row and a row of zeros mean different things. Right after creation, my test indexes had no row in the DMV at all. After one INSERT, all three indexes gained rows showing user_updates 1, while the unread ones kept user_seeks 0. The first state says nothing was recorded. The second says maintained but never read.
Preserve Snapshots Before Index Usage Stats Reset
Store capture time, server start time, database identity, object and index identifiers, and counters. Schedule the query. Do not assume a single cumulative counter row represents a permanent lifetime record.
Try a zero counter after restart before accepting the first result. That case tells you whether the example handles the boundary you actually care about. Look at the returned values, not just the message saying the statement completed. Successful execution and a correct answer are separate checks. Save the exact input that exposed a difference. It gives you a repeatable test for the next change and keeps the discussion tied to evidence.
The SELECT INTO creates dbo.IndexUsageHistory on the first run. A second run failed in my test with error 2714, because the table already existed. That is why the next block switches to INSERT. The WHERE filter keeps the history to the current database, so run the capture inside each database you track.
SELECT SYSUTCDATETIME() AS captured_utc,s.sqlserver_start_time,u.database_id,u.object_id,u.index_id,
u.user_seeks,u.user_scans,u.user_lookups,u.user_updates
INTO dbo.IndexUsageHistory FROM sys.dm_db_index_usage_stats AS u CROSS JOIN sys.dm_os_sys_info AS s
WHERE u.database_id=DB_ID();
Calculate Deltas Between Each Index Usage Stats Reset
Subtract snapshots only when the counter epoch and object identity are unchanged. A lower counter or new start time means a reset, not negative usage. I retain index names and definitions because numeric identifiers can be reused after changes.
Now compare snapshots within one counter epoch with the original input. Change one part at a time. If you change the data, the query, and the session settings together, the comparison loses its meaning. Keep the result columns visible while you work. A difference is useful only when you can explain which rule produced it. When the output surprises you, reduce the example until the reason becomes clear rather than adding another layer of SQL.
INSERT dbo.IndexUsageHistory
SELECT SYSUTCDATETIME(),s.sqlserver_start_time,u.database_id,u.object_id,u.index_id,
u.user_seeks,u.user_scans,u.user_lookups,u.user_updates
FROM sys.dm_db_index_usage_stats AS u CROSS JOIN sys.dm_os_sys_info AS s WHERE u.database_id=DB_ID();Include Rare Business Activity
Monthly and yearly reports can need an index absent from the current window. Ask the workload owners before removal. A calendar has longer patience than a DMV. Constraint-backed indexes also protect rules beyond visible reads.
I check a cumulative usage counter before I trust the final answer. It is easy to focus on the visible symptom and overlook the input that created it. Ask yourself: does the actual observation window support the decision you are about to make? Keep a second example that disagrees with your first assumption. A check that only confirms the easy case is comforting, but it does not protect the next person who uses the script.
Treat Update Counts Carefully
user_updates counts operations, not rows changed. One statement touching many rows is not the same number of counter increments. In my test, one INSERT of 100 rows and one UPDATE of all of them left user_updates at 2. Avoid turning that value directly into a claimed maintenance byte cost.
Include a monthly report outside the captured interval in your review. The quiet case matters as much as the busy one. Define what an empty result means and what an error means. Do not treat the two as interchangeable. If another process changes the same data, decide who owns the comparison and when it is valid. Record that boundary beside the script. The next run should not depend on someone remembering an unwritten rule.
Review Before Removing
Index usage stats reset is a reason to improve evidence, not abandon the view. Collect history across relevant workload cycles, inspect dependencies, and test any removal on a copy. Keep a restoration definition for approved changes.
I keep rare business-cycle index requirements in the handoff notes because that is where shortcuts return. Give the next DBA the query, the interpretation, and the condition that makes the result unreliable. Keep the original evidence when you change the implementation. Repeat the same boundary checks afterward. You then have a practical way to judge the change, instead of a general feeling that the new version looks better.
Related reading on this blog: Columnstore Index and sys.dm_db_index_usage_stats and Reading a Workload Capture Without a Tool.
An empty usage counter is not a drop recommendation, it is a result within a limited window.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
