5

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.

Improve this question
0

2 Answers 2

Reset to default
3

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.
Improve this answer
0
3

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.

Improve this answer
2
  • 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.
    – Hannah Vernon ♦
    Commented Feb 11, 2016 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
    Commented Feb 11, 2016 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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