Thursday 5 April 2012

Looking for unused indexes in Sql Server 2008

 

In a long running project the database is changing day by day. We create new tables, optimize our queries, and of course, make new indexes. Usually when I finish an optimization process I forget an important step:

I always forget to check how many indexes I have that I don’t use anymore.

Sql server stores index statisctics in a sys.dm_ view named SYS.DM_DB_INDEX_USAGE_STATS. As the other sys.dm_ views, sql server clears statistics on every restart, so we have to check after missing indexes only for our database instance had run for significant time and we had collected enough statistics to decide if we delete an index or not.

Now after we have understood the basic, let’s see the query for showing our index statistics:

DECLARE @table_name varchar(100) = null

-- Uncomment the following line if you want to filter for a given table
--set @table_name = 'table_name'

SELECT
         OBJECT_NAME(I.[OBJECT_ID]) AS [OBJECT NAME],
         I.[object_id],
         I.[NAME] AS [INDEX NAME],
         sum(USER_SEEKS) as USER_SEEKS,
         sum(USER_SCANS) as USER_SCANS,  
         sum(USER_LOOKUPS) as USER_LOOKUPS,
         sum(USER_UPDATES)  as USER_UPDATES
     FROMSYS.INDEXES AS I
         LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
  WHEREOBJECTPROPERTY(I.[OBJECT_ID],'IsUserTable') = 1
    and (@table_name is null or OBJECT_NAME(I.[OBJECT_ID]) = @table_name)

group by OBJECT_NAME(I.[OBJECT_ID]),
         I.[object_id],
         I.[NAME]
    order by [OBJECT NAME], [INDEX NAME]

As we can see on our column names, we access seek, scan, lookup and index update statistics counted since the last start of our sql server instance. Our result can contain null statistics. In that case we don’t have any statistics about the given index since the last restart.

This script contains one parameter (@table_name). If we uncomment the 4th line, we can filter for indexes only on a given table.

No comments:

Post a Comment