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.

To track ’em down, run:

That’s the “inventory” output for sp_BlitzIndex. Look for rows where the “Definition” = Heap, especially for tables that you care about. I sure wish SSMS would let us click on columns and sort the output, but until then, the easiest way to manipulate this data is probably to copy/paste it into Excel, format it as a table, and slice & dice & filter the output.

After the change, you can monitor overall system performance looking for improvements, especially around query execution plans and lower logical reads.