Finding heap tables in your SQL Server database

Category: Performance
Item: Active tables without clustered IX

What is a Heap in SQL Server table?

A table without a clustered index is called Heap.

It consists only of data pages.

Note: You can have one or more nonclustered indexes this type of table.

Why should you care about them?

On tables without clustered and nonclustered indexes, SQL Server will do a table scan to find any row, degrading the query performance.

Neither the data pages nor the physical placement of the pages are guaranteed to be in any particular order.

All tables should have a clustered index, with rare exceptions (one reason could be a table that only has INSERTs, like log files).

How can you list all tables without a clustered index?

A heap is always identified by index_id = 0 in the catalog tables.

You can use the following query to list heap tables from one database.

  1. SELECT + '.' + AS TableName
  2. FROM sys.tables AS TBL 
  3. INNER JOIN sys.schemas AS SCH ON TBL.schema_id = SCH.schema_id 
  4. INNER JOIN sys.indexes AS IDX ON TBL.object_id = IDX.object_id AND IDX.type = 0 
  5. ORDER BY TableName
Figure 1 – Query output (Heap tables).

How can you fix them?

  1. For active tables, create the right clustering index.  (Sometimes, there is a primary key, and someone just forgot to set it as the clustered index).
  2. Start with tables that are used the most.

More information

Microsoft – Heaps (Tables without Clustered Indexes).

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published. Required fields are marked *