I have a query thats performing badly and has a RID lookup. There are already quite a few indexes on the table and in the query stats I can see they are used although some less than others and all are being updated.

My question is should I be looking to create a covering index for the poorly performing index, and when do I add columns as a key and when do I add them as includes?

Also how do I find out which queries are using the other indexes? It would be useful to know which these are such that if I removed them I would want to see if they would use the new covering index instead.

All that said when is it more efficient to have multiple indexes rather than one large covering index containing all the columns of the table.

Thanks in advance as I feel my knowledge has a lot of gaps here.

  • INCLUDE columns allow the query engine to avoid performing a secondary lookup for columns that are not in the index being used by the query. The data in INCLUDEd columns is stored in the leaf pages of the index, where they can't be used for WHERE/JOIN clauses. – Max Vernon Apr 30 '14 at 16:41
  • The database tuning adviser tool might provide some help. One caveat would be to not blindly implement all of its recommendations, but use its output as a starting point.technet.microsoft.com/en-us/library/ms173494(v=sql.105).aspx – Jeremy Apr 30 '14 at 16:52
  • 1
    I would highly suggest this book apress.com/9781430237419 on Expert Performance Indexing. I am not the author but this is the only book I know of on indexing. It is an excellent read so far and very good internal information on indexes. – Shawn Melton Apr 30 '14 at 17:24

Your Answer

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

Browse other questions tagged or ask your own question.