locked
What indexes to remove RRS feed

  • Question

  • Hi,

    I am using  dm_db_index_usage_stats dmv to query for the indexes that are not used. I find two kinds of queries on google search

    1. Query that returns indexes from indexUsageStats but have 0 reads( These have lots of updates)

    SELECT TableName = OBJECT_NAME(s.[object_id]),

           SchemaName=SCHEMA_NAME(o.[schema_id])

            ,IndexName = i.name

            ,user_updates   

            ,i.is_primary_key

            --,OBJECTPROPERTY(s.[object_id], 'IsMsShipped')

            --,user_seeks

            --,user_scans

            --,user_lookups

    FROM   sys.dm_db_index_usage_stats s

    JOIN sys.objects O ON  s.[object_id] = O.[object_id]

    JOIN sys.indexes i ON  s.[object_id] = i.[object_id]

        AND s.index_id = i.index_id

    WHERE   OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

        -- Only ISV defined.

        AND user_seeks = 0

        AND user_scans = 0

        AND user_lookups = 0

        AND i.name IS NOT NULL -- Ignore HEAP indexes.

    ORDER BY user_updates DESC

     

    1. Query that returns indexes that do not exist in indexUsageStats at all

    SELECT  OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,

            OBJECT_NAME(I.OBJECT_ID) AS ObjectName,

            I.NAME AS IndexName       

    FROM    sys.indexes I  

    WHERE   -- only get indexes for user created tables

            OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1

            -- find all indexes that exists but are NOT used

            AND NOT EXISTS (

                        SELECT  index_id

                        FROM    sys.dm_db_index_usage_stats

                        WHERE   OBJECT_ID = I.OBJECT_ID

                                AND I.index_id = index_id

                                -- limit our query only for the current db

                                AND database_id = DB_ID())

    ORDER BY SchemaName, ObjectName, IndexName

     

    I think I can consider all the indexes that both of these queries return as ones which are not used and can be removed from database after some analysis. Can someone suggest if this approach would be appropriate or which one from the above to consider for removing indexes.

    Thanks,

    Hari

    Thursday, August 23, 2012 2:08 PM

Answers

  • Hi HariAdu,

    I would suggest you to follow my blog post that will guide you how you can remove the duplicate indexes.

    Find unused indexes using sys.dm_db_index_usage_stats

    You basically need to consider the following when removing unused indexes:

    Key considerations when using sys.dm_db_index_usage_stats:

    • SQL Server cache is flushed whenever you restart SQL Server instance, and is not persisted across instance restarts.
    • All cache entries for indexes in a database are removed when the database status is changed to read_only.
    • Rebuilding an index clears the stats from SQL Server cache for the index involved.
    • It is important to make sure that index monitoring is performed over a representative time period. If you only monitor indexes during specific time frames you may incorrectly highlight indexes as being unused. The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations.

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Chintan982 Thursday, August 23, 2012 2:36 PM
    • Marked as answer by Iric Wen Friday, August 31, 2012 8:17 AM
    Thursday, August 23, 2012 2:19 PM