Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: IndexingDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Show read and write activity for every regular index
  5. 2. Find indexes with writes but no observed user reads
  6. 3. Find indexes with many writes relative to reads
  7. 4. Show last observed read and write times
  8. 5. Check how long the current DMV observation window has existed
  9. 6. Add index size to the usage view
  10. 7. Show the indexed columns before making a decision
  11. What the result means
  12. What to look for
  13. Zero reads + many writes
  14. Very low reads + very high updates
  15. Heavy scans
  16. Many lookups
  17. High index size + low read activity
  18. Similar indexes on the same table
  19. Production notes
  20. Usage statistics reset
  21. user_updates counts operations, not modified rows
  22. Missing DMV row is not proof of permanent non-use
  23. Permission requirements vary
  24. Memory-optimized indexes use different DMVs
  25. Usage count is not business value
  26. Common mistakes
  27. 1. Dropping every index with zero reads
  28. 2. Ignoring the server restart time
  29. 3. Treating scans as bad
  30. 4. Using user_updates as modified-row count
  31. 5. Dropping primary-key or unique-constraint indexes
  32. 6. Evaluating one index in isolation
  33. 7. Using usage stats as historical monitoring
  34. Next step
  35. 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.

Next step

Use the usage pattern to decide what to investigate next.

  • Zero reads + meaningful writes + long observation window → inspect the index definition, dependencies, Query Store, and business workload before considering removal.
  • Many reads + many writes → likely valuable; tune only if size or maintenance cost is a problem.
  • Many lookups → inspect execution plans and evaluate covering strategy.
  • Many scans → inspect workload selectivity and plans; do not assume the index is bad.
  • Large index + low reads → compare usage, size, overlap, and maintenance cost.
  • Several similar indexes → compare key order, INCLUDE columns, filters, and plan usage for possible consolidation.
  • Observation window is short → collect more evidence before changing anything.

← All cheat sheets