3

I have a really large SQL SERVER table with only non clustered indices for several columns , while querying the table the execution plan shows RID Lookups , i expected to see this kind of lookup because the columns in the select list are not part of a covering index or included in another index .

The non-clustered indices were created on several columns because each one of these columns is a part of a different query with different select lists and search conditions.

Due to size and storage issues creating a covering index or a new index with included columns while keeping the current non-clustered indices is not feasible.

Now the question is :

  1. Can i replace the non-clustered indices with one covering index ?
  2. If not, is there any way to get rid of RID Lookup rather than a covering index ?
  3. Does each query require a different covering index depending on the columns in the select list and the search conditions ?
  4. I have no clustered indexes in the table , does adding a unique column and setting it as a primary key helps in getting rid of RID Lookups ?
  5. Any notes or comments to solve such problems are welcome ..
Improve this question
4
  • You can add lookup rows as included column in index to avoid RID lookup. This wont affect size much
    – Shanky
    Sep 17, 2015 at 11:17
  • Re #4, RID lookups will be replaced by key lookups if you do nothing more than add a clustered surrogate primary key. Post DDL and sample queries.
    – Dan Guzman
    Sep 17, 2015 at 11:26
  • @Shanky you mean by "add lookup rows as included column in index" creating an index with included columns ? if yes does it mean i have to create one for each query with different select list and search condition ?
    – Daniel Attieh
    Sep 17, 2015 at 11:36
  • Yes I meant that only but I am not sure how much indexes we are talking about. Lookups mean the rows were not present in index so it had to go to heap(in your case) to find out other matching rows.
    – Shanky
    Sep 17, 2015 at 11:38

1 Answer 1

Reset to default
6

Can i replace the non-clustered indices with one covering index ?

No. Suppose you sometimes find people by LastName and sometimes by FirstName. An index on (LastName, FirstName) won't help you find people by FirstName.

If not, is there any way to get rid of RID Lookup rather than a covering index ?

Not in a way that's particularly useful.

Does each query require a different covering index depending on the columns in the select list and the search conditions ?

Pretty much. Your job is to come up with a compromise.

I have no clustered indexes in the table , does adding a unique column and setting it as a primary key helps in getting rid of RID Lookups ?

A PK doesn't necessarily mean a clustered index. And having a CIX just means your RID Lookups will become Key Lookups, which are potentially worse. But without CIXs you have heaps which can fragment when you change or delete data. So CIXs are fine, but won't improve performance of your Lookups.

Improve this answer
2
  • For question number 3 , the same answer apply for index with included columns ?
    – Daniel Attieh
    Sep 17, 2015 at 11:27
  • 1
    An index with included columns is how you make a covering index. Anything that is not used in a Seek Predicate may as well be just an included column.
    – Rob Farley
    Sep 17, 2015 at 11:41

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.