Technical Article

Aggregating Index usage information

,

This script aggregates index usage stats using the Dynamic Management Views in SQL SERVER 2005.

This view can then be used to analyse the indexes like calculating the percentage usage of indexes relative to the table.

--Aggregate index usage in a VIEW
CREATE view Index_Usage_Info
As
select [Database]='ABC' ,so.name as 'TableName',
     ISNULL(si.name,'No Index') as IndexName,
 si.type_desc,sc.name ColumnName,spi.user_seeks,
     spi.user_scans,spi.user_lookups,spi.user_updates,
 (user_seeks+user_scans+user_lookups+user_updates) as 'IndexUsage '
from sys.objects so inner join sys.indexes si on so.object_id=si.Object_id
inner join sys.dm_db_index_usage_stats spi on spi.Object_id=so.Object_id
inner join sys.index_columns sic on 
    sic.object_id=si.object_id and 
    sic.index_id=si.index_id
inner join sys.columns sc on 
    sc.Column_id=sic.column_id and 
    sc.object_id=sic.object_id
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on so.name=c.TABLE_NAME
where so.type<>'S' 

--Percentage usage of index : Database ABC
select b.TableName,b.IndexName,
(cast(sum(b.IndexUsage) as float(2))/
(select cast(sum(a.IndexUsage) as float(2)) from Index_Usage_Info a where a.tablename = b.TableName) 
)*100 As 'Percentage Usage'
from Index_Usage_Info b
where b.Indexusage > 0
group by b.indexname,b.tablename
order by b.tablename,b.indexname DESC

Rate

3.33 (6)

You rated this post out of 5. Change rating

Share

Share

Rate

3.33 (6)

You rated this post out of 5. Change rating