Do you know how to verify SQL indexes are being used?
Last updated by Bahjat Alaadel [SSW] 4 months ago.See historySo you've created some indexes on your database tables. How can you tell if they are being used by your queries?
You'd be surprised to find out how often the indexes that have been defined on a table are not being used. As such it's worth checking that your indexes are being used.
Video: Verify your indexes are used | Bryden Oliver (4 min)
Ways to verify
If you know the exact query being run, you can simply run the query in SQL Server Management Studio or Azure Data Studio with "Include Actual Execution Plan" turned on. Then check the execution plan to verify what indexes were used.
The second option is to use the query below. It retrieves the index usage stats. The WHERE clause can be adjusted to ensure you only get the indexes you are interested in. Then you can run this query, then perform the operations in your application that you think should use the index, and then rerun this query. If the index was used you should see the UserSeeks, UserScans or UserLookups increase between the 2 runs.
SELECT
object_name(s.[object_id]) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
us.user_seeks AS UserSeeks,
us.user_scans AS UserScans,
us.user_lookups AS UserLookups,
us.user_updates AS UserUpdates
FROM
sys.dm_db_index_usage_stats us
INNER JOIN
sys.indexes i ON (us.object_id = i.object_id AND us.index_id = i.index_id)
INNER JOIN
sys.objects s ON us.object_id = s.object_id
WHERE
s.type = 'U' -- Filter for user tables only
-- AND i.type_desc = 'NONCLUSTERED' -- Filter for non-clustered indexes
-- AND (us.user_seeks = 0 AND us.user_scans = 0) -- Filter for unused indexes
ORDER BY
TableName, IndexName;
Be aware the statistics in this query are reset when the server is restarted or a user explicitly resets the statistics.