Key Lookup & Residual Predicate

Performance Tunining

USE [AdventureWorks2012];

GO

SELECT NationalIDNumber, HireDate, MaritalStatus

FROM HumanResources.Employee

WHERE NationalIDNumber = N'14417807';

Below result produced . 

From the below screenshot , Output List has the columns Employee,MaritalStatus,HireDate . Non Clustered index don’t have the other columns or predicate columns don’t exist on them.

Keylookup , for each output , it has to lookup on clusterindex based on clustered index key (i.e in this case BusinessEntityID) . 

We can resolve this situation on altering the existing or create a new index .

/****** Object:  Index [AK_Employee_NationalIDNumber]    Script Date: 07/11/2019 13:14:08 ******/

CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_NationalIDNumber] ON [HumanResources].[Employee]

(

       NationalIDNumber,HireDate,MaritalStatus ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,

DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

After the index creation, Lets inspect the query plan.

No key lookup .

What if we change the query as per below

SELECT NationalIDNumber, HireDate, MaritalStatus

FROM HumanResources.Employee

WHERE NationalIDNumber > 100

We have found residual predicate by simply giving the value of 100, as it has to fetch it from the storage engine instead of memory and also 100 is a integer value as the nationalidnumber is nvarchar . SQL decided to use scan instead of seek and select operator spills out due to convert implicit int expression used in the where clause predicate .

Let’s force the residual predicate to force in to query processor to do the filter.

We have resolved the residucal predicate but introduced filter and computer scalar along with index scan .

To resolve this issue.

SELECT NationalIDNumber, HireDate, MaritalStatus

FROM HumanResources.Employee

WHERE NationalIDNumber > '100'