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. Let us see script for unused index.
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.
Anyway, the scripts is a good starting point. You should pay attention to User Scan, User Lookup and User Update when you are going to drop the index. The generic understanding is if this value is all high and User Seek is low, the index needs tuning. The index drop script is also provided in the last column.
-- Unused Index Script -- Original Author: Pinal Dave 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
If you need further help with this script, do leave a comment or send me email at my email address.
Note: It goes without saying, please test all the script on your development server and after they pass your test, deploy them on production server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
60 Comments. Leave new
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
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.
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
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
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
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.
In your script I noticed you opted out of dropping any unused unique indexes why?
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
Hi there Dear, are you in fact visiting this web site daily, if so
after that you will absolutely get nice knowledge.
check this guy out…..
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.
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
Can you plz provide queries for SQL Azure as some of the queries does’nt execute on SQL Azure
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.
you cannot simply run the script and drop unused indexes. You first need to ensure that you had the server up and running for atleast 3 weeks or a month.And then ensure that the database was created for a good time so that you have enough information.
Agree with Sasi.
Hi Pinal. How far back in time this list goes? I mean, this script shows unused indexes since the instance was last started? It means if we run this query right after the instance was restarted we’re going to see most of the indexes listed there as “unused”?
Filtering on database_id may lead to skipping indexes that have (not) been used when queried from other databases, for example, – in cross-database queries. I re-wrote your script to eliminate this flaw, as well as to check for index existence prior to dropping it. Also, removed “top 25” clause, because you really need to look out for all unused indexes, not just the top 25 ones.
Pinal,
I found the query very useful but discovered it suggests dropping some replication indexes.
example
syspublications nc3syspublications
syssubscriptions unc1syssubscriptions
May I suggest adding the following
AND o.is_ms_shipped = 1
Hi Pinal ,
Please provide inormation about indexs and when will won’t work . How to tune the queries related indexs and concepts of indexs.
Hi Pinal
We generally make Indexes by looking into query conditions e,g
Column used in query,column in where condition,joins,So why all indexes have been not used.and why all index objects not found in sys.dm_db_index_usage_stats.Please guide.
@Mahi – it all depends on plan cache.
Thank you for reply,i hv one more query regarding this .if i am creating new indexes for speedup execution of query n some of them not have been used by execution plan .those indexes should be drop???
@Mahi – If indexes are not used by ANY query then they can be dropped. YOU need to evaluate this before dropping it.
Thank you for your valuable reply..
My pleasure Mahi.
Hi
I have a question.
In the description is this text: “You should pay attention to User Scan, User Lookup and User Update when you are going to drop the index. The generic understanding is if this value is all high and User Seek is low, …”
Why is in query:
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC ?
I expect something like this:
ORDER BY dm_ius.user_seeks ASC, (dm_ius.user_scans + dm_ius.user_lookups + dm_ius.user_updates) DESC;
Thank you very much for the script, it is very useful. :-)
Martin
As per my understanding for unused index seek ,lookup,scan all values needs to be checked and if for particular index sum of all these values is zero then it is unused index,if seek values is zero then it means then opmizer nt found it useful , index scan also important as it is faster than table scan.so order of all these in given script is correct .
Thanks Mahi. You are correct.