SQL SERVER – 2005 – Find Unused Indexes of Current Database

Simple but accurate following script will give you list of all the indexes in the database which are unused. If indexes are not used they should be dropped as Indexes reduces the performance for INSERT/UPDATE statement. Indexes are only useful when used with SELECT statement.

Script to find unused Indexes.

USE AdventureWorks
GO
DECLARE @dbid INT
SELECT
@dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO

Note: This is only for SQL Server 2005, it will not run for previous version of SQL Server.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

24 thoughts on “SQL SERVER – 2005 – Find Unused Indexes of Current Database

  1. The query returns clustered indexes and heaps as well.
    I would not necessarily drop clustered indexes even if they are not used. At least they should be identified as such to inform the user.

    What if a index is not used by any select statements, but is updated when rows are inserted into a table – will it be included in the above query?

    Like

  2. Pinal,

    If we are recreating indexes every week as part of maintenance AND we recycle the server boxes (and SQL Server contained) at least once a week as part of normal maintenance, will this script accurately reflect indexes not being used?

    Inquiring minds and junior DBAs want to know.

    Like

  3. Glen – I’d love to know the answer. It’s probably using IDs behind the scenes, so a new index with the same name wouldn’t get the same history.

    Darshan – if you mean tables and SPs and Functions, there is no way currently; 2008 does offer that functionality, and it’s hopefully going to be a glorious thing, since you could theoretically get rid of a lot of cruft that way.

    Like

  4. Thanks Pinal.
    I want to point out one minor detail only. The script fails on databases that have case sensitive collation. The objects SYS.INDEXES and SYS.OBJECTS for example, are not found in such cases and the script fails to execute.

    Like

  5. A useful script but I don’t agree with your assertion that indexes are only useful for select statements and are not used for updates – this is obvioulsy nonsense.

    By definition an update must act on an existing row – so SQL server must find that row first right ? Same goes for a delete – so an index can help in those cases.

    The Sajal Dam book, Performance Tuning distilled (SQL 2000 edition) has good information on this.

    Like

  6. I disagree that the query gives the desired results.

    The premise is that an index that does not appear in SYS.DM_DB_INDEX_USAGE_STATS is not in use.

    This will of course be true, but there may be many more indexes that are not used properly but still appear in this DMV.

    You say that the reason to drop unused indexes is because they reduce the performance of inserts and updates, but when this actually occurs, i.e. a single update is written to the table data pages and also the index data pages, then this is logged in the SYS.DM_DB_INDEX_USAGE_STATS DMV.

    This inefficient use of the index that you are trying to locate is the actual cause of it not being identified by the query.

    Any indexes shown by the query are not in use but are also not doing any harm to performance, although they do obviously take up drive space.

    Like

  7. I agree with Ben. a better script would be as follows:

    select i.name as indexname,
    object_name(s.[object_id]) as tablename, s.database_id, s.[object_id], s.User_seeks, last_user_seek, user_scans, last_user_scan,
    user_lookups, Last_user_lookup, user_updates, last_user_update
    from sys.dm_db_index_usage_stats s
    join sys.indexes i on s.object_id = i.object_id
    and s.index_id = i.index_id
    where OBJECTPROPERTY(s.OBJECT_ID,’IsUserTable’) = 1
    and user_scans + user_lookups + user_seeks = 0

    No scans, lookups, or seeks means no one is using the index.

    Like

  8. LAST Line of BRUCE’s script

    and user_scans + user_lookups + user_seeks = 0

    RESULTS still gives indexes that were last used and user_updates

    SHOULD be

    and user_scans + user_lookups + user_seeks + user_lookups + user_updates = 0

    In order for it to show never used or updated

    Just tried it and I get better results to know if the index was really unused

    Like

  9. The column user_updates only reflects the maintenance of the index due to insert, delete or update activity on the data pages, but it does not mean that the user ever used the index. If user_scans + user_lookups + user_seeks = 0 then it really means that the index has no practical use, and it does not help a query to access the data in a more efficient way.

    Hope this helps,

    César Buzzo

    Like

  10. I generally look at the ratio of updates and seeks,scans. Generally speaking indexes with a ratio of 75% updates and 25% scans,seeks goes out the window. Unused indexes on a table that is heavily inserted will consume more CPU as inserting new rows in the table is also inserting new rows in the index., and as a Index is a sorted data structure SQL uses CPU not disk to create a place to insert the index.

    Seeks on non-clustered indexes for me is almost worse than a table scan. If you scan a non-clustered index you read the entire index sequentially one row at a time. If the row matches the query criteria you now have to follow the pointer stored in the index to the actual row on the table. In other words you read the non-clustered index then the clustered index/table(your clustered index is your table). This is two I/O operations.

    In a table/clustered index scan you read the entire table and get the rows there is no pointer to follow and you get the value. One operation.

    Like

  11. Guys Please help me up with this thing.. i have executed the query written by Bruce Salvo (above)..
    what is exactly user_updates.. i have a statistics saying that

    user_scan+user_seek+user_lookups=0

    but user_updates= 44492

    so what exactly it says.. plz help me

    any help wil be appeciated..

    Like

    • It means that the indexes are getting updated but not read in other words they are just doing nothing but slowing you down.

      Every update and insert will affect all indexes on that table however some indexes never get used because their selectivity is too low. Simple rule of thumb is Selectivity of index = number of rows/number of distinct rows.

      Like

  12. –Find missing index
    USE AdventureWorks;
    GO
    — run some queries
    SELECT City, ModifiedDate
    FROM Person.Address
    WHERE StateProvinceID DATEADD(yyyy, -5, GETDATE());
    GO
    SELECT City, StateProvinceID, PostalCode
    FROM Person.Address
    WHERE StateProvinceID = 15733;
    GO
    — get the missing indexes that would be beneficial for speeding up above queries
    SELECT D.index_handle, [statement] AS full_object_name, unique_compiles, avg_user_impact, user_scans, user_seeks, column_name, column_usage
    FROM sys.dm_db_missing_index_groups G
    JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
    JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
    CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC
    ORDER BY D.index_handle, [statement];

    Like

  13. Here is a version that should give you the amount of space being used by the unused indexes…please comment as I am not 100% sure…

    SELECT i.name as UnusedIndexName,
    object_name(s.[object_id]) as tablename,

    CASE WHEN ps.usedpages > ps.pages THEN (ps.usedpages – ps.pages)
    ELSE 0 END * 8 AS IndexSizeKB,
    CASE WHEN ps.usedpages > ps.pages THEN (ps.usedpages – ps.pages)
    ELSE 0 END * 8 / 1024 AS IndexSizeMB,
    CAST(CASE
    WHEN ps.usedpages > ps.pages THEN (ps.usedpages – ps.pages)
    ELSE 0
    END * 8.0 / 1024.0 / 1024.0 AS DECIMAL(18,2)) AS IndexSizeGB,
    s.database_id,
    s.[object_id],
    s.User_seeks,
    last_user_seek,
    user_scans,
    last_user_scan,
    user_lookups,
    Last_user_lookup,
    user_updates,
    last_user_update
    FROM sys.dm_db_index_usage_stats s
    JOIN sys.indexes i on s.object_id = i.object_id
    AND s.index_id = i.index_id
    LEFT OUTER JOIN (
    SELECT
    OBJECT_ID,
    index_id,
    SUM (used_page_count) usedpages,
    SUM (
    CASE
    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
    ELSE lob_used_page_count + row_overflow_used_page_count
    END
    ) pages
    FROM sys.dm_db_partition_stats
    GROUP BY object_id, index_id
    ) ps on i.index_id = ps.index_id AND s.object_id = ps.object_id
    WHERE OBJECTPROPERTY(s.OBJECT_ID, 'IsUserTable') = 1
    AND user_scans + user_lookups + user_seeks = 0
    ORDER BY tablename

    Like

  14. Hi,

    Its not clear, microsoft states 2 statements on “user_updates”, first one is “Number of updates by user queries.”, Secondly, “The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view”.

    If i am not wrong then indexes does help in locating the rows during updates on a table (So, above first statement verified). However, if second statement is true, then it means user_updates doesn’t actually counting the number of updates done using that particular index.

    http://msdn.microsoft.com/en-us/library/ms188755(v=sql.90).aspx.

    Can anyone please clarify.

    Thanks!
    Amit.

    Like

  15. User_Updates is only for Indexes.The number of times the index was updated(includes all updates, deletes and inserts). If the index has helped in updating the row, it would be counted under user_seeks or user_scans or user_lookup. what it basically says is, If someone inserts a row to the table, a corresponding row should be entered in the index as well. This causes a user_updates(ENTERING A NEW ROW to INDEX).Similarly, if there was a update in the index column, it has to be updated in the index as well, this comes under user_updates as well..same with deletes…if the index was used in the query, it will be counted under user_seeks or user_scans or user_lookup. Hope this helps..

    Like

  16. Hi,
    I have found Unused Indexes for table-wise in particular database, after that have to delete all the indexes? or what.
    If i have to delete all indexes means how can I delete all unused indexes at a time.

    Like

    • Common yaar please answer me anybody, i am getting the issue daily like server is solw, so that i have checked indexes but here i found unused indexes, after that what should i do i don’t know.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s