Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a table in one of our databases with approximately following structure:

Field1 int primary key
Field2 int foreign key A
Field3 int foreign key B
Field4 int foreign key C
Field5 ... Field70 diverse data types, for the "payload"

There are indices on the foreign key columns, albeit only with key columns, without any included columns. The index analysis routine keeps suggesting new indices with the same key columns for which there are already indices there, but with many included columns - in one case even all the Field5 to Field70. I am a bit hesitant to create an index with so many included columns - it feels like duplicating the table itself.

Are there some rule-of-thumb limits for the count of included columns in an index, or should I just go forward and replace the existing index (key column only) with an index with the same key column and so many included columns?

Thanks in advance.

share|improve this question
    
it feels like duplicating the table itself that's exactly right but it's not a problem by itself. – usr Feb 11 at 15:38

Suggested indexes always tend to include as many columns as needed by queries accessing the data, in order to have covering indexes and eliminate the need for lookups.

Whether this is a good thing or not, only you can tell. It highly depends on the shape of your workload. Some queries will highly benefit from covering indexes, some others will barely improve.

Having columns included in your nonclustered indexes will also mean that whenever you change the data in those columns, the changes will have to be written to the index as well. Broad indexes have a cost.

It is also possible that some of those columns are better placed inside the index key rather than included. Some queries might benefit from ordered data in the index.

The only way to know for sure is benchmarking:

  1. Capture a production workload and sync it with a backup
  2. Restore the backup to your test server
  3. Run the workload against the test server and analyze performance indicators and execution times
  4. Apply the changes you identified as sensible to your indexes
  5. Replay the workload
  6. Compare performance
  7. Rinse and repeat

There is no other way to know for sure.

share|improve this answer
2  
It's also possible the clustered index is not well-designed, and modifying it could provide major performance improvements. @ErichHorach - look at index stats to see how the clustered index and other indexes are being used. – Max Vernon Feb 11 at 12:53
    
Ratio of (seeks + scans + lookups) / updates ranges from 20 / 1 to 60 / 1 for the existing indices. Would you say the workload is "select heavy" enough to justify adding the included columns to the indices? – Erich Horak Feb 11 at 13:19

Some of your main considerations with included columns are:

  • Would the query plan otherwise result in a lookup?
  • How many executions occur for the lookup operator?
  • Is the added overhead of maintaining that extra piece of data (for change) counter productive to performance? -and remember included columns are only present in the leaf level pages of the non clustered index so they are nowhere near as costly as index columns.
  • Added storage overhead on disk and obviously (if read) in buffer cache.
share|improve this answer
    
"included columns are only present in the leaf level pages of the non clustered index" --> this is true only for unique indexes. Non-unique indexes include the columns also at intermediate levels. I think the key difference, performance wise, is the lack of sort order, so reduced CPU load. Space saving, not so much. – spaghettidba Feb 11 at 14:21
    
@spaghettidba are you sure (that non-unique indexes include the columns also at intermediate levels)? Any link that verifies this? – ypercubeᵀᴹ Feb 12 at 7:59
    
@ypercubeᵀᴹ t.co/iW4eMLn7Pz and t.co/tiTg2BFaDI and... the long overdue blog post I'm writing on the subject :-) – spaghettidba Feb 12 at 8:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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