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