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
SELECT TOP 25
o.name
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
, 'DROP INDEX ' + QUOTENAME(i.name)
+
' 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
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND
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
GO

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

About these ads

17 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????

    mjfii

  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.

    Thanks,
    Gary

    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?

    Thanks.

    Leo

  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.

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

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