DBA Scripts: Get Index Usage Stats

---

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_ONLY is 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 instance
• usage_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.
*/
-- 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:

• sql/performance/indexes/Get-IndexUsageStats.sql

---

Example Output

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:

• Index Maintenance (hub)
• Unused Indexes
• Index Fragmentation
• Missing Indexes
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

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.