Browse cheat sheets · SQL Server
Check Index Usage
Review SQL Server index read and write activity, identify low-value indexes, and decide what deserves deeper analysis before any index is removed.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Show read and write activity for every regular index
- 2. Find indexes with writes but no observed user reads
- 3. Find indexes with many writes relative to reads
- 4. Show last observed read and write times
- 5. Check how long the current DMV observation window has existed
- 6. Add index size to the usage view
- 7. Show the indexed columns before making a decision
- What the result means
- What to look for
- Zero reads + many writes
- Very low reads + very high updates
- Heavy scans
- Many lookups
- High index size + low read activity
- Similar indexes on the same table
- Production notes
- Usage statistics reset
- user_updates counts operations, not modified rows
- Missing DMV row is not proof of permanent non-use
- Permission requirements vary
- Memory-optimized indexes use different DMVs
- Usage count is not business value
- Common mistakes
- 1. Dropping every index with zero reads
- 2. Ignoring the server restart time
- 3. Treating scans as bad
- 4. Using user_updates as modified-row count
- 5. Dropping primary-key or unique-constraint indexes
- 6. Evaluating one index in isolation
- 7. Using usage stats as historical monitoring
- Next step
- Related deep dives
Problem
SQL Server can accumulate indexes over time.
Some are heavily used. Some exist for a specific monthly report. Some support constraints. Some may no longer help any important workload but still add storage, memory, and write-maintenance cost.
The goal is not to find indexes to drop automatically.
The goal is to answer:
- Which indexes are being used for seeks, scans, or lookups?
- Which indexes are being maintained by writes?
- Which indexes appear unused during the observed period?
- How recently was an index used?
- Is an index expensive to maintain relative to the read benefit it provides?
- Which indexes deserve deeper review?
Quick answer
Start with sys.dm_db_index_usage_stats, joined to the catalog views so indexes with no DMV row still appear.
SELECT
s.name AS schema_name,
t.name AS table_name,
i.index_id,
i.name AS index_name,
i.type_desc,
i.is_unique,
i.is_primary_key,
i.is_unique_constraint,
COALESCE(us.user_seeks, 0) AS user_seeks,
COALESCE(us.user_scans, 0) AS user_scans,
COALESCE(us.user_lookups, 0) AS user_lookups,
COALESCE(us.user_updates, 0) AS user_updates,
COALESCE(us.user_seeks, 0)
+ COALESCE(us.user_scans, 0)
+ COALESCE(us.user_lookups, 0) AS total_user_reads,
us.last_user_seek,
us.last_user_scan,
us.last_user_lookup,
us.last_user_update
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
ON us.database_id = DB_ID()
AND us.object_id = i.object_id
AND us.index_id = i.index_id
WHERE
t.is_ms_shipped = 0
AND i.index_id > 0
AND i.is_hypothetical = 0
ORDER BY
total_user_reads ASC,
user_updates DESC,
s.name,
t.name,
i.index_id;
Treat the output as evidence for investigation, not as a drop script.
Code / commands
1. Show read and write activity for every regular index
This is the default inventory query.
SELECT
s.name AS schema_name,
t.name AS table_name,
i.index_id,
i.name AS index_name,
i.type_desc,
COALESCE(us.user_seeks, 0) AS user_seeks,
COALESCE(us.user_scans, 0) AS user_scans,
COALESCE(us.user_lookups, 0) AS user_lookups,
COALESCE(us.user_updates, 0) AS user_updates,
COALESCE(us.user_seeks, 0)
+ COALESCE(us.user_scans, 0)
+ COALESCE(us.user_lookups, 0) AS total_user_reads,
us.last_user_seek,
us.last_user_scan,
us.last_user_lookup,
us.last_user_update
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
ON us.database_id = DB_ID()
AND us.object_id = i.object_id
AND us.index_id = i.index_id
WHERE
t.is_ms_shipped = 0
AND i.index_id > 0
AND i.is_hypothetical = 0
ORDER BY
total_user_reads DESC,
user_updates DESC;
The LEFT JOIN matters.
An index that has not been used since usage statistics were reset may not have a row in sys.dm_db_index_usage_stats. An inner join would hide exactly the indexes you may want to investigate.
2. Find indexes with writes but no observed user reads
Use this as a candidate list, never as an automatic removal list.
SELECT
s.name AS schema_name,
t.name AS table_name,
i.index_id,
i.name AS index_name,
i.type_desc,
COALESCE(us.user_seeks, 0) AS user_seeks,
COALESCE(us.user_scans, 0) AS user_scans,
COALESCE(us.user_lookups, 0) AS user_lookups,
COALESCE(us.user_updates, 0) AS user_updates,
us.last_user_update
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
ON us.database_id = DB_ID()
AND us.object_id = i.object_id
AND us.index_id = i.index_id
WHERE
t.is_ms_shipped = 0
AND i.index_id > 1
AND i.is_hypothetical = 0
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND COALESCE(us.user_seeks, 0) = 0
AND COALESCE(us.user_scans, 0) = 0
AND COALESCE(us.user_lookups, 0) = 0
AND COALESCE(us.user_updates, 0) > 0
ORDER BY
user_updates DESC;
Why index_id > 1?
This focuses on nonclustered rowstore indexes and avoids treating a clustered index like an optional secondary index.
You still must review the index definition, workload history, dependencies, and business use before considering removal.
3. Find indexes with many writes relative to reads
This can reveal indexes with high maintenance cost compared with observed user read activity.
SELECT
s.name AS schema_name,
t.name AS table_name,
i.index_id,
i.name AS index_name,
COALESCE(us.user_seeks, 0)
+ COALESCE(us.user_scans, 0)
+ COALESCE(us.user_lookups, 0) AS total_user_reads,
COALESCE(us.user_updates, 0) AS user_updates,
CAST(
COALESCE(us.user_updates, 0) * 1.0
/ NULLIF(
COALESCE(us.user_seeks, 0)
+ COALESCE(us.user_scans, 0)
+ COALESCE(us.user_lookups, 0),
0
)
AS decimal(18,2)
) AS writes_per_read
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
ON us.database_id = DB_ID()
AND us.object_id = i.object_id
AND us.index_id = i.index_id
WHERE
t.is_ms_shipped = 0
AND i.index_id > 1
AND i.is_hypothetical = 0
ORDER BY
writes_per_read DESC,
user_updates DESC;
A high ratio is not automatically bad.
An index used by a critical query once per hour may be worth far more than an index read thousands of times by unimportant workload.
4. Show last observed read and write times
Use this when trying to determine whether an index is seasonal, periodic, or potentially obsolete.
SELECT
s.name AS schema_name,
t.name AS table_name,
i.name AS index_name,
us.last_user_seek,
us.last_user_scan,
us.last_user_lookup,
us.last_user_update
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
ON us.database_id = DB_ID()
AND us.object_id = i.object_id
AND us.index_id = i.index_id
WHERE
t.is_ms_shipped = 0
AND i.index_id > 0
AND i.is_hypothetical = 0
ORDER BY
COALESCE(
us.last_user_seek,
us.last_user_scan,
us.last_user_lookup,
us.last_user_update
) ASC;
A NULL last-use timestamp does not prove an index has never been used.
It means no corresponding usage has been recorded in the current observation window.
5. Check how long the current DMV observation window has existed
Usage counters are meaningful only if you know when they could have been reset.
SELECT
sqlserver_start_time
FROM sys.dm_os_sys_info;
sys.dm_db_index_usage_stats counters are reset when the SQL Server Database Engine starts.
Rows for a database can also disappear when the database is detached or shut down, such as with AUTO_CLOSE.
If the instance restarted yesterday, “unused index” conclusions are weak.
6. Add index size to the usage view
A large, write-heavy, rarely read index may deserve more attention than a tiny index.
WITH index_size AS
(
SELECT
ps.object_id,
ps.index_id,
SUM(ps.used_page_count) AS used_pages
FROM sys.dm_db_partition_stats AS ps
GROUP BY
ps.object_id,
ps.index_id
)
SELECT
s.name AS schema_name,
t.name AS table_name,
i.name AS index_name,
CAST(COALESCE(sz.used_pages, 0) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
COALESCE(us.user_seeks, 0) AS user_seeks,
COALESCE(us.user_scans, 0) AS user_scans,
COALESCE(us.user_lookups, 0) AS user_lookups,
COALESCE(us.user_updates, 0) AS user_updates
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN index_size AS sz
ON sz.object_id = i.object_id
AND sz.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
ON us.database_id = DB_ID()
AND us.object_id = i.object_id
AND us.index_id = i.index_id
WHERE
t.is_ms_shipped = 0
AND i.index_id > 0
AND i.is_hypothetical = 0
ORDER BY
used_mb DESC;
Use size together with usage, not instead of usage.
7. Show the indexed columns before making a decision
Before dropping or consolidating anything, inspect the definition.
SELECT
s.name AS schema_name,
t.name AS table_name,
i.name AS index_name,
i.index_id,
i.is_unique,
i.is_primary_key,
i.is_unique_constraint,
i.has_filter,
i.filter_definition,
ic.key_ordinal,
ic.is_included_column,
c.name AS column_name
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
INNER JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE
t.is_ms_shipped = 0
AND i.name = N'YourIndexName'
AND t.object_id = OBJECT_ID(N'dbo.YourTable')
ORDER BY
ic.is_included_column,
ic.key_ordinal,
ic.index_column_id;
Two indexes with similar names may still support different workloads because of key order, INCLUDE columns, filters, or uniqueness.
What the result means
| Field | Why it matters |
|---|---|
user_seeks |
Number of user-query seeks recorded against the index. |
user_scans |
Number of user-query scans recorded against the index. A scan is not automatically bad. |
user_lookups |
Bookmark/key lookup activity involving the index structure. |
user_updates |
Number of user write operations that required index maintenance. This is operation count, not rows changed. |
last_user_seek |
Last recorded user seek during the current DMV observation window. |
last_user_scan |
Last recorded user scan. |
last_user_lookup |
Last recorded user lookup. |
last_user_update |
Last recorded user write-maintenance event. |
total_user_reads |
Convenience calculation: seeks + scans + lookups. |
writes_per_read |
Triage ratio only. Helps highlight maintenance-heavy indexes but is not a drop rule. |
used_mb |
Current storage footprint of the index. |
What to look for
Zero reads + many writes
This is a strong review candidate if the observation window is long enough.
Before acting, verify:
- SQL Server has not restarted recently
- the database has not recently been restored, detached, or closed
- the index is not used by monthly/quarterly/year-end workload
- it is not required for uniqueness or constraint semantics
- no important query depends on it outside the observed period
Very low reads + very high updates
This can indicate maintenance overhead with little observed read benefit.
Prioritize large indexes on write-heavy tables for deeper review.
Heavy scans
Do not label the index bad because scans are high.
A scan can be correct for:
- large analytical queries
- low-selectivity predicates
- small tables/indexes
- ordered access
- covering access patterns
Inspect the execution plans that use it.
Many lookups
Frequent key/bookmark lookups can indicate that a nonclustered index gets SQL Server close to the required rows but does not cover the requested columns.
Possible next step:
- inspect the query
- examine lookup cost in the execution plan
- consider whether INCLUDE columns make sense
Do not blindly add every lookup column to the index.
High index size + low read activity
This combination deserves attention because it affects:
- storage
- buffer pool
- backup size
- index maintenance
- write cost
But still validate business workload before removal.
Similar indexes on the same table
Potential overlap can exist when indexes share:
- the same leading keys
- similar INCLUDE columns
- similar filters
Usage counters alone cannot tell you whether two indexes can be safely consolidated.
Compare definitions and actual query plans.
Production notes
Usage statistics reset
sys.dm_db_index_usage_stats is not permanent history.
Its counters are initialized again when the Database Engine starts.
Rows can also disappear when a database is detached or shut down.
Always record the observation-window start before labeling an index unused.
user_updates counts operations, not modified rows
One statement that updates 1 row increments the counter once.
One statement that updates 1,000,000 rows also increments it once.
Do not interpret user_updates as row-change volume.
Missing DMV row is not proof of permanent non-use
It means no usage row exists in the current collection window.
That may be because the index is unused, but it can also mean:
- recent restart
- recent index creation
- infrequent workload
- database lifecycle event
Permission requirements vary
On SQL Server and SQL Managed Instance, broad access to sys.dm_db_index_usage_stats requires server-state visibility.
For SQL Server 2022 and later, Microsoft documents VIEW SERVER PERFORMANCE STATE.
Earlier SQL Server versions use VIEW SERVER STATE.
Azure SQL Database has database/service-tier-specific permission behavior.
Memory-optimized indexes use different DMVs
sys.dm_db_index_usage_stats is not the right place to evaluate memory-optimized indexes.
Use the relevant XTP index statistics DMVs for memory-optimized workloads.
Usage count is not business value
A query executed once per day may be business-critical.
Do not optimize solely for frequency.
Common mistakes
1. Dropping every index with zero reads
This is the most dangerous interpretation of the DMV.
Zero reads means zero observed reads in the current statistics window, not “this index has no value.”
2. Ignoring the server restart time
An index may appear unused simply because the counters were reset recently.
Always establish the observation window.
3. Treating scans as bad
A scan can be the optimal access method.
Evaluate the query, selectivity, index width, row count, and execution plan.
4. Using user_updates as modified-row count
It counts operations, not affected rows.
Do not derive write volume from it.
5. Dropping primary-key or unique-constraint indexes
Indexes backing constraints are part of data integrity semantics.
Do not treat them like optional performance indexes.
6. Evaluating one index in isolation
An index can:
- support a critical query
- overlap another index
- provide ordering
- enforce uniqueness
- reduce blocking by shortening reads
- increase write cost
Review the workload and neighboring indexes together.
7. Using usage stats as historical monitoring
The DMV is cumulative only within its current lifetime.
If you need durable evidence, snapshot usage statistics periodically into your own monitoring store.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.