October 26, 2017 at 4:36 pm
Hi everyone,
There is a ton of information about key lookup vs rid lookup, but it seems I can't find the answer to my questions:
Does SQL need to read every row in the table when it does a Key Lookup ? (I am unsure)
Does SQL need to read every row in the table when it does a Rid Lookup ? (I think yes)
There reason I am asking is because I want to know if the process (key lookup & rid lookup) would be blocked by a X/IX locks.
Thank you
October 26, 2017 at 5:21 pm
Key lookup and RID lookup are the same thing, they're single-row reads of either the clustered index (key) or heap (RID)
They certainly would be blocked by X locks, as they need a shared lock on the appropriate resource. Whether they're blocked by an IX depends on what granularity SQL's chosen to taken the shared locks at.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2017 at 9:11 am
Thank you for your answer.
Assuming that I have a Clustered Index, is it possible to do a Clustered Index Seek instead of Key Lookup ? Does it make sense ?
Thank you
October 27, 2017 at 9:22 am
That is going to be determined by SQL Server based on the query. If SQL Server determines that Key lookup is faster to satisfy the query than a clustered index seek, that is what it will do.
October 27, 2017 at 9:56 am
Thank you,
What I do no not understand if that when I Google about how to get rid of Key Lookups, most of the solutions are Covering Indexes. I understand the concept of Covering indexing, but it is not always possible. How about tweaking the query/index in order to get a Clustered Index Seek ?
October 27, 2017 at 10:00 am
Gamleur84 - Friday, October 27, 2017 9:56 AMThank you,What I do no not understand if that when I Google about how to get rid of Key Lookups, most of the solutions are Covering Indexes. I understand the concept of Covering indexing, but it is not always possible. How about tweaking the query/index in order to get a Clustered Index Seek ?
Okay, what is the problem you are trying to solve? Anything you get right now is going to be extremely high level and more than likely simply shots in the dark.
October 27, 2017 at 10:10 am
Gamleur84 - Friday, October 27, 2017 9:56 AMHow about tweaking the query/index in order to get a Clustered Index Seek ?
Remove columns from the query that aren't part of the index being used.
See why it's not a solution offered?
Assuming that I have a Clustered Index, is it possible to do a Clustered Index Seek instead of Key Lookup ? Does it make sense ?
No, that does not make sense. If SQL could do a clustered index seek, it probably would already be doing so.
To get a clustered index seek, you need a predicate that filters on the clustered index key.
Oh, and see https://www.sqlinthewild.co.za/index.php/2016/02/02/on-the-addition-of-useless-where-clauses/
p.s. Key lookups aren't bad.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2017 at 10:27 am
Hi,
I can tell you where I come from. Basically, I found a slow running query and I saw a RID Lookup in the execution plan. I know that a RID lookup means there is no Clustered Index on the table. I was worried and I checked the entire database and found about 20 tables with million of records without a Clustered Index. I want to tell the business that they need Clustered Indexes, but I will need to justify my claim with technical knowledge.
Here is my thinking/justification:
Initially, I thought a KEY Lookup would be much faster than a RID Lookup, which is not true. Then I thought that a Key Lookup would not be blocked by a X lock, which is not true. When I Google how to get rid of lookups, most of the solutions are Covering Indexes. Good, but when you have a covering index, you do not need my Clustered Index. Then I thought, well at least SQL could potentially do a Clustered Index Scan or Clustered Index Seek instead of a Key Lookup.
That is where I am now.
October 27, 2017 at 10:36 am
The biggest reason for having a clustered index is page management. SQL Server doesn't (this may have changed in newer versions) manage space well when comes to heap tables.
Depending on the size of the data rows, and the data needed to resolve queries, covering indexes are a good thing. I wouldn't create a covering index that replicated all the data in a table, and yes I have seen this done. Creating many indexes is also not really a good thing as the more indexes you create the more work SQL Server has to do during INSERTs, UPDATEs, and DELETEs.
Not having a clustered index, however, isn't necessarily a bad thing either. It really depends on the usage of the table(s) and the data stored.
Again, without specifics, you are not going to get specific answers. The one big thing to remember "It Depends" is a correct answer in many cases.
October 27, 2017 at 3:24 pm
Gamleur84 - Friday, October 27, 2017 10:27 AMThen I thought, well at least SQL could potentially do a Clustered Index Scan or Clustered Index Seek instead of a Key Lookup.
It can, but a clustered index scan is a table scan, a full table scan. You don't generally want those.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply