July 2, 2021 at 9:44 am
Another day wrestling executionplans and stumbled upon this.. I'm looking at a "key lookup (clustered)" that uses a PK clustered index. I'm confused by the "key lookup" I didn't expected that since it's using a clustered index, I thought all columns "came for free" with a clustered index and didn't need any lookups? Or perhaps I've got it wrong somehow.
I shouldn't have to create another nonclustered index with included columns to get rid of the "Key lookup" or?
July 2, 2021 at 3:10 pm
Well SQL server has decided it's cheaper to use another index to run the query then do the key lookup for whatever other fields it needs. And clustered indexes are not inherently free, when you lookup a record you do get all the columns, but then you get all the columns whether you need them or not.
As for whether it's worth adding the column to the non clustered index that really depends.
July 17, 2021 at 7:26 pm
Key lookups against a Primary Key clustered index happen when an existing non-clustered index doesn't have all the columns needed to "cover" the columns that need to be retrieved to process the results for a query. When the SQL Server engine estimates cost for the query, in your case, it decided that it was cheaper/quicker to get some of the data from an existing non-clustered index and then use the column(s) in the clustered primary key to get the rest of the data needed. This is a very common query pattern. Often the key lookup can be eliminated by reviewing the query plan to see what columns are being retrieved in the key lookup and then adding those columns to the corresponding activity in an existing non-clustered index.
If you want additional help with this, you might consider using this link to get a plan you can share here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply