Blitz Result: 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.

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

Return to sp_Blitz or Ask Us Questions

css.php