Search code examples
sql-serverindexingdatabase-performance

Performance effect of include column in index

We know that having index on a table, degrades the insert/update/delete (CUD) performance. I want to know if I use a field as included column, how much affect (CUD) performance.

what I mean is, comparing the performance degradation between following indexes:

CREATE UNIQUE NONCLUSTERED INDEX [Idx1] ON dbo.Table (Col1, Col2)

and 

CREATE UNIQUE NONCLUSTERED INDEX [Idx1] ON dbo.Table (Col1, Col2) INCLUDE (Col3)

Notes:

  1. Col3 is a small field (like int/decimal/date)
  2. The table has about 100 million rows
Solution

  • If the INCLUDE column covers a query, that query is likely to be much faster. The down side is that other queries that use the index may be slightly slower.

    Queries that are not covered by this index may perform more I/O when using this index. Adding an INCLUDE column will widen the size of the rows on the non-clustered index leaf pages. Wider rows means that fewer rows fit in a single leaf page. Queries that use this index to find many rows will have to load more pages.

    The INCLUDE column will cause the index to take up more disk space. How much space depends on the data type of the column.

    The index may take up more space in memory if many rows are scanned.

    The best way to determine the impact of the INCLUDE column is to measure it. Run queries against the table before hand and measure I/O, disk space, and record the query plan. Run queries against it afterwards and do the same.