SQL Server : Included columns in an Index: is there something like "too many"?

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.

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:

• Capture a production workload and sync it with a backup

• Restore the backup to your test server

• Run the workload against the test server and analyze performance indicators and execution times

• Apply the changes you identified as sensible to your indexes

• Replay the workload

• Compare performance

• Rinse and repeat

There is no other way to know for sure.

StackExchange Database Administrators Q#128977, answer score: 3