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 (https://blog.sqlauthority.com)
24 Comments. Leave new
very nice! thanks for this! what would it mean when it shows index name as null and index_id = 0 ? I am using 2005.
hi pinal,
plz let me know what does the field index_id represent.
i mean the value 0,1,2
tnks!!!
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?
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.
Hi Pinal,
If i want to find out which objects are not used since long time. so what to do?
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.
Nice, Is there any option to see unused index in sql server 2000
Hello, how come your query is showing me in the indexname column statistic Names?
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.
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.
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.
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.
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
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
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.
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..
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.
–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];
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
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.
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms188755(v=sql.90).
Can anyone please clarify.
Thanks!
Amit.
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..