3

Sorry folks if this sounds obvious. But does index seek always work on index pages not data pages? If so, which I think it is, it can only be used on its own if the output list are the same as index fields/covering . As a result, if a non-index field is needed for the output , it is neither not used by a query engine at all or used with Key/RId lookup which does not have a good performance. So I can conclude that index seek is only beneficial if the data is already covered by the index. is that correct?

migrated from stackoverflow.com Jun 11 '15 at 7:56

This question came from our site for professional and enthusiast programmers.

  • 2
    "Key/RId lookup which does not have a good performance" - so, the system uses a nice, narrow index to isolate a single row and then performs a lookup for that row. You think that's going to have worse performance than scanning the entire wide table? – Damien_The_Unbeliever Jun 11 '15 at 6:51
  • @Damien_The_Unbeliever, No I don't think so. I just read that it would be better be avoided by using cover index. but it is not always possible. is it? So now my question is : is the process of fetching data rows after seeking the index key value which is accompanied by nested join is key lookup? sorry this might be obvious as well. but I need to get a firm understanding on these basics. – Jami Jun 11 '15 at 6:58
8

You are correct in your description but the conclusion is not correct. It may still be faster to use the index anyway even if it has to go off to the table for the rest of it. The data pages contain many fewer rows than the index so there's lots of extra I/O when scanning. Generally the index will tend to give you benefits if it selects few rows even if it's not a great fit.

  • Thanks, so Key Lookup is not bad as it might be needed in some scenarios. is the process of fetching data rows after seeking the index key value which is accompanied by nested join is called key lookup? sorry this might be obvious as well. but I need to get a firm understanding on these basics. – Jami Jun 11 '15 at 7:06
  • Also , I expect the key lookup to be used by query engine in many scenarios. is this correct? Well, I am asking this because apparently key lookup is notorious as I have searched the internet for it. – Jami Jun 11 '15 at 7:11
  • 2
    It's not really notorious, it just introduces a bit of extra I/O. It's the only way to do it unless you build an entire index + covering columns for every query you're going to ever do. You look at the index, then you go to the data pages. That's a typical compromise. Using an index to entirely answer your query is in fact atypical. – LoztInSpace Jun 11 '15 at 7:19
  • Thanks, I have marked it as answer. So a key lookup on clustered index, how is it different from a clustered index seek? – Jami Jun 11 '15 at 7:23
  • 1
    Searching the internet will also most likely make you think that for very similar reasons you should always have a PK that's an IDENTITY column and must be a clustered index. It's nowhere near always true but you'll almost believe it by the time you've read the internet. – LoztInSpace Jun 11 '15 at 7:25
1

Even if the data is not stored on the leaf level, the index will provide your database engine with a very exact location in the data pages where to look for the results.

I.e. instead of browsing all of your table's data pages for relevant data, your index seek first browses fewer and smaller pages, ideally with a hierarchy (so that it browses even fewer).

As a result you get a number of locators, which will admittedly have to be looked up from the complete set of (unsorted) data pages, but since you're looking up a specific page and location every time, it's still reasonably fast.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.