SQL Server wide indexes with many include columns

Question

Should I be careful adding too many include columns to a non-cluster index?

I understand that this will prevent bookmark look-ups on fully covered queries, but the counter I assume is there's the additional cost of maintaining the index if the columns aren't static and the additional overall size of the index causing additional physical reads.

Answer 1

I agree with both answers so far, just want to add 2 things:

For covering indexes, SQL Server 2005 introduced the INCLUDE clause which made storage and usage more efficient. For earlier versions, included columns were part of the tree, part of the 900 byte width and made the index larger.

It's also typical for your indexes to be larger than the table when using sp_spaceused. Databases are mostly reads (I saw "85% read" somewhere), even when write heavy (eg INSERT looks for duplicates, DELETE checks FKs, UPDATE with WHERE etc).

Answer 2

You said it in the question: the risk with having many indexes and/or many columns in indexes is that the cost of maintaining the indexes may become significant in databases which receive a lot of CUD (Create/Update/Delete) operations.

Selecting the right indexes, is an art of sort which involves balancing the most common use cases, along with storage concerns (typically a low priority issue, but important in some contexts), and performance issues with CUD ops.

Answer 3

I agree with mjv - there's no real easy and quick answer to this - it's a balancing act.

In general, fewer but wider indices are preferable over lots of narrower ones, and covering indices (with include fields) are preferable over having to do a bookmark lookup - but that's just generalizations, and those are generally speaking wrong :-)

You really can't do much more than test and measure:

• measure your performance in the areas of interest
• then add your wide and covering index
• measure again and see if you a) get a speedup on certain operations, and b) the remaining performance doesn't suffer too much

All the guessing and trying to figure out really doesn't help - measure, do it, measure again, compare the results. That's really all you can do.