Index Usage Stats – Indexes Used, Unused and Updated

While working on Performance Optimization and Index tuning many Developers ends up in creating some (or even many) unnecessary Indexes based upon various SQL Queries. Some or many of those Indexes might not be used at all by the SQL Query Optimizer. But even if they are used you may have to Trade-off with your ETL performance going down, as the CRUD (CREATE, READ, UPDATE, DELETE) operations are going to taking more time to update those new Indexes.

So, while creating new Indexes you will have to plan very carefully, decide and balance out things so that your Data retrieval is fast and on the same hand your ETLs are also not affected much.

SQL Server provides some DMVs (Dynamic Management Views) and DMFs (Dynamic Management Functions) to get this information from SQL engine.

–> To know how the Indexes are getting used we can use sys.dm_db_index_usage_stats DMV and this will provide us information on how many times the Index was used for SEEK, SCAN & LOOKUP operations. Check the Query and its output below:

SELECT
DB_NAME(database_id) AS DATABASE_NAME,
OBJECT_NAME(ius.object_id) AS TABLE_NAME,
ids.name AS INDEX_NAME,
ius.user_seeks AS SEEK_COUNT,
ius.user_scans AS SCAN_COUNT,
ius.user_lookups AS LOOKUP_COUNT,
ius.user_seeks + ius.user_scans + ius.user_lookups AS TOTAL_USAGE,
ius.last_user_seek AS LAST_SEEK_COUNT,
ius.last_user_scan AS LAST_SCAN_COUNT,
ius.last_user_lookup AS LAST_LOOKUP_COUNT
FROM sys.dm_db_index_usage_stats AS ius
INNER JOIN sys.indexes AS ids
ON ids.object_id = ius.object_id
AND ids.index_id = ius.index_id
WHERE OBJECTPROPERTY(ius.object_id,'IsUserTable') = 1
ORDER BY DATABASE_NAME, TOTAL_USAGE

–> Now if we want to know the maintenance overhead on the new Indexes we created, like every time a related Table is updated the Indexes are also updated. We can check by using sys.dm_db_index_operational_stats DMF and it will show how many INSERT, UPDATE & DELETE operations are happening on particular indexes. Check the Query and its output below: