Identify mostly used Indexes in SQL Server


To identify the most used indexes in your DB run the following query. This will help you to identify whether or not your indexes are useful and used.

declare @dbid int
–To get Datbase ID
set @dbid = db_id()

select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*
from sys.dm_db_index_usage_stats d
inner join sys.indexes s
on d.object_id = s.object_id
and d.index_id = s.index_id
left outer join
(select distinct object_id, index_id,
stuff((SELECT ‘,’+col_name(object_id,column_id ) as ‘data()’ FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH (”)),1,1,”)
as ‘index_columns’ FROM sys.index_columns t1 ) c on
c.index_id = s.index_id and c.object_id = s.object_id
where database_id = @dbid
and s.type_desc = ‘NONCLUSTERED’
and objectproperty(d.object_id, ‘IsIndexable’) = 1
order by
(user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups) desc

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: