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. Removing unused indexes helps your INSERT and UPDATE statements.

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.

Check This Before You Drop Unused Indexes

The script depends on sys.dm_db_index_usage_stats, and that view starts empty every time the SQL Server service restarts. If your server was restarted last night, almost every index looks idle this morning. Before I trust the list, I check how long the server has been running, and I prefer to look after a full business cycle, including month-end jobs and reports that run only once a quarter.

A few more things to keep in mind:

• The script lists indexes with no row in the view at all. An index can also have a row with many updates and zero seeks, scans or lookups. That index costs you on every write and helps no reads, so look at user_seeks, user_scans, user_lookups and user_updates together.
• Leave primary keys and unique indexes alone. Even if no query reads them, they enforce rules on your data.
• Rows with an index_id of 0 are heaps, not indexes, so skip them.
• Script out the index definition before you drop it, so you can put it back in a minute if something slows down.

While you are at it, look for overlapping indexes as well. An index on (CustomerID) is often covered by another one on (CustomerID, OrderDate). The narrower one may show few reads only because the wider one does its work, and keeping both means every insert and update maintains two structures instead of one.

When you are ready, drop them in small steps, a few indexes at a time, and watch the workload for a while. If a report suddenly takes much longer, you know exactly which change to undo.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.