It often happens that DBA’s/Developers end up creating database objects(mostly tables) during development phase for various reasons like intermediate testing or backup. Such objects often end up on production databases.
DBA’s/Developers often repeat same things on production databases as well
Probable Reasons
- Backup before performing quick fix
- Performance optimization
It is always a good practice to delete such objects created for temporary purposes. However, most of the times DBA’s/Developers forget to drop them.
Drawbacks
- Unorganized database
- Unnecessary space claim
How if there was a way to identify tables/indexes that are not used? Well, not to worry there is a way to find out tables and indexes that are not used.
Here is the script (using onf of the DMV’s) to find out the tables/indexes that are not used by any SQL process.
Such objects can be dropped after consulting with team members supporting the particular application.
P:S – The statistics are reset with SQL restart.
SELECT
OBJECT_NAME(i.[object_id]) AS [Table name] ,
CASE WHEN i.name IS NULL THEN '<Unused table>' ELSE i.name END AS [Index name]
FROM
sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE
i.index_id NOT IN (
SELECT s.index_id
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;
Regards
Ritesh Medhe
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
I believe this query wouldn’t be able to tell if your heaps are used or not, since they wouldn’t have a clustered index in the sys.indexes table. I think that was the piece I was missing when I went searching for the same thing. Can you verify it reports correctly on tables without a clustered index?
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 under column index name no matter whether the table is heap/have CI or NCI
Thanks Kyle
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).
Good reading; the post and comments
Amit,
How would you get all unused indexes in a single table instead of all databases?