Parameter sniffing triage in five minutes

A short checklist when plans flip between good and terrible.

2026-07-16

/*
================================================================================
Purpose:        Parses the XML execution plan cache to identify key lookups 
                (non-covering indexes) and implicit conversions (SARGability).
Provides:       Top 20 queries with Key Lookups and top 20 queries with Implicit 
                Conversions, including execution counts and XML query plans.
Importance:     Essential for identifying hidden performance killers that waste 
                CPU and I/O due to data type mismatches or suboptimal indexing.
Interpretation: High "Avg_CPU_ms" for these queries indicates high-impact 
                 optimization opportunities. Check Plan_XML for the specific nodes.
Action: For Key Lookup queries (first result set), create a covering nonclustered index that includes all columns from the SELECT and WHERE clauses to eliminate the lookup. For Implicit Conversion queries (second result set), fix the data type mismatch — typically the application is passing a string (NVARCHAR) to an INT or DATETIME column, or the join columns have mismatched types. Use the Plan_XML column to identify the specific node.
Criticality:    High
Author:        Ravi Sharma
================================================================================
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

-- 1. Find Top 20 Queries with Key Lookups
PRINT 'Searching for Key Lookups in Plan Cache...';
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 20
    qs.execution_count,
    qs.total_worker_time / 1000 AS [Total_CPU_ms],
    (qs.total_worker_time / qs.execution_count) / 1000 AS [Avg_CPU_ms],
    st.text AS [Parent_Query],
    qp.query_plan AS [Plan_XML]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.exist('//IndexLookUp') = 1
ORDER BY qs.total_worker_time DESC;

-- 2. Find Queries with Implicit Conversions (Plan Bloat / SARGability issues)
PRINT 'Searching for Implicit Conversions in Plan Cache...';
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 20
    qs.execution_count,
    st.text AS [Query_Text],
    qp.query_plan AS [Plan_XML]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.exist('//ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")]') = 1
ORDER BY qs.total_worker_time DESC;

Review the full script and confirm the connected SQL Server instance before execution. The run instructions and output guide appear later in this article.

Parameter sniffing triage in five minutes

Start with actual vs estimated rows for the bad plan, then compare sniffed parameters for good and bad executions.

Only then decide between recompile hints, optimized plan forcing, or query store plan regression controls. Blind OPTION (RECOMPILE) everywhere is not a strategy.

Why parameter sensitivity deserves a method

The central principle is that one cached plan may not serve selective and broad parameter values equally well. A single metric, recommendation, or successful command is not enough. The decision has to connect an observed symptom to a plausible cause, a controlled change, and a result that can be verified under the workload that matters.

Evidence to collect

Use evidence from the same time window whenever possible:

Query Store plan and runtime variation
data skew and histogram coverage
estimated versus actual rows
compile and execution frequency

A practical workflow

Reproduce fast and slow parameter classes.
Confirm that the plan difference explains the cost.
Choose the narrowest mitigation.
Monitor plan and runtime behavior after the change.

Common mistakes

Avoid these shortcuts:

adding RECOMPILE everywhere
optimizing for one convenient value
confusing blocking or resource pressure with plan sensitivity

Verification and completion

Define completion before making the change. Confirm correctness first, then compare latency, throughput, reads, CPU, waits, storage behavior, or recovery time as appropriate for Parameter sniffing triage in five minutes. Test negative and boundary cases, not only the path expected to succeed. Watch the system long enough to cover the relevant workload pattern.

Download and run the diagnostic

Plan cache diagnostic

Download plan_cache_deep_dive.sql

Before you run it

Run the script first on a non-production or read-only connection when possible.
Review the complete SQL file before execution and confirm that the connected instance is the intended target.
Required access: VIEW SERVER STATE.
Open SQL Server Management Studio or Azure Data Studio, connect to the instance, open the downloaded file, and select Execute. Do not run diagnostic scripts through an application login.
Save the result grid with the server name, collection time, and incident window. DMV output changes over time and often resets after a SQL Server restart.

What the script does

Surfaces high-cost cached plans and plan-cache evidence that can help identify unstable estimates, reuse patterns, and parameter-sensitive workload behavior. The download is intended for diagnosis and does not apply an automatic remediation. Read every statement anyway, especially when adapting the script for automation or a different SQL Server version.

How to read the output

Compare execution count, total and average cost, compiled versus runtime values when available, and estimated versus actual behavior from a reproduced plan. Cache evidence suggests candidates; Query Store is better for historical confirmation.