Build1 publisher3 min readPublished
A SQL Server restart zeroes the usage counters that justify dropping an index
sys.dm_db_index_usage_stats counts seeks, scans and lookups only since the instance came up, so an index with no reads may just be waiting for its monthly job. One cross join puts that window in the report.
The Engineer · Build desk
What happened
- The same query filters to i.type = 2 and excludes hypothetical, primary key and unique indexes, so only standard nonclustered rowstore indexes reach the review list.
- Writes are read from user_updates, because updating a row may require SQL Server to update every index containing the changed columns whether or not anything reads them.
- The article cites a 2023 DBA Stack Exchange discussion in which a team described tables carrying more than 70 indexes, while saying that does not mean all 70 were unnecessary.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint No review window shorter than a business cycle can clear an index that only serves month-end close or an annual reconciliation, so instance uptime sets a hard ceiling on what the evidence can prove.
- decision With the restart time on every row, a reviewer can reject a drop candidate on uptime alone, before anyone argues about whether the query plan needs it.
- cost Keeping a dead index costs continuously and predictably in DML work, pages, log and rebuild time; the cost of a wrong drop lands once, in the middle of a reporting run.
- exposure Because the published code only reads the counters, any team that runs it once and acts on the result is exposing its rarest queries.
One line in the inventory query has nothing to do with indexes. `CROSS JOIN sys.dm_os_sys_info AS os` stamps `sqlserver_start_time` onto every row of the output, and the dev.to walkthrough tells you to keep it there [6]. Without it, you have no way to tell how long the zero in the reads column has been a zero. The counters in `sys.dm_db_index_usage_stats` reset when SQL Server restarts [2], so uptime is the window the count covers. If the instance came up last week, a month-end report has not had its turn yet. An index that looks unused today may still be serving month-end reporting, an annual reconciliation, or an audit query that runs occasionally [4].
The reads column is `user_seeks + user_scans + user_lookups`, and the writes column is `user_updates` [14]. The writes side is what an index costs you to keep. Update an order, and SQL Server may have to update the order row plus every index that includes the changed columns, which adds work to inserts, updates and deletes [7]. Wide indexes cost more than narrow ones: large `INCLUDE` lists mean more pages, more log activity, and heavier rebuild and maintenance jobs [8]. Microsoft warns against speculative and overly wide indexes for those reasons [9].
Size comes from `sys.dm_db_partition_stats` as `SUM(used_page_count) * 8.0 / 1024`, grouped by object and index [10]. That is 8 KB pages, so 128 pages to the megabyte [11]. The ordering is `writes DESC, reads ASC, size_mb DESC` [13], which puts the index paying the most maintenance for the fewest recorded reads at the top of the list.
The filter is narrow on purpose: `i.type = 2`, no hypothetical indexes, no primary keys, no unique indexes [12]. The article is explicit that what comes back is a starting inventory of candidates for review, not a list of indexes that are safe to delete [15].
The scale anecdote deserves the same treatment as any benchmark. One team described tables with more than 70 indexes in a 2023 DBA Stack Exchange discussion, and the article says plainly that this does not mean all 70 were unnecessary [16]. For that number to mean anything in your database you need the read/write mix of the table it came from. A reporting table may legitimately need several indexes; an insert-heavy queue table may need far fewer [17]. Without that mix, 70 only tells you that old fixes piled up and nobody revisited them [1].
The procedure the article recommends is one change at a time, then watch the workload, with a rollback script ready if the workload disagrees [18]. That holds up, with one gap worth closing yourself. The published query is a `SELECT` [19]. It reads the counters at the moment you run it; carrying evidence across a quarter or a fiscal year means persisting those rows on a schedule, which is a separate job. Drop on a single snapshot and the failure surfaces at the next business cycle [5].
What to watch
- Whether the walkthrough follows up with a scheduled job that persists DMV rows. Persisted rows are what turn one snapshot into cycle-spanning evidence.
- How many candidates from a first pass turn out to serve a once-a-year audit or reconciliation query once a full fiscal year of snapshots exists.
- Whether Microsoft's DMV documentation changes what survives a restart, since the whole review procedure currently depends on uptime.