Share via

Quick Tips for your indices, is it used? Is it overhead ? using dm_db_index_usage_stats

  • Article

 A quick overview about your indices that you should execute from time to time to make sure that

1. Is all my indices used

2. What’s the useless indices

3. Am I indexing this table correct or the engine is doing a lot of scans over it

First:

to know more about your indices use this query

select OBJECT_NAME(object_id, “YOUR DATABASE ID” ), index_id, user_seeks, user_scans, user_lookups

from sys.dm_db_index_usage_stats

order by object_id, index_id

 

Index_id: 0 Heap , 1 Clustered index, 1 < non-clustered index

User_seeks: the amount of the seeks in the index

User_scans: Engine couldn’t use the index and scanned the table (I WANT IT SMALL NUMBER)

User_lookups: Engine used my index for the query (I WANT IT A HIGH NUMBER)

So Scans Vs. Lookups you need the Engine to lookup your index not scanning the whole table ...!!

 

Second:

Do you have useless indices that needed to be updated each time the engine work on the table and you don’t use it so removing it will enhance the performance?

Use this query

select object_name(object_id), i.name

from sys.indexes i

where  i.index_id NOT IN (select s.index_id

                          from sys.dm_db_index_usage_stats s

                          where s.object_id=i.object_id and

                          i.index_id=s.index_id and

                          database_id = “YOUR DATABASE ID” )

order by object_name(object_id) asc

 

Those are your indices that not been used since engine started??

Think of removing them …!!!