Query Plan Analysis

A one-off diagnostic tool for investigating a specific slow query. Unlike every other tool in the suite, which analyzes fleet-wide DMV data on a schedule, this tool focuses on a single query's execution plan and returns actionable recommendations to fix it.

A standalone diagnostic tool, not wired into sp_IndexOptimizationSuite. You reach for it when someone says "this query is slow." Pass in a query to execute and analyze, or a pre-captured plan XML from SSMS.

Why This Exists

When a query is slow, the answer is almost always in the execution plan. But reading a raw XML plan is tedious, and the most impactful findings follow predictable patterns:

• Stale statistics → optimizer picks the wrong join strategy → catastrophic performance
• Missing indexes → full table scans where a seek would work
• Key lookups → double I/O on every row
• Implicit conversions → index seeks become scans silently
• Cardinality misestimates → nested loops where hash joins are needed

This tool automates the detection of all five patterns and generates the exact SQL to fix each one.

What It Detects

1. Stale Statistics

The #1 cause of bad execution plans. The optimizer uses statistics to estimate row counts. When statistics are stale (many modifications since last update, or sampled at a low rate), the optimizer makes wrong assumptions about how many rows will flow through each operator. This leads to catastrophic join strategy choices — for example, choosing a nested loop (good for 7 rows) when the actual result is 5 million rows.

Output: Specific UPDATE STATISTICS [schema].[table] [stat_name] WITH FULLSCAN; statements — stat names, not just table names. You can copy and run them directly.

2. Missing Indexes

SQL Server's optimizer sometimes embeds missing index hints directly in the execution plan when it knows a better index would help. These are high-confidence recommendations because the optimizer calculated them during actual plan compilation for this specific query.

3. Key Lookups

A key lookup occurs when the optimizer finds the right row via a nonclustered index seek, but then has to go back to the clustered index to fetch additional columns that aren't in the nonclustered index. This doubles the I/O for every row returned.

4. Cardinality Misestimates

When the optimizer's row estimate for an operator is wildly different from reality, every downstream decision (join type, memory grant, parallelism) is wrong. This is the signature pattern of stale statistics — if you see 100x+ misestimates, look at the stale statistics findings first.

5. Implicit Conversions

When a query compares a column to a parameter of a different data type, SQL Server silently inserts a CONVERT_IMPLICIT operation. If the conversion is on the column side (not the parameter side), it prevents index seeks entirely — the optimizer must scan the whole index because it can't seek on a converted column.

Two Modes of Operation

@SQL Mode — Execute and Analyze

Pass the query as a string. The proc executes it, then grabs the plan from the plan cache.

EXEC indexing.sp_AnalyzeQueryPlan
    @SQL = 'SELECT o.OrderId, c.Name
            FROM dbo.Orders o
            JOIN dbo.Customers c ON o.CustomerId = c.Id
            WHERE o.OrderDate > ''2024-01-01''',
    @DatabaseName = 'MyDatabase';

How it works internally:

1. Injects a unique GUID marker comment into the SQL: /*AnalyzeQP_a1b2c3...*/
2. Executes the query via sp_executesql — results return to the caller (they'll scroll by)
3. Searches sys.dm_exec_cached_plans for the plan containing the GUID marker
4. Parses the cached plan XML with XQuery to extract findings

@PlanXML Mode — Analyze Without Executing

Pass a pre-captured plan directly. No query execution. Use this when:

• You already have a .sqlplan file from SSMS (Ctrl+L for estimated, Ctrl+M for actual, then right-click → Save Execution Plan As)
• The query is too expensive to re-run
• You want to analyze an actual plan with runtime row counts (for cardinality detection)
• You're analyzing a plan from a different server

DECLARE @plan XML = (SELECT BulkColumn FROM OPENROWSET(
    BULK 'D:\path\to\file.sqlplan', SINGLE_BLOB) AS x);
EXEC indexing.sp_AnalyzeQueryPlan @PlanXML = @plan;

Stale Statistics Threshold Logic

The plan XML lists every statistic the optimizer considered during compilation — not just the ones causing problems. Naive filtering (e.g., "any stat with modifications") produces too much noise. The thresholds are calibrated to flag genuinely problematic stats while filtering out healthy ones:

Output Format

The stored proc returns a result set with four columns, ordered by priority:

Priority order ensures the most impactful fixes appear first. Stale statistics are ranked highest because they are the most common root cause of bad plans and the easiest to fix.

Typical Workflow

1. Customer or monitoring reports a slow query
2. Run sp_AnalyzeQueryPlan with the query or a saved plan file
3. Copy the UPDATE STATISTICS statements from the Recommendation column
4. Execute them on the target database
5. Re-run sp_AnalyzeQueryPlan with the same query
6. If findings remain, address them (missing indexes, key lookups, etc.)
7. Repeat until the result set is empty — the query is now running on a clean plan

Parameters

One of @SQL or @PlanXML is required. If both are provided, @PlanXML takes precedence.

Installation

Standalone — not included in the suite install script. Deploy directly:

-- Run on each server where you want query plan analysis
USE DBA_Utilities;
GO
-- Execute the contents of query-plan-analysis.sql

Usage Examples

Quick diagnostic with @SQL

-- Analyze a slow query directly
EXEC indexing.sp_AnalyzeQueryPlan
    @SQL = 'SELECT * FROM dbo.Orders WHERE CustomerId = 42 AND OrderDate > ''2024-01-01''',
    @DatabaseName = 'MyDatabase';

Analyze a saved plan file

-- Load a .sqlplan file from SSMS
DECLARE @plan XML = (SELECT BulkColumn FROM OPENROWSET(
    BULK 'D:\Plans\slow_query.sqlplan', SINGLE_BLOB) AS x);
EXEC indexing.sp_AnalyzeQueryPlan @PlanXML = @plan;

Debug mode — inspect the captured plan

-- Returns the plan XML as an extra result set
EXEC indexing.sp_AnalyzeQueryPlan
    @SQL = 'SELECT COUNT(*) FROM dbo.LargeTable WHERE Status = ''Active''',
    @DatabaseName = 'MyDatabase',
    @Debug = 1;

Iterative fix workflow

-- Step 1: Find what's wrong
EXEC indexing.sp_AnalyzeQueryPlan @SQL = 'SELECT ...', @DatabaseName = 'MyDatabase';

-- Step 2: Fix the stale stats (copy from Recommendation column)
UPDATE STATISTICS [dbo].[Orders] [IX_Orders_CustomerId] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[Orders] [IX_Orders_OrderDate] WITH FULLSCAN;

-- Step 3: Verify — should return fewer or zero findings
EXEC indexing.sp_AnalyzeQueryPlan @SQL = 'SELECT ...', @DatabaseName = 'MyDatabase';