Download Missing Index Script with Unused Index Script and Duplicate Index Script
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.
Download Missing Index Script with Unused Index Script
-- 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)
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
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
[...] SQL SERVER – 2008 – Unused Index Script – Download [...]
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.
[...] Server Interview Questions and Answers ISBN: 1466405643 Page#149 Unused Index Script – Download Missing Index Script – Download Disable Clustered Index and Data Insert Generate Report for Index [...]
[...] workload (here is the blog post to identify them), drop unused indexes with careful observation (here is the script for it), identify missing indexes and after careful testing add them (here is the script for it). Even [...]
In your script I noticed you opted out of dropping any unused unique indexes why?
[...] Missing Index Script and Unused Index Script – Download [...]