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:

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.

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.

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.

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.

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

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?

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.

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

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??

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

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.

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

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.