February 27, 2011 at 8:02 pm
Ok, the attached image is a snippet of an exec plan.
I presume I am missing something simple here.
It all begins with an Index Seek and a Key Lookup. The lookup is required because one of the outputs from the Index Seek is not contained within the index, so a Key Lookup is required to go back to the CI and retrieve that data. This is all brought together in the Nested Loop.
Then, after the filter, we get to the highlighted Index Seek. As with the first one, this Index Seek has an output value that is not included in the index being used. However, I see no corresponding lookup. Should there not be another Key Lookup? How is it getting that value if it is not in the index without going back to the clustered index?
Now there is another NC index on that table that contains all three outputs, but that is not the index it says it is using. If this is some sort of index intersection, I would expect to see another operator for the seek from the other index.
What am I missing here?
February 27, 2011 at 8:21 pm
So here is a followup....
Later on in the plan, there is a CI Seek on the same table. Contained within the CI and an output of the CI seek is the field that was not contained in the previous non clustered index seek.
So it would appear to me that it is deferring having to lookup up the value from the non clustered index seek cause it can eventually match it all up further down the line.
The output from all of these tables are being chained together through a series of nested loops. It definitely appears to be "deferring" the output from the NC seek cause it will be able to join that up later once we get the output from the CI seek.
Does that make sense? Does that actually happen or am I making this up? 🙂
February 28, 2011 at 1:25 pm
Not seeing the full plan, I can't guarantee my answer, but, if the operator accessing the cluster says that it's a key lookup operation, deferred or not, that's what it is. If it's not a key lookup operation, then the optimizer just chose to access the table twice to retrieve the data. Why precisely? Hard to know, ever, but I'm just guessing without seeing the plan. It could be that it's using the index for a count or an order by instead of getting data from it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply