How to avoid KEYLOOKUP operator

The Key Lookup Operator is a bookmark look up on a table with a clustered index. This Key Lookup can be quite expensive, so we should try to eliminate them when you can. Of course we should also need to consider your overall work load, and how often the query with the key lookup is executed.

Key Lookup occurs when you have an index seek against a table ,But your query requires  additional columns that are not in that index. This causes SQL Server to have to go back and retrieve those extra columns you can see the example here.

 

One way to reduce or eliminate Key Lookup is to remove some or all of the columns that are causing  the Key Lookup from the query. This can easily break your application. So don’t do this until you are sure that these columns are necessary.

And the second method is creating covering index  on a column of the table in SELECT list. A covering index is simply a non-clustered index  that has all the columns needed to either satisfy the entire query in our case. So it is always better  to get a list of columns in the SELECT query which are required only.The below example will tell you how creating covering index will helpful to you to process the query

 

 

Caution: You need to consider what kind of work load you have,and how volatile this particular table is. You also need to look at your existing indexes,and how they are being used. You want to avoid the common mistakes of adding indexes that helps a query that is not executed very often

One way to reduce the maintenance cost of “covering index” is to add many of the columns to the index as included columns. This means that the columns are only included in the leaf level of the index and not in the B-tree of index.