Missing Index Recommendations (optimize-index)

The most complex and powerful tool in the suite. A multi-stage consolidation pipeline that takes SQL Server's raw DMV missing index suggestions — often hundreds of noisy, overlapping, context-free recommendations — and distills them into a smaller set of actionable, non-overlapping index operations with smart size estimation and enterprise-aware script generation.

Why This Tool Exists

SQL Server's sys.dm_db_missing_index_* DMVs are the starting point for most index tuning. But they have significant limitations:

• No consolidation. If 10 queries need an index on [status] with different include columns, the DMVs produce 10 separate recommendations instead of one merged index.
• No awareness of existing indexes. The DMVs may recommend creating an index that's a near-duplicate of one that already exists, or that could be satisfied by extending an existing index with a couple of includes.
• No prefix detection. A recommendation for [status] and another for [status, site_id] are treated as unrelated, when the longer key covers both.
• No size estimation. There's no indication whether a recommended index will be 5 MB or 50 GB.
• No actionable output. The DMVs give you column lists, not ready-to-run CREATE INDEX statements with proper naming, enterprise clauses, or collision handling.

This tool solves all of these. In production testing across 50+ servers with 4,000+ databases, a typical fleet generates ~2,000 raw DMV recommendations that consolidate down to ~1,600 actionable operations — an 18-20% reduction in index count, with each surviving recommendation being higher quality than any individual DMV suggestion.

Pipeline Architecture

The tool runs as a seven-stage pipeline, each stage feeding the next through staging tables in the indexing schema:

sp_CreateIndexOptimizationRecommendations(@DatabaseName, @RunnableUnder)
  |
  Stage 1: sp_LoadDataFromServer
  |   Collects raw DMV missing index data + current index inventory
  |   → indexing.IndexRecommendations, indexing.CurrentIndexes
  |
  Stage 2: sp_ConsolidateRecommendations
  |   Basic consolidation: group by identical key columns, merge includes
  |   → indexing.ConsolidatedRecommendations
  |
  Stage 3: sp_ApplyPrefixConsolidation
  |   Prefix + cross-coverage: merge where keys are leftmost prefixes
  |   → indexing.PrefixConsolidatedGroups
  |
  Stage 4: sp_ApplySuperConsolidation
  |   Super-consolidation: merge groups with same first key + compatible includes
  |   → indexing.SuperConsolidatedGroups
  |
  Stage 5: sp_RemoveRedundantAfterSuperConsolidation
  |   Remove groups made fully redundant by super-consolidated indexes
  |   → indexing.CleanedConsolidatedGroups
  |
  Stage 6: sp_AnalyzeIndexStrategies
  |   Match against existing indexes: CREATE, UPDATE, EXTEND, or REBUILD
  |   → indexing.AnalysisResults
  |
  Stage 7: sp_GenerateOptimizationScript
      Generate ready-to-run SQL with size estimation + enterprise clauses
      → indexing.GeneratedScripts, indexing.MissingIndexResults

Consolidation Deep Dive

Stage 2: Basic Consolidation

Groups recommendations with identical key columns on the same table and merges their include columns. The first recommendation's column order is preserved; additional includes from other recommendations are appended in first-appearance order.

Stage 3: Prefix Consolidation

Detects two patterns across groups on the same table:

Pattern A — Leftmost Prefix: If Group A's keys are a leftmost prefix of Group B's keys, Group B already covers Group A's seeks. Merge A's includes into B.

Pattern B — Cross-Coverage: If Group A's include columns appear in Group B's keys (or vice versa), the groups can be merged by promoting shared columns to keys.

Stage 4: Super-Consolidation

The most aggressive consolidation stage. Finds groups that share the same first key column with compatible include columns and merges them into a single "super-index."

A size threshold (100K) prevents creating excessively wide indexes. Only groups whose combined key+include width stays under the threshold are merged.

Stage 5: Redundancy Removal

