Expensive Query Plan Analysis

Analyzes the plan cache (and optionally Query Store) for the most expensive queries and identifies optimization opportunities. This complements the other indexing tools — duplicate-index, key-lookup, and unused-index find problems with existing indexes. This tool finds problems with queries — specifically, the queries that would benefit most from new or better indexes.

Finding Types

TABLE_SCAN

Identifies clustered index scans, heap scans, and index scans on large tables. These are operations where the optimizer chose to read a significant portion of a table because no suitable index exists for the query's predicates.

Only scans with ≥ 1,000 estimated rows are flagged. Small-table scans are normal and not worth optimizing.

MISSING_INDEX

The SQL Server optimizer embeds missing index hints directly in execution plans when it identifies that an index would have improved the query. These are more targeted than the DMV-level suggestions because they come from specific query plans with real execution statistics.

Only hints with impact ≥ @MinImpact (default 50%) are flagged. After collection, the tool:

1. Deduplicates exact matches (same keys + includes on the same table)
2. Consolidates overlapping recommendations (same keys, different includes → merge to widest include set)
3. Validates columns against the target table schema (the optimizer can misattribute columns in multi-table joins)
4. Generates scripts via sp_BuildIndexScript which checks existing indexes to determine the right strategy (CREATE / EXTEND / REBUILD / skip if covered)
5. Estimates size via the hybrid size estimator

REGRESSED_QUERY

Requires @WithQueryStore = 1. Compares recent query performance (last N days via @LookbackDays) against historical performance. Queries where recent average logical reads are ≥ 3x worse than historical are flagged.

This catches:

• Plan regressions — the optimizer picked a worse plan after a recompile
• Parameter sniffing — a plan compiled for atypical parameter values
• Data growth — a table grew large enough that the existing plan no longer works well
• Missing statistics — stale stats caused a bad plan choice

Analysis Engine

Data Flow

sp_ExpensiveQueryRecommendations(@RunAnalysis = 1)
  |
  +--> sp_ExpensiveQueryAnalysis
  |      Phase 1: Plan Cache (always runs)
  |        1A. Collect top N queries from sys.dm_exec_query_stats
  |            ranked by @SortBy (READS, CPU, DURATION, EXECUTIONS)
  |        1B. Parse plan XML via XQuery for scans
  |            (only scans with >= 1000 estimated rows)
  |        1C. Parse plan XML for missing index hints
  |            (only hints with >= @MinImpact)
  |        1D. Filter results to target databases
  |
  |      Phase 2: Query Store regressions (when @WithQueryStore = 1)
  |        - Per database: compare recent vs historical avg reads
  |        - Flag queries where recent >= 3x worse
  |
  |      -> writes indexing.ExpensiveQueryResults
  |
  +--> Post-processing (MISSING_INDEX findings):
  |      - Deduplicate exact matches
  |      - Consolidate overlapping (same keys, merge includes)
  |      - Validate columns exist on target table
  |      - Generate scripts via sp_BuildIndexScript
  |      - Estimate size via sp_GetHybridIndexSizeEstimate
  |      -> updates CreateStatement, EstimatedSizeGB
  |
  +--> Generate ScriptText output grouped by database -> table -> finding type

Server-Wide Single Pass

The plan cache analysis runs server-wide in a single pass — no per-database loop needed. The top N queries are collected from sys.dm_exec_query_stats, then their plan XML is parsed using XQuery with the showplan namespace. Database identification comes from the plan XML's Object/@Database attribute, and results are filtered to target databases afterward.

Column Validation

The optimizer can misattribute predicate columns to the wrong table in multi-table joins. sp_BuildIndexScript validates that all recommended key and include columns actually exist on the target table. Findings with invalid columns are flagged as SKIPPED rather than generating broken CREATE INDEX statements.

Query Store Integration

When @WithQueryStore = 1, the tool adds a second analysis phase that runs per-database (only databases with Query Store enabled). It compares two time windows:

A query is flagged as regressed when recent_avg_reads / historical_avg_reads ≥ 3.0. The regression factor is included in the output so you can prioritize the worst regressions.

Script Generation

For MISSING_INDEX findings, the tool generates ready-to-run CREATE INDEX statements using the same infrastructure as the rest of the suite:

• Existing index awareness — sp_BuildIndexScript checks current indexes and determines CREATE (new), EXTEND (add includes to existing), REBUILD (re-enable disabled), or SUPERSET_COVERED (skip, already handled)
• Naming convention — IX_{table}_{keys}[_INCLUDES], with intelligent truncation at 128 chars
• Enterprise detection — WITH (ONLINE = ON) on Enterprise/Developer editions
• Size estimation — via hybrid estimator with v2.0 data sampling
• @RunnableUnder — edition-aware thresholds; indexes above the threshold are wrapped in /* */
• Cross-tool deduplication — when run via the orchestrator, findings already covered by Key Lookup or other tools are noted as "Covered above by..."

For TABLE_SCAN and REGRESSED_QUERY findings, no CREATE INDEX is generated — these are diagnostic findings that require manual investigation.

Parameters

sp_ExpensiveQueryRecommendations (reporting)

sp_ExpensiveQueryAnalysis (engine)

Called by the recommendations proc when @RunAnalysis = 1. Same parameters as above minus @RunnableUnder, @FindingType, and @TableName (those are reporting-only filters).

Results Table

indexing.ExpensiveQueryResults — one row per finding, with ResolvedDate tracking.

Usage Examples

-- Default: analyze plan cache for scans and missing indexes
EXEC indexing.sp_ExpensiveQueryRecommendations @RunAnalysis = 1;

-- Single database with high thresholds
EXEC indexing.sp_ExpensiveQueryRecommendations
    @RunAnalysis = 1,
    @DatabaseName = 'MyDatabase',
    @TopN = 100,
    @MinImpact = 90;

-- Fleet-wide: top 1000 queries, edition-aware threshold
EXEC indexing.sp_ExpensiveQueryRecommendations
    @RunAnalysis = 1,
    @DatabaseName = 'MyApp_%',
    @TopN = 1000,
    @MinExecutions = 500,
    @RunnableUnder = '1,5e';

-- Sort by CPU instead of reads
EXEC indexing.sp_ExpensiveQueryRecommendations
    @RunAnalysis = 1,
    @SortBy = 'CPU';

-- Include Query Store regression detection
EXEC indexing.sp_ExpensiveQueryRecommendations
    @RunAnalysis = 1,
    @WithQueryStore = 1,
    @LookbackDays = 14;

-- Only regressed queries
EXEC indexing.sp_ExpensiveQueryRecommendations
    @FindingType = 'REGRESSED_QUERY';

-- Only missing index findings, lower impact threshold
EXEC indexing.sp_ExpensiveQueryRecommendations
    @RunAnalysis = 1,
    @FindingType = 'MISSING_INDEX',
    @MinImpact = 30.0;

-- View existing results without re-analyzing
EXEC indexing.sp_ExpensiveQueryRecommendations;