Tuesday, 3 July 2012

Find Index usage

/* How to find how many times indexes has been used */

/*Query which is used to find these indexes related data is mentioned below*/

select O.name as Table_Name, I.name as Index_Name, IU.user_seeks,

IU.User_Scans, IU.System_Seeks, IU.System_Scans

From sys.dm_db_index_usage_stats IU

inner join sys.objects O

ON IU.object_id = O.object_id

Inner join sys.indexes I

ON O.object_id = I.object_id

and IU.index_id = I.index_id

where I.name is not null

order by o.name, i.name

No comments:

Post a Comment