|
For
every Developer and DBA index maintenance is very important task. Since the changing
requirements of customers Developers has to regularly create new indexes for
new reports and queries. As the system becomes old and database becomes large and Developers changed time to time due to different
reasons, its become hard to maintain track of all index usages. Some old index which may have been irrelevant after change
in business requirements.
In
SQL 2000 it was not possible easily to find which indexes are used regularly and which are not used regularly. Since each
update , delete and insert also do changes in index and index play important part in transaction and locking proper
index maintenance requires. So for every DBA its required to get report which index are being used by application
or system and which are not.
In SQL Server
2005 Dynamic Management View sys.dm_db_index_usage_stats contains counts of different
types of index operations and the time each type of operation was last performed.
Every operation like seek, scan, lookup, or update on the specified index is counted as a use of that index
and increments the corresponding counter in this view.
The counters are resert everytime when the
SQL Server service is started.
I have tried some code as below to
find index usage and find unused indexes or indexes which are updated only but not used in index seek or update
or lookup operation.
declare @dbid int
--To get Datbase ID
set @dbid = db_id()
--This query will
order result according to index usage, which can help you to find which indexes are highly used and requires more attention
--
I am not using index upadate count over here since we requires details of index usage for search operations
-- Join
this view to Sys.indexes to get name of indexes. Index name will be null where there is no index exist and table scan happens.
--Here index_name and
index_column may come NULL, where no index exists and SQL Server refers table directly which is called heap.
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 objectproperty(d.object_id, 'IsIndexable') = 1
order by
(user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups)
desc
-- To find index scans
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 objectproperty(d.object_id, 'IsIndexable')
= 1
and ( d.user_scans +d.system_scans ) > 0
order by
d.user_scans +d.system_scans
--To find unused indexes
-- This query will give you details of unused indexes with details like object_name index_name,index_type
, index columns .
-- We are considering only indexes so we are omitting index_type heap, clustered since it represents
tables
select object_name(i.object_id) object_name,
i.name index_name,
i.index_id index_id,
i.type_desc type_desc,
c.index_columns
from
sys.indexes i left outer join sys.dm_db_index_usage_stats d
on d.object_id=i.object_id and i.index_id=d.index_id
and d.database_id = @dbid
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 = i.index_id and c.object_id = i.object_id
where objectproperty(i.object_id, 'IsIndexable')
= 1
and d.index_id is null and i.type_desc not in ('heap','clustered')
-- To find only indexes which are only updated but not used in index seek, lookup or scan.
select object_name(i.object_id) object_name,
i.name index_name,
i.index_id index_id,
i.type_desc type_desc,
c.index_columns,
d.user_updates,
d.user_seeks,
d.user_scans,
d.user_lookups,
d.system_updates,
d.system_seeks,
d.system_scans,
d.system_lookups
from
sys.indexes i inner join sys.dm_db_index_usage_stats d
on d.object_id=i.object_id and i.index_id=d.index_id
and
d.database_id = @dbid 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
= i.index_id and c.object_id = i.object_id
where objectproperty(i.object_id, 'IsIndexable') = 1
and i.type_desc not
in ('heap','clustered')
and (d.user_updates > 0 or d.system_updates > 0) and d.user_seeks = 0
and d.user_scans
= 0 and d.user_lookups = 0
and d.system_seeks=0
and d.system_scans = 0 and d.system_lookups = 0
Regards
Amish Shah
|
