Successful Anti-Patterns, Key Lookup VS RID Lookup

This post wants to show some performance improvements we may have by doing things the way we’ve been told not to When it comes to technical subjects like databases, we must always look for proven solutions and best practices, which usually take us from A to B in the most efficient way. Usually.

But what happens if one of those approaches considered best practices is not as efficient as the one supposed to be the baddie?
 
DISCLAIMER: This is a demonstration which shows a fact or series of facts, not pretending to set or contradict any best practice.
 
With that said, let’s start with the topic in title.

In SQL Server, there are a number of reasons why you want to have most of (if not all) your tables created as clustered indexes and not heaps. There are also lots of information about how we should carefully choose on which column[s] from our tables we would create them as this choice will help our performance in general. My favorite expert for this topic is Kimberly Tripp (b|t), so I strongly recommend reading this, this and this. Just checking the dates on the posts, you can picture how long this has been around.

But sometimes we can find some behaviours that contradicts all we’ve learned and get unexpected benefits from doing things “wrong”.

Let’s set up a small demo where to see this in action, starting by restoring a copy of [AdventureWorksXXXX] on our server, in my case the 2014 version of it

--===============================================================================================
-- Set up
--===============================================================================================

USE master
GO
IF DB_ID('AdventureWorks2014') IS NOT NULL BEGIN
	ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE AdventureWorks2014
END

RESTORE FILELISTONLY FROM DISK = '.\AdventureWorks2014.bak'

RESTORE DATABASE AdventureWorks2014
FROM DISK = '.\AdventureWorks2014.bak'
WITH RECOVERY
, MOVE 'AdventureWorks2014_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\AdventureWorks2014_Data.mdf'
, MOVE 'AdventureWorks2014_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\AdventureWorks2014_Log.ldf'
GO

 
And then we create a HEAP table by simply SELECTing one INTO another

USE AdventureWorks2014
GO 
 
SELECT * 
INTO Person.Address_HEAP
FROM Person.Address

 
The first [unexpected] benefit is that the HEAP is smaller than the Clustered Index.
Since there is no B-Tree to navigate to our records, those sort of pages are not required in a HEAP, so the result is a slightly smaller table, not enough to say it’s and advantage but there it is.

 
So, to the point, let’s create a nonclustered index on our HEAP to match an existing one on the clustered table.

USE AdventureWorks2014
GO 

CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID] ON [Person].[Address_HEAP]
(
	[StateProvinceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

 
Now we need a bit of imagination but you’ll see it makes sense (hopefully)

We have our 2 tables with a non clustered index on the column [StateProvinceID], so the query optimizer is likely to choose the combination of Non Clustered Index Seek + Lookup if we query the table for a specific value that do not exceed the tipping point.

In this case I will force the optimizer to choose that plan like we are in a SP that suffers from parameter sniffing, so take note because that is a common behaviour.

USE AdventureWorks2014
GO 

SET STATISTICS IO, TIME ON

DECLARE @StateProvinceID INT = 9

SELECT * FROM Person.Address
WHERE StateProvinceID = @StateProvinceID
OPTION (OPTIMIZE FOR (@StateProvinceID = 1))

GO

DECLARE @StateProvinceID INT = 9

SELECT * FROM Person.Address_HEAP
WHERE StateProvinceID = @StateProvinceID
OPTION (OPTIMIZE FOR (@StateProvinceID = 1))

 
As expected, on both queries the plan is identical with the exception of the Key Lookup versus the RID Lookup

 
Both plans are the same but let’s get a better look to STATISTICS IO to see what was going on under the hood.

/*
(4564 row(s) affected)
Table 'Address'. Scan count 1, logical reads 9139, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 176 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(4564 row(s) affected)
Table 'Address_HEAP'. Scan count 1, logical reads 4577, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 95 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

*/

 
You can see we need almost double the reads (and time) to finish with the lookups in the clustered index versus the HEAP.

The explanation is that for every lookup, you must locate the row, and that is done in different ways
– If there is a clustered index, the non clustered index references a row by the clustering key, so we must check the upper levels of the B-tree to locate the row at the leaf levels.
– However in the HEAP, rows are uniquely identified by a RID which points exactly to a specific file, page and slot in our database, and that’s the only needed by the storage engine to go fetch that row, so the ratio of reads per fetched row looks more like 1:1 as opposed to the 2:1 in the clustered index in this example.
 
Conclusion
 
Most of the time following best practices will result in a more robust a fast performing database, but as always there are some behaviors that might contradict them.

The takeaway of this post is that you do not have to assume best practices are always right for each and every case, it is more important to learn how things work and have the knowledge to decide when those best practices really apply to your environment or workload, because every question may be answered with a big “It Depends”.

As I said in the disclaimer, I’m not saying now you should go drop all your clustered indexes because of this example. There are certainly more benefits doing things right and I only pretend you feel more curious about how SQL Server works.

If you have experience with other anti-patterns which can have a use case, please feel free to share them and thanks for reading.