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.

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.

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).

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

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.

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

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.

Now let us check the execution plan for the same.

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.

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 (http://blog.SQLAuthority.com)

29 thoughts on “SQL SERVER – Removing Key Lookup – Seek Predicate – Predicate – An Interesting Observation Related to Datatypes

  1. I found your document very useful.
    After spending hours for performance tuning related material on net I found your article the best one. Please share me more info on EXECUTION PLAN and PERFORMANCE TUNING.
    Thanks again
    AMit :)

    Like

  2. In your example, you select three columns, so it makes sense to create a NONCLUSTERED INDEX. If we select 30 columns, does it still make sense to create a NONCLUSTERED INDEX? Thanks.

    Like

  3. If my query is: SELECT * FROM tablename WHERE field1=value and field1 already is a nonclustered index, after executing, and look at actual execution plan, i saw Index Seek on field1 and Key Lookup on primary key, how i gonna remove this Key Lookup since i cant include all field as covering index?

    Like

  4. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31 Journey to SQLAuthority

  5. If you turn on IO statistics and the query is doing a convert from varchar to say nvarchar. One can see the impact in the elasped time, the hit is not in the query plan. It is overhead in converting the data to a comparable data type.
    If there are a large number of rows this is a very unwanted overhead and it will take your query longer to complete.

    Like

  6. I may be wrong here, but the way I understand this is when comparing different Data Types, the difference between a “Seek Predicate” and a “Predicate” is based on the Data Type Precedence. The Data Type with the lower precedence is the one that is always converted. So, if the lower precedence Data Type is on the left side (table side), every row gets converted (Full Index Scan). If the lower precedence is on the right side (expression side), only the expression value gets converetd (Index Seek).

    See: http://msdn.microsoft.com/en-us/library/ms190309.aspx for precedence order.

    Like

  7. Hi Sir,

    I cant get the answer for the following question from my senior colleagues so i am escalting to you.I dont know if it belong to this post or not.but after reading this i came across this problem

    Question is something like my isolation level is set to read committed.Which is default for SQL SERVER.Now i am doing a explicit transaction(Where When i will execute commit after that only my data will get committed).In this situation.

    I have a table of 100 rows.I am executing one update on table which will affect 10 rows.This update wont get committ.

    First doubt : Is it after each single row will be updated and then that row related index will be updated?
    or After updating 10 rows Index will be updated for all 10 rows.??

    Second doubt : After committ only my belonging index will be updated or before committ and how??

    Please if you want i can try to explain further my problem.

    Thanks
    Kamesh

    Like

  8. Alter Way of Leftouter join method?

    Can anyone give me the method of alter way of left outer join Because when we create a indexed view we could not able create index on theat view due to statement contains FULL JOIN or LEFT JOIN or RIGHT JOIN. So i searched the web fully it gives suggestion using UNION Instead of left join,I mentioned query below. So could you give me any other method is available to replace LEFT JOIN with that other than UNION? Is it full to reduce optimization cost when we use UNION instead of LEFT JOIN? Quick suggestions are highly appreciated………….buddys………

    ———————-My original table using left join method—————–

    SELECT tablea.*,
    CASE
    WHEN tableb.tablebcolumn1 IS NULL THEN 0
    ELSE 1
    END IsHosted
    FROM tablea
    LEFT JOIN tableb ON tablea.column=tableb.column

    ———–Example of alter way of using UNIONALL instead of leftouterjoin ———

    SELECT tablea.,
    CASE
    WHEN tableb.tablebcolumn1 IS NULL THEN 0
    ELSE 1
    END IsHosted
    FROM tablea where tablea.column IN (select tableb.column from tableb) UNION ALL SELECT tablea.,
    CASE
    WHEN tableb.tablebcolumn1 IS NULL THEN 0
    ELSE 1
    END IsHosted
    FROM tablea where tablea.column NOT IN (select tableb.column from tableb)

    ————————————————————————————– Note: Due to security reason i did not give original table name. i gave table name of tablea and tableb………as my originaltable name

    Like

  9. 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’.

    Like

  10. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  12. 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.

    Like

  13. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s