I’m generally a convert to the congragation that all tables should have clustered indexes. It makes sense, helps performance, and in most cases is the best starting point for a tables. If you aren’t part of the community of the faithful on clustered indexes, please read Kimberly Trip’s blog on clustered indexes. There are other articles here and there worth reading, but that’s the one I usually start people on that aren’t convinced.
Anyways, I’ve been working on a database lately that is quite lacking in clustered indexes. Some of the most active tables in the database are lacking clustered indexes and in a few cases indexes at all. I wanted to take a look at all of the tables with heaps and some of the statistics on the heaps and put together the following query:
SELECT object_name(i.object_id )
,p.rows
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
FROM sys.indexes i
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id =ius.index_id
WHERE type_desc = 'HEAP'
ORDER BY rows desc
I liked pulling in the index usage stats with the indexes to help identify whether the indexes were being used sufficiently enough to make the effort worthwhile. And the rowcount helped identify the value of building the indexes as well.
If someone finds this useful and adds a tweak or two, let me know. I really dig some of the interesting things that DMVs can uncover in the database.
Strate SQL 