Included column savings vs performance impact
What I want to know, is this cost savings enough to outweigh any potential performance impact caused by additional column?
Here are some stats on the table/index with and without the included column (let me know if anything else is needed)
Table Index Space 421mb without vs 427mb with
Index Leaf Rows 504,841 without vs 511,080 with
The columns in the lookups
INT – 1
Numeric – 1
DateTime – 5
VarChar – 3
Would this add too much to the index?
- Cost percentages in execution plans are always estimates. Even in post-execution ("actual") plans. They are based on the optimizer's cost model, which is not meant to reflect your system's performance characteristics. The numbers and formulas exist primarily to give the optimizer a way to choose, generally, between different candidate plan shapes for a query. I cannot stress enough how literally you should NOT take these numbers 🙂
- Plan Explorer provides a button to directly upload a plan or session file with your question. These can be anonymized if the names are sensitive somehow, or general policy dictates. Queries run from PE provide the best diagnostic information.
- Included columns make the index structure larger.
- A larger structure (at the leaf level) means fewer index rows per page than before.
- Fewer rows per page means more memory is used by the index when in cache.
- It also means more disk I/O to read the index from disk.
- Included columns have to be maintained by any data modification query that affects those columns. This can change the update/insert/merge plan shape significantly, and so affect performance in ways that may not be easy to anticipate.
Ultimately, there are multiple competing factors. The only way to be sure is to test a representative workload with and without the included columns, as Aaron suggested. That said, the impact of adding a few small columns to the include list is often quite small. Just not always 🙂 Be very careful about adding large columns or too many columns though.