SQL SERVER – Removing Key Lookup – Seek Predicate – Predicate – An Interesting Observation Related to Datatypes

Recently, I have been working on Query Optimization project. While working on it, I found the following interesting observation. This entire concept may appear very simple, but if you are working in the area of query optimization and server tuning, you will find such useful hints.

Before we start, let us understand the difference between Seek Predicate and Predicate. Seek Predicate is the operation that describes the b-tree portion of the Seek. Predicate is the operation that describes the additional filter using non-key columns. Based on the description, it is very clear that Seek Predicate is better than Predicate as it searches indexes whereas in Predicate, the search is on non-key columns – which implies that the search is on the data in page files itself.

Earlier, I had posted regarding how to remove key lookup and bookmark lookup in the following:

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3

Let us see an example where we will remove the bookmark lookup first using the covering index. On removing the bookmark lookup, it resulted in Index Scan, which is not good for performance. When Index Scan is converted to Index Seek, it provides  a significant improvement in performance.

Run following SELECT, which is based on the database AdventureWorks.

USE AdventureWorks
GO
-- CTRL + M Enforces Key Lookup
-- Try 1
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
GO

Let us check the execution plan for the same. The execution plan consists of the key lookup because there are columns that we are trying to retrieve in SELECT as well in WHERE clause.

SQL SERVER - Removing Key Lookup - Seek Predicate - Predicate - An Interesting Observation Related to Datatypes klseek1

Solarwinds

Let us create a covering index on this table HumanResources.Employee.

-- Create Non clustered Index
CREATE NONCLUSTERED INDEX [IX_HumanResources_Employee_Example] ON HumanResources.Employee
(
NationalIDNumber ASC, HireDate, MaritalStatus
) ON [PRIMARY] GO

After creating above index, let us run the same SELECT statement again.

-- CTRL + M Removes Key Lookup, but it still enforces Index Scan
-- Try 2
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
GO

Due to non-clustered index, Key Lookup is removed along with Nested Loops but Index Scan still remains, and this is not good for performance.

SQL SERVER - Removing Key Lookup - Seek Predicate - Predicate - An Interesting Observation Related to Datatypes klseek2

I tried to remove the scan, but my attempts were unsuccessful. I finally looked at the datatype of the NationalIDNumber. All this time, I was assuming that this datatype is INT, but on a careful check, I found that the datatype of NationalIDNumber is nvarchar(15).

SQL SERVER - Removing Key Lookup - Seek Predicate - Predicate - An Interesting Observation Related to Datatypes klseekdt

In the SELECT statement, we were comparing the datatype of NVARCHAR to INT, and this was forcing the predicate operation while executing the query.

SQL SERVER - Removing Key Lookup - Seek Predicate - Predicate - An Interesting Observation Related to Datatypes klseekpre

As discussed earlier, due to predicate operation, there has to be explicit conversion on the side of NationalIDNumber, which forces the query optimizer to not use the index and instead it has to scan the complete data in table to get the necessary data. This is not the desired solution. Index Scan reduces performance. The reason for this conversion is because I am using INT value in WHERE clause instead of NVARCHAR.

I changed my WHERE clause and passed STRING as the parameter instead of INT.

-- CTRL + M Removes Key Lookup and it enforces Index Seek
-- Try 3
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = '14417807'
GO

After running the query with the changed WHERE clause, the Index Scan is now converted into Index Seek.

SQL SERVER - Removing Key Lookup - Seek Predicate - Predicate - An Interesting Observation Related to Datatypes klseek3

Index Seek is definitely the most optimal solution in this particular scenario.

SQL SERVER - Removing Key Lookup - Seek Predicate - Predicate - An Interesting Observation Related to Datatypes klseekpre1

When the detail execution plan was checked, I found the following two notable points. First, the predicate is converted to seek predicate, which is the reason for performance improvement, as described earlier. Instead of scanning data in the table, Index Seek is performed. Second, as the datatype of the NationalIDNumber is NVARCHAR and the parameters are passed as VARCHAR, the conversion happens on the parameters instead of NationalIDNumber column, and this forces Index Scan to Index Seek.

If we pass the parameter as NVARCHAR instead of VARCHAR, the execution plan will remain the same, but CONVERT_IMPLICIT will not be required any more. Let us run the following query, which has NVARCHAR as the parameter.

-- CTRL + M Removes Key Lookup and it enforces Index Seek and no CONVERT_IMPLICIT
-- Try 4
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = N'14417807'
GO

The execution plan of the above query is very similar to that in which we had passed the parameter as VARCHAR.

SQL SERVER - Removing Key Lookup - Seek Predicate - Predicate - An Interesting Observation Related to Datatypes klseek3

Now let us check the execution plan for the same.

