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

One row per index that SQL Server has actually touched since the last restart, ordered by write cost first, then reads. The pattern to look for is a wide gap between the two: an index the server is paying to maintain and nobody is reading.

Understanding the Results

The most important thing this script cannot show you is an index that is missing from it. sys.dm_db_index_usage_stats only holds a row once an index has been read or written, so an index nobody has touched since the last restart does not appear here at all. Absence is a finding, not a clean bill of health, and it is the opposite of what the list looks like it is telling you. Counters also reset on restart, so check the uptime with OS and Hardware Info before reading anything into a small number.

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

Microsoft’s reference covers sys.dm_db_index_usage_stats in full.

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.