Do you have rarely used indexes that are hurting performance of writes?


We know that indexes are necessary for query performance but comes with overhead during DML operations. Usually, the overhead is worth the performance gain we get out of them.

What I needed was to find and consider for removal indexes that incur heavy maintenance overhead, but are only lightly used for queries or enforcing constraints.

Here is an a version of query to find unused or lesser used indexes. It looks for non-unique indexes with large number of DMLs with ratio of writes vs reads is relatively high (I am using 10 but at this point its an arbitrary starting point).

WITH cte

AS (SELECT Db_name(iu.database_id)                   db_name,

Object_name(iu.object_id, iu.database_id) object_name,

i.NAME                                    index_name,

i.type_desc                               index_type,

Sum(iu.user_seeks) + Sum(iu.user_scans)

Sum(iu.user_lookups)                    total_user_reads,

Sum(iu.user_updates)                      total_user_writes

FROM   sys.dm_db_index_usage_stats iu

INNER JOIN sys.indexes i ON i.object_id = iu.object_id

AND i.index_id = iu.index_id

WHERE  iu.database_id = Db_id()

AND i.index_id > 0

AND i.is_unique = 0

GROUP  BY iu.database_id,





total_user_writes / total_user_reads write_to_read_ratio

FROM   cte

WHERE  1 = 1

AND total_user_writes > 1000000

AND total_user_reads  < 1000

AND ( total_user_writes / NULLIF(total_user_reads,0) > 10

OR total_user_writes / total_user_reads IS NULL )

ORDER  BY write_to_read_ratio DESC

Please make sure to test before actually deleting any index.

Original post (opens in new tab)


5 (1)




5 (1)