How We can eliminate Key/RID Lookups in the nonclustered index? (ANO18)

#Nonclusteredindex, #lookup, #Index, #sqlserver, #PerformanceTuning

In the previous article, I explained what is key and RID lookups and why we have to eliminate them as long as possible, and in this article I want to explain the possible solutions.

Solution 1: Composite index

A composite index is an index that contains more than one column. these

indexes are useful when your SELECT queries use those columns frequently.

So let's take a look at an example query and the query plan.?

USE AdventureWorks2019
Go
DROP TABLE IF EXISTS ClusteredTable
GO
SELECT * INTO ClusteredTable FROM AdventureWorks2019.Person.Person

--Create a Clustered index
CREATE CLUSTERED INDEX IX_Clustered ON ClusteredTable(BusinessEntityID)
GO
--Create a nonclustered index
CREATE NONCLUSTERED INDEX [IX_LastName] ON ClusteredTable ([LastName])
GO
sp_helpindex ClusteredTable        

Now we have two indexes and I want to show you how the SQL server uses these indexes.

Note: before executing the script below enable Include actual execution plan (or press Ctrl+M)

SELECT
	BusinessEntityID, FirstName, LastName FROM ClusteredTable 
WHERE? LastName = 'Russell'
GO?        

If we look at the execution plan we can see that we have an Index Seek in the nonclustered index to return the "LastName", but we also have a Key Lookup on the clustered index.?The reason for this is that the nonclustered index does not contain the FirstName column, Therefore the SQL uses a lookup operation to get the FirstName column from the clustered index (If our table is Heap, therefore we will have a RID Lookup).?

Notice: Clustered index key (BusinessEntityID) is stored inside the nonclustered index.

Now we want to remove this extra operation by using a composite index:

We can drop the existing index and recreate it or add another index.

DROP INDEX [IX_LastName] ON ClusteredTable
GO
CREATE NONCLUSTERED INDEX [IX_LastName] ON ClusteredTable ([LastName],[FirstName])
GO
--OR
CREATE NONCLUSTERED INDEX [IX_FLName] ON ClusteredTable ([LastName],FirstName)
GO
SELECT
	BusinessEntityID, FirstName, LastName
FROM ClusteredTable WHERE? LastName = 'Russell'
GO?        

As you can see by adding the FirstName in the nonclustered index we could eliminate the key look operation, and this is very valuable in performance tuning.

Solution2 :

Although the above index improves the performance of the query, the downside is the cost of maintaining the index b-tree every time the quantity changes. The additional index maintenance cost is probably not justified when the query is only run once a day but the Quantity is changed continuously. A better option may be an included column as I will explain as a Second solution.

DROP TABLE IF EXISTS IncludeIndexTable
GO
SELECT * INTO IncludeIndexTable FROM AdventureWorks2019.Person.Person
GO
CREATE CLUSTERED INDEX CIX_BusinessEntityID ON IncludeIndexTable(BusinessEntityID)
GO        

Now create a nonclustered index in which the LastName is the index key, and add the FirstName as an include column

CREATE NONCLUSTERED INDEX NCIX_Include
	ON IncludeIndexTable ([LastName])
	INCLUDE([FirstName])??        

Again, As you can see by adding the FirstName in the Include part we could eliminate the key look operation.

Now let's compare the use of the nonclustered index and the clustered index :

while the execution plan is on, execute this query together.

SELECT
	BusinessEntityID, FirstName, LastName
FROM IncludeIndexTable WHERE? LastName = 'Russell'
GO?
--this command forces the SQL to use the clustered index
SELECT
	BusinessEntityID, FirstName, LastName
FROM IncludeIndexTable WITH(INDEX(CIX_BusinessEntityID))
	WHERE? LastName = 'Russell'
GO?        

This image clearly demonstrates the efficiency of the nonclustered index over the clustered index(cost 0% over 100 %)

in the next article am going to give some tips and tricks about indexes.