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).


Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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




FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.