Search
  • Filip Holub

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

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!


40 views
  • Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon

© 2020 All rights reserved. PigeonSQL.com | Filip Holub

  • Twitter Social Icon
  • Facebook Social Icon
  • LinkedIn Social Icon