Index Consolidation Plan

Overview

The other index reports each list one kind of finding: missing index suggestions, duplicates, unused indexes. Acting on each list separately is how a table ends up with nine overlapping indexes. The Index Consolidation Plan is the step after them: for every table in one database it treats the existing indexes and the suggestions together and works out the smallest set of changes, with an estimate of what they cost and save, and one script with a matching rollback.

Action What it means
CREATE A suggestion that nothing serves becomes a new index, named IX_<Table>_<Key1>_<Key2>.
WIDEN An existing index already has the keys a suggestion needs, so it takes the extra included columns (or, when it is not unique, the extra trailing equality keys) instead of a new index being created. It keeps its name, so index hints still work.
MERGE Two non unique indexes where one’s keys lead the other’s: (A) is merged into (A, B), which takes the union of the included columns, and (A) is dropped.
DROP An exact duplicate (the copy kept is the one Duplicate Indexes keeps), or an index with no seek, scan or lookup since SQL Server started.
KEEP An index that already covers one or more suggestions. Nothing to do; the request most likely predates the index.

Nothing on this page changes the database. It writes scripts; running them is up to you.

Where to find it

A database-level report, from SQL Server 2008. Select a database in the tree, open Real Time, Indexing, then Index Consolidation Plan. It is also linked from:

  • the Indexing Overview footer;
  • Build consolidation plan… on the Missing Indexes toolbar;
  • Include in consolidation plan… on the Duplicate Indexes toolbar;
  • the right-click Go to menu of any grid that lists tables: Index Consolidation Plan for [dbo].[Orders] opens the plan narrowed to that table (All tables on the toolbar widens it again).

Reading the page

The heading gives the uptime the usage numbers cover, then two lines:

  • Plan – the default plan: tables touched, the count of each action, the index count before and after, the estimated size added and removed, and the estimated writes per day added and removed.
  • Selected – the same totals for the rows that are ticked. The totals always equal the sum of the ticked rows.
Column What it is
(tick) Whether the row goes into the scripts. CREATE, WIDEN and MERGE are ticked by default; a DROP only when its verdict is Safe. Blocked rows cannot be ticked.
Table The table.
Action CREATE, WIDEN, MERGE, DROP or KEEP.
Verdict Safe, Review (worth a look before running) or Blocked (never scripted).
Index The index acted on, or the name of the new index.
Detail The keys and includes of a new index, what a WIDEN adds, what a MERGE folds in, why a DROP.
Size (est.) Estimated size added or removed.
Writes/day Estimated write operations a day added or removed. For a new index this is the table’s own write rate, an upper bound (marked “max”).
Benefit The summed benefit of the suggestions the row serves, the same score the Missing Indexes report uses.

Select a row to see its reasoning in the side pane: the suggestions it serves, the index it replaces, the seeks, scans, lookups and updates since the restart, the warnings, and the statements for that row.

Guardrails

These are never dropped or altered; a row that would touch one is Blocked with the reason:

  • the clustered index, a primary key, a UNIQUE constraint, or a hypothetical index;
  • the only index supporting a foreign key;
  • an index named in an index hint in a procedure, function, view or trigger (the module text is searched, so this is reported as a possible reference);
  • an index used by a forced Query Store plan;
  • a key column that is a large object type, or a key over the 1,700 byte limit (900 bytes before SQL Server 2016); text, ntext and image columns cannot even be included.

A DROP is Review rather than Safe when SQL Server has been up for less than the unused threshold (30 days by default; a banner says so), when the unused index is barely written anyway, or when the duplicate rule says so (a unique copy, or a copy read more than the keeper). An index with more included columns than the wide threshold is Review too.

When the login cannot read index usage (VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later), the plan still lists every index but offers no unused drops, and says why.

The scripts

Script selected… opens the forward script for the ticked rows:

  1. A preflight that stops before anything is changed if any index the plan touches was dropped or changed since the plan was read (a checksum of its key and included columns, filter and uniqueness is compared), or if a new index name is already taken.
  2. CREATE the new indexes, then WIDEN and MERGE (CREATE INDEX ... WITH (DROP_EXISTING = ON) on the index kept, with every option it had), then DROP last, so nothing is removed before its replacement exists.
  3. A verification query listing the indexes of every table touched.

Every change is its own batch with a PRINT, and nothing wraps them in one transaction, because a large index build inside a single transaction holds the log until the end. If a batch fails the script turns SET NOEXEC ON and skips the rest; build the plan again before running it again. ONLINE = ON is added on Enterprise, Developer and Azure editions. Run the script in SSMS, or in sqlcmd with -I.

Script rollback… writes the inverse from the definitions captured when the plan was read: it re-creates every dropped index, restores every widened or merged index to its exact previous definition, and drops every index the plan created, each step guarded with IF EXISTS / IF NOT EXISTS.

Save both… writes <database>_index_plan.sql and <database>_index_plan_rollback.sql side by side. Save plan HTML… saves the plan with each row’s reasoning. Copy puts the plan on the clipboard as text.

Options

Option Default
Include suggestions with at least this share of the database’s missing index benefit 5%, the line the Missing Indexes chart draws
Treat an index as unused after this many days of uptime 30
Most key columns in a planned index 5
Included columns before an index counts as wide 10
Warn when a table would carry more nonclustered indexes than 10
MAXDOP for the builds 0 (the server’s setting)
ONLINE = ON On where the edition has it
SORT_IN_TEMPDB = ON Off
New indexes take the data compression of the table On

Measure widths samples the variable width columns the planned indexes would carry (AVG(DATALENGTH()), with TABLESAMPLE (1 PERCENT) on tables over 100,000 rows) so the size estimates use real widths instead of half the declared size.

Estimates

Sizes are estimates: rows times the estimated width of the key and included columns, plus the row locator (the clustered key, or an 8 byte RID on a heap) and the row overhead, divided by the page fill. A widened index is scaled from its actual size today. Write rates come from sys.dm_db_index_operational_stats divided by the days since the restart; both those numbers and the missing index suggestions reset when SQL Server restarts.

Report Why you would go there
Indexing Overview The overall index health of the database.
Missing Indexes The suggestions the plan starts from, with their benefit against write cost.
Duplicate Indexes The duplicate groups and the keeper rule the plan shares.
Unused Indexes Indexes with no reads since the restart.
Plan Warnings The missing index requests read from the plans that made them.