Is this Index useful ?

Creating indexes on tables always been a tricky question. Creating a index does not mean that optimizer will use that index to solve queries. SQL Server 2005 introduced a dynamic management view sys.dm_db_index_usage_stats to capture the use of indexes by SELECT, INSERT, UPDATE and DELETE. Thus any index or table which does not exists in this view is overhead to system and not useful.

Following query will return all the indexes and tables which never get used by SQL Server since the service started. The best way to check is to create index and run the data work load or regression test that should make use of all indexes and run following query.see full article on SQL Server Customer Advisory Team (CAT) How can SQL Server 2005 help me evaluate and manage indexes

SELECT  

            ObjectName = OBJECT_NAME(ind.object_id),

            IndexName = ind.name

FROM

            Sys.Indexes ind

            INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id

WHERE

            OBJECTPROPERTY(obj.object_id,‘IsUserTable’) = 1

            AND NOT EXISTS

            (

                  SELECT

                        1

                  FROM

                        Sys.dm_db_index_usage_stats usg

                  WHERE

                        usg.object_id = ind.object_id 

                        AND usg.index_id = ind.index_id

                        AND ind.index_id = usg.index_id

            )

ORDER BY

            ObjectName,

            IndexName

About these ads