After super-consolidation, some original groups may be fully covered by the new super-indexes. This stage detects and removes those redundant groups using the fn_SuperGroupCoversIndividual function, which checks whether a super-group's keys and includes fully satisfy each individual group's needs.

Strategy Analysis

After consolidation, each surviving recommendation is matched against the server's existing indexes to determine the optimal action. The system uses a scoring algorithm to find the best match:

Recommendation needs: [access_category_id] INCLUDE [doc_status]
Existing index:       [info_card_id], [doc_status], [access_category_id]

Strategy: SUPERSET_MATCH (keys are a subset of existing)
  Base score:    700
  Position:      [access_category_id] at position 3 → +10
  Penalty:       3 existing keys × 5 = -15
  Total:         700 + 10 - 15 = 695 → EXTEND existing index

The strategy with the highest score wins. Ties are broken by fewest extra keys (prefer extending indexes that are closest to the recommendation's shape).

Size Estimation

Every CREATE and EXTEND operation includes an estimated index size. The estimation uses a hybrid approach:

Strategy 1: Smart Estimation (similarity-based)

If the table already has indexes with similar structure, the system uses those as a baseline. An exact key match with 3 existing includes that needs 2 more includes will estimate base_size + (2 × per_include_cost), where per_include_cost is derived from the existing index's actual size.

Strategy 2: Calculated Estimation (data sampling, v2.0)

When no similar indexes exist, the system falls back to calculation. Version 2.0 samples actual data using AVG(DATALENGTH(column)) on 10,000 rows per column instead of using max_length guesses. This is dramatically more accurate — the old approach could overestimate by 40x or more on tables with wide NVARCHAR columns that are mostly NULL or short.

Script Generation Features

• Enterprise detection: Automatically adds WITH (ONLINE = ON) on Enterprise and Developer editions. Standard edition gets offline operations.
• @RunnableUnder: Edition-aware thresholds. '1,5e' means 1 GB for Standard, 5 GB for Enterprise. Indexes over threshold are wrapped in /* */ for manual review; smaller indexes are ready to execute immediately.
• Filtered index protection: Never recommends dropping, modifying, or replacing filtered indexes (indexes with WHERE clauses).
• Name collision handling: When a generated index name matches an existing index, uses WITH(DROP_EXISTING=ON) for atomic replacement. When names differ, generates CREATE + DROP as separate statements.
• Index name length: SQL Server's 128-character limit is enforced. Long names are intelligently truncated with an _xMORE suffix.
• Documentation per operation: Each generated statement includes comments showing the original recommendation advantage, key/include columns, and which recommendations it covers.
• ResolvedDate tracking: Each recommendation includes an UPDATE ... SET ResolvedDate = GETDATE() statement so applied recommendations are marked as resolved and don't reappear on the next run.

Internal Objects

Tables (9 staging + 1 results)

Functions (8)

Procedures (~13)

Parameters

sp_CreateIndexOptimizationRecommendations (entry point)

sp_MissingIndexRecommendations (per-finding view)

Usage Examples

-- Full analysis, all databases
EXEC indexing.sp_CreateIndexOptimizationRecommendations;

-- Single database with debug output
EXEC indexing.sp_CreateIndexOptimizationRecommendations
    @DatabaseName = 'MyDatabase', @Debug = 1;

-- With edition-aware RunnableUnder threshold
EXEC indexing.sp_CreateIndexOptimizationRecommendations
    @DatabaseName = 'MyApp_%',
    @RunnableUnder = '1,5e';

-- Per-finding view (runs analysis first)
EXEC indexing.sp_MissingIndexRecommendations
    @RunAnalysis = 1,
    @DatabaseName = 'MyApp_%',
    @RunnableUnder = '1,5e';

-- View previously generated scripts
EXEC indexing.sp_ExportGeneratedScript @ServerName = @@SERVERNAME;

-- List all generated scripts
SELECT ScriptID, ServerName, GeneratedDate, LEN(ScriptText) AS SizeChars
FROM indexing.GeneratedScripts
ORDER BY ScriptID DESC;