1/9/2012 11:35:17 AM
Ritesh Medhe -
OBJECT_NAME(i.[object_id]) AS [Table name] ,
CASE WHEN i.name IS NULL THEN '<Unused table>' ELSE i.name END AS [Index name]
sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
i.index_id NOT IN (
FROM sys.dm_db_index_usage_stats AS s
WHERE s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
AND database_id = DB_ID() )
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC;
If you liked the posts, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks
Ritesh Medhe (Member since: 3/31/2011 11:34:25 AM)
Ritesh Medhe is a Microsoft Certified DBA & a BI professional with over 8 years of experience.
Expertise: Performance optimization & SQL Server Administration
View Ritesh Medhe 's profile
Just add i.type_desc as one of the selected fields and it shows it works on heap and nonclusterd indexes. You'll need to test it to determine if its "reporting correctly".
Hi Shannon, The script does show unused heaps, Script would return <unused table> under column index name no matter whether the table is heap/have CI or NCI
Just one problem: sys.dm_db_index_usage_stats is initialized to empty whenever the SQL Server service starts, whenever a database is detached or is shut down. This means index utilization stats are only valid since the last time the SQL Server service was started.
Is is very possible to have indexes that support reports which are only run periodically (monthly, quarterly or annually). These could be mistakenly dropped because they appear to be unused.
I agree completely that such objects can be dropped ONLY after consulting with the team members who support the application
I have just been studying the DMV sys.dm_db_index_usage_statsused here this week for just this purpose as well as the DMV's for possible missing indexes.
I don't think you can really just use the existance or not of the index within the DMV as it may have been used for a system scan to update the statistics! I was looking to use the columns within the DMV to assess how much the index is used as a candidate for deletion could be used seldomly and infrequently (after ignoring the system accesses).
How would you get all unused indexes in a single table instead of all databases?
Leave a comment