Tables Without Clustered Indexes
Tables without clustered indexes are called heaps. They’re scattered on disk anywhere that SQL Server can find a spot, and they’re not stored in any order whatsoever. This can make for really fast inserts – SQL Server can just throw the data down – but slow selects, updates, and deletes. This part of our SQL Server sp_Blitz script checks sys.indexes looking for heaps: tables without clustered indexes.
There are cases where heaps perform better than tables with clustered indexes. For example, if you’ve got a staging table where data is inserted and then selected back out (without doing any updates whatsoever) then heaps may be faster. However, unless you’ve tested and proven that a heap is the right answer for your issue, it’s probably not.
When we back up data temporarily using a SELECT INTO, like right before we do a big change in the database, that structure is a heap. We often forget to go back and clean up those objects later.
To Fix the Problem
This isn’t a fast fix: we need to do some research and design. We’ll need to:
- List the heaps (tables with no clustered indexes)
- If they’re not actively being queried (like if the seeks, scans, updates are null), then they might be leftover backup tables. Consider moving them to a database, making it read-only, taking a backup, and then eventually detaching the database.
- If they’re being actively queried, determine the right clustering index. Sometimes there’s already a primary key, but someone just forgot to set it as the clustered index.
This query will help you track ’em down:
SELECT TOP ( 10000 ) o.name, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates --, *
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.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 = N'ib2010'
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 (ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates) > 0
AND sd.name <> 'tempdb'
AND o.is_ms_shipped = 0
AND o.type <> 'S'
ORDER BY (ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates) DESC, o.name;
After the change, you can monitor overall system performance looking for improvements, especially around query execution plans and lower logical reads.