Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Index Utilization Summary

By Devendra Thakur, 2017/04/20

Use this script to get the list of all Indexes in your database and how effective they are.

select
Obj.name as 'Table/View Name',
Obj.type_desc As 'TableType',
Indx.name as 'IndexName',
Indx.index_id,
Indx.type_desc,
Indx.is_primary_key,
Indx.is_unique,
Indx.is_unique_constraint,
Indx.has_filter,
 IUS.*,
 IOS.*
from
sys.INDEXES Indx inner join sys.objects Obj on Obj.object_id=Indx.object_id
left join SYS.DM_DB_INDEX_USAGE_STATS IUS on Indx.index_id=IUS.index_id and IUS.object_id=Obj.object_id
left join
SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) IOS      
         ON Indx.[OBJECT_ID] = IOS.[OBJECT_ID]
            AND Indx.INDEX_ID = IOS.INDEX_ID
where (Obj.type_desc='USER_TABLE' or Obj.type_desc='VIEW')
order by Obj.name,Indx.Name,
IUS.user_updates,IUS.user_lookups,IUS.user_seeks,IUS.user_scans  DESC

Total article views: 557 | Views in the last 30 days: 36