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.
SELECT SCH.name + '.' + TBL.name AS TableName
FROM sys.tables AS TBL
INNER JOIN sys.schemas AS SCH ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS IDX ON TBL.object_id = IDX.object_id AND IDX.type = 0
ORDER BY TableName
How can you fix them?
- 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).
- Start with tables that are used the most.
