Part of the DBA-Tools Project.
The Raw Usage Data Behind Every Index Decision
Unused Indexes already filters straight to drop candidates in one database. This script is the raw data underneath that filter, and more: every index across every user database, its seeks, scans, lookups, and updates, classified into a usage pattern, with no filtering applied. It’s the source data you’d want when the filtered “unused indexes” list isn’t quite the question you’re asking, cross-database usage patterns, write-heavy indexes, or scan-heavy tables that might need a better index instead of just removing a bad one.
Why Index Usage Stats Matter
usage_pattern = WRITE_ONLYis the same signal Unused Indexes filters for, but seeing it across every database in one pass, rather than one database at a time, is useful when reviewing a whole instanceusage_pattern = SCAN_HEAVY(scans far outnumbering seeks) points the other direction, toward a missing or ineffective index, not a redundant one, a table doing full scans instead of seeks needs attention even though the index isn’t “unused”- These counters reset on every SQL Server restart, a recently-restarted instance will show artificially low numbers across the board, not a true picture of long-term usage
- Cross-database visibility in one query is genuinely useful when reviewing an instance with many databases, rather than switching context repeatedly
When to Run This Script
- Instance-wide index usage review, especially on a server with many databases
- Investigating a specific table’s scan/seek balance as part of a performance investigation
- Before committing to Unused Indexes’ drop recommendations, to see the same data in a broader, unfiltered context
- After several days of representative workload, running immediately after a restart gives an incomplete picture
The Script
/*
Script Name : Get-IndexUsageStats
Category : performance-troubleshooting
Purpose : Show how indexes across all user databases are being used, seeks, scans,
lookups, updates.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-index-usage-stats/)
Requires : VIEW SERVER STATE, VIEW ANY DATABASE
Notes : Usage counters reset on SQL Server restart. High user_updates with low reads =
candidate for removal. High user_scans = possible missing index on that table.
*/
-- Blog: https://sqldba.blog/dba-scripts-get-index-usage-stats/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
DB_NAME(ius.database_id) AS database_name,
OBJECT_SCHEMA_NAME(ius.object_id, ius.database_id) AS schema_name,
OBJECT_NAME(ius.object_id, ius.database_id) AS table_name,
ius.index_id,
ius.user_seeks,
ius.user_scans,
ius.user_lookups,
ius.user_updates,
ius.user_seeks + ius.user_scans + ius.user_lookups AS total_reads,
CASE
WHEN ius.user_seeks + ius.user_scans + ius.user_lookups = 0
AND ius.user_updates > 0
THEN 'WRITE_ONLY'
WHEN ius.user_scans > ius.user_seeks * 10
THEN 'SCAN_HEAVY'
ELSE 'NORMAL'
END AS usage_pattern,
ius.last_user_seek,
ius.last_user_scan,
ius.last_user_update
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.database_id > 4
ORDER BY ius.user_updates DESC, total_reads DESC;
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Index usage across every user database:
.\run.ps1 Get-IndexUsageStats
# To run against a remote sql server:
.\run.ps1 Get-IndexUsageStats -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
Real output from this lab instance, not staged:
| database_name | table_name | index_id | user_seeks | user_scans | user_updates | usage_pattern |
|---|---|---|---|---|---|---|
| WatchtowerMetrics | DatabaseGrowthEvents | 2 | 0 | 128 | 64 | SCAN_HEAVY |
| WatchtowerMetrics | DatabaseGrowthEvents | 1 | 0 | 0 | 64 | WRITE_ONLY |
| RandomLab | OrdersDemo | 0 | 0 | 13 | 10 | SCAN_HEAVY |
| RandomLab | CustomerLookupDemo | 2 | 0 | 2 | 0 | SCAN_HEAVY |
| RandomLab | CustomerLookupDemo | 1 | 0 | 0 | 1 | NORMAL |
A genuine, real finding here: WatchtowerMetrics.DatabaseGrowthEvents‘s clustered index (index_id = 1) shows WRITE_ONLY, 64 updates and zero reads, exactly the pattern to watch, this table is a monitoring-data sink that gets written frequently but rarely queried directly on this lab instance.
Understanding the Results
- usage_pattern = WRITE_ONLY — the same signal as Unused Indexes, a non-clustered index with this pattern is a real drop candidate; a clustered index (index_id = 1) with this pattern is expected for write-heavy, rarely-queried tables, not something to remove
- usage_pattern = SCAN_HEAVY — the table is doing full or range scans rather than seeks; investigate whether a more selective index would let queries seek instead, this is a “add or fix an index” signal, not a “remove one” signal
- Counters reset on restart — check OS and Hardware Info‘s uptime figure alongside this, a recently-restarted instance hasn’t accumulated enough usage data for these numbers to mean much yet
- index_id = 0 — a heap (no clustered index); scan-heavy activity on a heap is worth investigating whether a clustered index would help overall table access patterns
Best Practices
- Run this instance-wide before drilling into Unused Indexes on a specific database, it gives the broader context the filtered list doesn’t show
- Don’t trust the numbers immediately after a restart, wait for at least a few days of representative workload
- Treat SCAN_HEAVY findings as an indexing opportunity, not just a curiosity, a genuinely useful index could turn those scans into seeks
- Cross-reference clustered index (index_id = 1) WRITE_ONLY findings against the table’s actual purpose before assuming anything is wrong, a write-heavy log or event table is supposed to look like this
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
How is this different from Unused Indexes?
Unused Indexes filters straight to a specific, actionable list: non-clustered indexes with zero reads and non-zero writes, in one database, with a ready-to-run DROP INDEX statement. This script is the unfiltered source data across every database, useful when you want the fuller picture, including scan-heavy tables that need a better index, not just indexes worth removing.
Why do the same table’s indexes show completely different usage patterns?
Different indexes on the same table often serve different queries. A clustered index might see heavy writes and few direct reads while a non-clustered covering index on the same table sees frequent seeks, that’s normal and expected, not a sign either one is misconfigured.
Summary
Every index-removal or index-addition decision should be grounded in real usage data, not a guess. This script is that raw data, seeks, scans, lookups, and updates across every index on the instance, classified into a pattern that points toward either a genuine drop candidate or a missing-index opportunity.
Run it instance-wide as a starting point, then reach for Unused Indexes’ filtered, actionable list once you know which database and which pattern you’re chasing.
Leave a Reply