SQL SERVER – Heaps, Scans and RID Lookup

Today we will discuss a question which I was recently asked by my client during the Comprehensive Database Performance Health Check. The question was do heaps are very bad and always force a table scan? The answer is NOT Always, whenever there is a case of RID Lookup, Heaps also do a single row lookup or limited row lookup instead of the table scan.

It is a wide belief that when the SQL Server table does not have a clustered index, the table is never going to give a performance. The truth is in most cases, a clustered index is recommended. However, with that said, it is not necessary that in every single case when there is no clustered index (heap) is going to perform poorly.

Let us see some definitions before continuing this blog post.

Heap: A heap is a special Tree-based data structure in which the tree is a complete binary tree. In simple words, a table without a clustered index is called Heap in SQL Server

Table Scans: A table scan is when SQL Server has to scan the data to find the appropriate records. A scan is the opposite of a seek (not necessarily it is a bad thing).

RID Lookup: A RID Lookup is a lookup into a heap table using a Row ID. Every non-clustered index includes a ROW ID in order to find the rest of a table’s data in the heap table.

A common belief is that as there is no clustered index heap is a very organized pile of data and it does perform well when we try to look up a specific row based on a column which is not indexed.

Honestly, the common belief is not true. A heap is not necessarily mean a complete table scan we try to retrieve the data. Let us see a simple example that is based on the sample database AdventureWorks.

Let us run the following query and also enable the execution plan when you run the query.

SELECT [DatabaseLogID]
,[PostTime]
,[DatabaseUser]
,[Event]
,[Schema]
,[Object]
,[TSQL]
FROM [AdventureWorks2017].[dbo].[DatabaseLog]
WHERE DatabaseLogID = 2

Now let us see the example plan.

You can see from the execution plan, when SQL Server Engine is not able to find the required data in the non-clustered index it goes to HEAP and instead of doing complete table scan just reads 1 row as it is able to map that one row based on the non-clustered index which is already existing on the table.

So there you go, HEAP does not mean table scan every single time. Let us connect on twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)