As part of a review of a system, I look for tables without clustered indexes; however, invariably I will provide the list of tables to a development team and they will say “These are old tables we don’t use anymore”, so I am forced to provide additional statistics about the number of times a heap table is used in the database. Based on a check found in Brent Ozar’s sp_blitz script, I pull usage information about the heap tables. If there is no usage, they are not reported.
[sourcecode language=”sql”]
EXEC dbo.sp_MSforeachdb ‘USE [?];
SELECT DB_NAME(), t.name as TableName, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_scan
FROM [?].sys.indexes i
INNER JOIN [?].sys.objects o ON i.object_id = o.object_id
INNER JOIN [?].sys.tables t ON o.object_id = t.object_id
INNER JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.databases sd ON sd.name = ”?”
LEFT OUTER JOIN [?].sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id
WHERE i.type_desc = ”HEAP” AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NOT NULL
AND sd.name <> ”tempdb” AND o.is_ms_shipped = 0 AND o.type <> ”S”’;
[/sourcecode]
Happy Hunting
Leave a Reply
You must belogged in to post a comment.