Monday, August 29, 2016

Sql Server - Index Utilization Report

Index Utilization Report


One day my manager came and told me that he need Index Utilization Report.
He felt that during development of the project we have created so many indexes but he is not sure about how useful those are. So he wants a report which shows all the Indexes on user defined table or view and how effectively they are being used in our project.  So guys here is the code and screen shot of the report.  You can run this code on any database and get the report.


select
Obj.name as 'Table/View Name',
Obj.type,
Indx.name as 'IndexName',
Indx.index_id,
Indx.type_desc,
Indx.type,
 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


Feel free to share or modify it.
Let me know if you have any idea to improve this report.

I am adding few more useful columns , here is the updated Sql Script -



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

Thanks