How to find index usage

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

 index_columns

 ,object_name

 ,( 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