SQL SERVER - Removing Key Lookup - Seek Predicate - Predicate - An Interesting Observation Related to Datatypes klseekpre2

In the WHERE condition, the operators we have on both the sides of “=” are of NVARCHAR. NationalIDNumber and parameter passed – both are NVARCHAR, which has removed CONVERT_IMPLICIT. However, there are no changes in the execution plan.

In summary, when Key Lookup is removed and index seek replaces index scan, the performance is tuned up. Let us quickly compare the execution plan of the above four options. I have included the complete code here for easy reference.

USE AdventureWorks
GO
-- CTRL + M Enforces Key Lookup
-- Try 1
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
GO
-- Create Non clustered Index
CREATE NONCLUSTERED INDEX [IX_HumanResources_Employee_Example] ON HumanResources.Employee
(
NationalIDNumber ASC, HireDate, MaritalStatus
) ON [PRIMARY] GO
--WAITFOR DELAY '00:00:30'
-- CTRL + M Removes Key Lookup, but it still enforces Index Scan
-- Try 2
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
GO
-- CTRL + M Removes Key Lookup and it enforces Index Seek
-- Try 3
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = '14417807'
GO
-- CTRL + M Removes Key Lookup and it enforces Index Seek and no CONVERT_IMPLICIT
-- Try 4
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = N'14417807'
GO
/* What is the reason for difference between Try 2 and Try 3?
Check the exeuction plan
*/
-- Drop Index
DROP INDEX [IX_HumanResources_Employee_Example] ON HumanResources.Employee WITH ( ONLINE = OFF )
GO

Let us look at the execution plan.

SQL SERVER - Removing Key Lookup - Seek Predicate - Predicate - An Interesting Observation Related to Datatypes klseek

Summary points:

  • In general, Index Seek is better than Index Scan (I am not talking about it depends conditions)
  • Understand Predicates and Seek Predicates and see if you have only Seek Predicates.
  • In case of key lookup or bookmark lookup, see if you can create a covering index or included column index.
  • Use the datatype wisely even though there is no change in the resultset.

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

Solarwinds
,
Previous Post
SQL SERVER – Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time
Next Post
SQL SERVER – Sharepoint Resource Available for SQL Server

Related Posts

35 Comments. Leave new

  • Hi ,

    This might be a very simple question but however just wanted to ask as I’m kind off stuck at a point. I’m trying to create a filtered index on a column, but I’m not able to because I’m getting a syntax error. I checked if this is permission issue, but that’s not the case, because I’m able to create and alter indexes and also use other option in the create index syntax.

    CREATE NONCLUSTERED INDEX “IX_test_NC” ON
    dbo.Table_name (column_name)
    WHERE substring(column_name,1,3) = ‘value’

    The error is :

    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword ‘WHERE’.

    Reply
  • Posted on the incorrect thread

    Reply
  • Paresh RathodParesh Rathod
    March 8, 2013 11:04 am

    Hi Pinal,

    Thanks a lot for detail explanation.

    I have sql query with where clause.

    Where IsActive = 1

    IsActive is bit field in table.

    It showing me Predicate with Convert_Implicit in query execution plan for above column.

    Now I convert where clause like

    Where IsActive = CAST(1 AS BIT)

    Even it showing me Predicate like IsActive=(1)

    Plan also showing Clustered Index Seek in above both cases

    Is there any performance problem in above both cases, please elaborate more on this.

    Reply
  • Hi,
    Can you just let me know if we create these indexes on the tables then how much does it affect the insert,update and delete operation on the tables?
    And yes if a table has 30 columns and the query is selecting all 30 columns and using two columns in WHERE clause then am i supposed to make a covering index on all the 30 columns? or is there any other way out to avoid index scan and RID lookup

    Reply
  • Thanks …. Simply great explanation

    Reply
  • Sampath Kumar Asealu
    January 6, 2015 6:47 pm

    good one

    Reply
  • Aaditya Chaubey
    January 14, 2015 1:57 pm

    Very nice article…

    Reply
  • Hi Pinal Dave, how about the Predicate vs Seek Predicate, with respect to just Index Seek. what I mean is, I have two queries, with slight difference in the filter. One is resulting in a Index Seek with Seek Predicates covering all the filers, while the slower query is resulting in Index Seek but some of the filters are in Predicate and other are in Seek Predicate; the filter that is different is in the Predicate. any thoughts on this?

    Reply
  • ANIL KUMAR DUBEY
    October 19, 2015 5:34 pm

    I have one simple query “select * from table_name”. I have created 2 indexes one clustered on ID column and one non clustered index which include all the columns of table. Still when i run query i get clustered index scan in execution plan. How can i remove index scan and get index seek.

    Reply
  • Thanks. Very informative

    Reply
  • Good one. Thanks Pinal.

    Reply

Leave a Reply

Menu