I'm starting a new blog category to talk about some of weird and confusing stuff I see while query tuning.
First up is the case of the unexpected Key Lookup (Clustered) in a query that looks like it should be covered. This is a follow on from the post Missing index DMVs bug that could cost your sanity…
Today I was tracking down some performance issues on a client site and came across something in a query plan that confused me for a bit. All code and screenshots below are from my simplified repro on my test machine at home.
The code is using a cursor to drive a process, and the SELECT statement driving the cursor is covered by a non-clustered index.
However, the query plan for the cursor-driving statement is as below:
You can see the SELECT statement above, and you can see that the query plan is using the nonclustered index on c2 and c3 to satisfy the SELECT. So where is the Key Lookup coming from? If I hover over the Key Lookup operator, I see the details of the operator in a tooltip:
According to the details, the output list of the Key Lookup is Chk1002. What's Chk1002? It's not a column in my table, and it's not a check constraint on the table either – I don't have any. And why is it looking up the cluster key *anyway*?
This is where I turned to Kimberly to help figure out what's going on, who said it's to do with the cursor.
I then proceeded to work thigns out and got it a little twisted up. Check out the comments discussion with Brad who explained things, and it's in Books Online too.
Because the cursor is a dynamic optimistic cursor (by default as I didn't specify anything else) SQL Server persists a checksum of all rows picked up by the cursor-driving query in a worktable in tempdb (which, if this is a heavily executed query, can cause perf issues with tempdb). When I say FETCH NEXT, it goes back to the table to get the next value, but if I want to update a column in the row I'm working on, it recalculates the checksum to make sure that nothing has changed in that row outside the confines of my cursor. If so, my update fails – if not, it allows it.
There are plenty of ways to improve the situation (e.g. removing the cursor altogether and using a nice set-based operation… or changing the cursro type) but that's not the point of this post. I just wanted you to be aware of this, and how the query operator properties ins't explicit in working out what's going on.