• Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon
  • Grey Google+ Icon

© 2016 - 2019 All rights reserved. PigeonSQL.com | Filip Holub

How to Identify Unused Indexes?

January 22, 2017

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!

 

Please reload

RECENT POST
Please reload

  • Facebook Social Icon
  • Google+ Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon