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
select [Database]='ABC' ,so.name as 'TableName',
ISNULL(si.name,'No Index') as IndexName,
(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
inner join sys.columns sc on
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on so.name=c.TABLE_NAME
--Percentage usage of index : Database ABC
(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
Provides a robust breakdown of all indexes contained within a database allowing for quick analysis and identification of inefficient, redundant, unused, bloated, stale, and / or poorly architected indexes.
SQL Server Indexes need to be effective. It is wrong to have too few or too many. The ones you create must ensure that the workload reads the data quickly with a minimum of I/O. As well as a sound knowledge of the way that relational databases work, it helps to be familiar with the Dynamic Management Objects that are there to assist with your indexing strategy.