SQL SERVER – 2008 – Unused Index Script – Download

Click to Download Scripts

Performance Tuning is quite interesting and Index plays a vital role in it. A proper index can improve the performance and a bad index can hamper the performance.

Here is the script from my script bank which I use to identify unused indexes on any database.

Please note, if you should not drop all the unused indexes this script suggest. This is just for guidance. You should not create more than 5-10 indexes per table. Additionally, this script sometime does not give accurate information so use your common sense.

Any way, the scripts is good starting point. You should pay attention to User Scan, User Lookup and User Update when you are going to drop index. The generic understanding is if this values are all high and User Seek is low, the index needs tuning. The index drop script is also provided in the last column.

Click to Download Scripts

-- Unused Index Script
-- Original Author: Pinal Dave (C) 2011
AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
AND dm_ius.database_id = DB_ID()
i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

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

About these ads

20 thoughts on “SQL SERVER – 2008 – Unused Index Script – Download

  1. This does not consider indexes that were created because of a foreign key on the same column. What are your thoughts on ALWAYS having an index on a foreign key. As a rule of thumb, I always do this. Is there any reason I shouldn’t????


    • I know this is kind of old, but just in case anybody else out there is wondering, you need to be careful when considering foreign key constraints.

      Remember that the indexes created on them can still be used implicitly in INSERT/UPDATE/DELETE statements (even if it is not referenced in the WHERE/JOIN clause).

      Consider the case where you suddenly need to modify data and end up doing a mass update operation on a table with a foreign-key constraint. SQL will still need an index on the foreign-key if it isn’t already present in order to join on the foreign-key table and perform the check on the constraint, or else you will end up with a table scan. On a fairly large amount of data, this could be very costly!

      In general, I always ensure there is an index on all foreign-key columns as a result, even if they may not be used under ordinary circumstances, just in case I have to perform a data modification.

  2. Can you comment more on this part of the article:

    “You should pay attention to User Scan, User Lookup and User Update when you are going to drop index”

    I would appreciate a separate article addressing these items.


    ETL Developer

  3. Hi Pinal,

    Is that anyway can you provide the script to look at user tables and find out which many userseeks, userscan, userlook on Each Individual Index rather than UNUsedIndex script?



  4. Pingback: SQL SERVER – Query to Find Duplicate Indexes – Script to Find Redundant Indexes Journey to SQLAuthority

  5. Hey Pinal,

    That’s a great script (as yours always are), however I think you are missing another class of “missing indexes”, and that is indexes that don’t have stats at all. Rows are only written to dm_db_index_usage_stats when an index is used. If that index is totally un-used (even for writing to), it won’t appear in dm_db_index_usage_stats and therefore won’t be included in your script.

    Try this on a production system, you might be surprised by how many rows it returns:

    SELECT object_name(i.object_id) as tableName, i.name as indexName
    FROM sys.indexes i
    LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id AND s.database_id = db_id()
    WHERE objectproperty(i.object_id,’IsUserTable’) = 1 and i.index_id> 0
    AND s.object_id IS NULL
    AND i.is_Primary_Key = 0
    AND i.is_unique_constraint = 0
    AND i.is_unique = 0

    –Dave (Another CFer)

    • This was exactly the point I was going to make David but it looks like you’ve beaten me to it ;) the INNER JOIN kinda negates the point of looking for indexes that aren’t used.

      Dan B

  6. Hi Pinal,

    Can you please explain what are the following column names suggests:

    dm_ius.user_seeks AS UserSeek
    dm_ius.user_scans AS UserScans
    dm_ius.user_lookups AS UserLookups
    dm_ius.user_updates AS UserUpdates

    e.i. how does these values changes, what factors changes all above values ?

    Thanks in advance.

  7. Pingback: SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Identify Numbers of Non Clustered Index on Tables for Entire Database « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  10. Hi Pinal,

    Thanks a lot for such a nice script, but i have one doubt. Let there is one useful index on a table. After creating database , table & index. Not a single query executed on table, which one used that index. Let I run script provided by you. Did your script treat that index as
    unused or useful.

    Please revert!!
    Thanks in advance.
    Amit Kr. Gupta

  11. You might also want to add:
    AND i.has_filter = 0

    because a dropping a filtered index could have a detrimental effect on the application and you should exercise extreme caution when dropping a filtered index.

  12. I prefer to use it with AND i.is_unique = 0 as this doesn’t then look at unique indexes either. Therefore there is no risk of dropping a unique index.

    Hope that helps

  13. I used the same query and found some 4 unused indexes on a particular table from production db.I went ahead and dropped all of them.After this change when code was given for internal testing, we started getting time out error in QA environment(where load is pretty less). Finally I had to recreate the dropped indexes to fix the time out issue.

    Please let me know if there is anything wrong with the query.

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