top of page

How to Identify Unused Indexes?

If you need find unused indexes you can try system viewes SYS.dm_db_index_usage_stats.

Information is displayed since the last reboot instances. In my case I often use two of these SELECT statement.

1) with filter DATABASE_ID,OBJECT_ID and INDEX ID

2) with filter DATABASE_ID

identify unused indexes - dm_db_index_usage_stats

database_id - ID of the database on which the table or view is defined.

object_id - ID of the table or view on which the index is defined.

index_id - ID of the index.

user_seeks - Number of seeks by user queries.

user_scans - Number of scans by user queries. This represents scans that did not use 'seek' predicate.

user_updates - Number of updates by user queries. This includes Insert, Delete and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count will increment by 1

More information od - MSDN

Bye guys!

Comments


RECENT POST
bottom of page