How to Check Index Usage in SQL Server And What to Do With It

Indexes can be the secret sauce behind fast SQL queries or the hidden reason your inserts and updates are sluggish. They help the database find data faster, but when unused or poorly designed, they eat up space and processing time.

In this post, we’ll walk through:

• How to check index usage
• What the results actually mean

The Script To See What Your Indexes Are Doing

Here’s a simple but powerful SQL snippet that shows how each index on your tables is being used:

SELECT
tbl.name AS table_name,
idx.name AS index_name,
idx.index_id,
idx.type_desc,
idx.is_primary_key,
idx.is_unique,
s.user_scans,
s.user_seeks,
s.user_lookups,
s.user_updates,
s.last_user_seek,
s.last_user_scan,
s.last_user_update
FROM sys.indexes idx
JOIN sys.tables tbl
ON idx.object_id = tbl.object_id
LEFT JOIN sys.dm_db_index_usage_stats s
ON s.index_id = idx.index_id AND s.object_id = idx.object_id
ORDER BY tbl.name, idx.name;

What Does This Tell Us

– index_name
Name of the index (or NULL if it’s a heap—no clustered index).

– index_id
A numeric ID that uniquely identifies the index within a table. Useful when distinguishing multiple indexes on the same table.

– type_desc
Tells you if it's CLUSTERED, NONCLUSTERED, or a HEAP.

– is_primary_key
1 if the index enforces a primary key constraint.

– is_unique
1 if the index enforces uniqueness of values.

– user_seeks
Number of times SQL Server used this index to do a targeted row lookup. Key column when assessing index usage effectiveness.

– user_scans
Number of full scans using the index. Often less efficient.

– user_lookups
Counts how often SQL Server had to fetch extra columns from the base table after using a non-clustered index. It happens when the index doesn't include all the columns your query needs.

– user_updates
Number of times the index was modified due to INSERT, UPDATE, or DELETE operations.

– last_user_ columns*
When the index was last used (or updated). Helps assess how recently it’s been active.

Example: Analysing 5 Indexes

Let’s look at an output showing usage of five indexes:

Which Indexes Are Working and Which Aren’t?

✅ Strong Index

• idx_BI_Region_Score
  • 12 seeks and recent usage = it’s doing its job!
  • Small number of scans and updates = efficient.
  • Definitely worth keeping.

❓ Index Worth Reviewing

• PK_consumer and PK_workforce
  • Clustered indexes with no recorded activity.
  • This might mean:
    • No recent queries use them directly
    • Queries target different indexes
    • Or they simply haven’t been used since last server restart

What About Heap?

You might notice in the results that some rows have type_desc = HEAP and show no user_seeks at all.

A heap is simply a table without a clustered index. That means the rows are stored without any particular order, and there’s no underlying structure for SQL Server to "navigate" through efficiently. Seeks rely on order or structure like flipping straight to the right page in a book using the index. In a heap, SQL Server has no choice but to scan the entire table if there's no suitable non-clustered index that can help.

So, it’s not that something is broken it’s just how heaps work:

• If a query filters on a column that’s not covered by a non-clustered index, SQL Server will scan the heap.
• If a suitable non-clustered index exists, the seek will be recorded on that index, not on the heap.

Keep What’s Needed, Drop the Rest

Indexes are crucial for speeding up database queries, making data retrieval efficient and improving overall performance.

However, unused indexes can become a burden as they consume storage, slow down write operations, and add unnecessary overhead to database maintenance.

Regularly reviewing and dropping indexes that aren’t actively used helps keep your database lean, fast, and easier to manage.