What is parameter sniffing in SQL Server?

In SQL Server, parameter sniffing occurs when the query engine creates an execution plan based on the specific parameter values used during the first compilation. This process is intended to optimize performance but can lead to inefficiencies if subsequent parameters require different execution strategies.

How to detect parameter sniffing in SQL Server?

In SQL Server, parameter sniffing is detected by identifying high variance in execution metrics for the same query hash. Using sys.dm_exec_query_stats, DBAs compare min_worker_time and max_worker_time to find queries that perform inconsistently based on input values.

Is OPTION(RECOMPILE) the best fix for parameter sniffing?

No. In SQL Server, OPTION(RECOMPILE) solves parameter sniffing by forcing a new plan for every execution, but it consumes significant CPU resources. It is best used for complex, infrequent queries rather than high-volume transaction processing.

Does Query Store fix parameter sniffing?

Yes. In SQL Server, Query Store identifies performance regressions caused by parameter sniffing and allows DBAs to force a known good execution plan, ensuring consistent performance regardless of parameter changes.

Why is my query fast in SSMS but slow in the application?

In SQL Server, this issue often occurs due to parameter sniffing and different SET options between SSMS and the application. The application may be stuck using a cached plan optimized for a different set of parameters.

Before we dive into today's topic, if you missed my previous post you can take a look at Check Index Fragmentation in 45 Seconds. Execution Engine | Part 14.

👉 If you found this deep-dive helpful, feel free to check out the ads—your support helps me keep creating high-quality SQL Server content for the community.

Check SQL Server Parameter Sniffing in 45 Seconds

Execution Engine | Part 15

Stop chasing ghosts and start identifying plan cache poisoning instantly.

In this post, I’ll show you how to detect Parameter Sniffing in under 45 seconds using DMVs. This is the #1 reason why a query is fast in SSMS but crawls in your application!

🧠 TL;DR BOX

✔️ What: SQL Server creates a plan based on the *first* parameter provided (the "sniffed" value).
✔️ The Trap: A plan optimized for 1 row is disastrous when the next execution needs 1,000,000 rows.
✔️ Quick Check: Compare Query Hash performance variations in the plan cache.
✔️ Action: Use Query Store or targeted hints to stabilize performance.

Hi SQL SERVER Guys and Gals,

We all know how important it is to make every second count when managing SQL Server environments. Parameter Sniffing is often treated like a mysterious curse, but it’s actually just a mechanical side effect of how the Execution Engine works to save optimization time. Let's dive into how to catch it in the act!

🔍 DIAGNOSIS

🧠 What it really is: In SQL Server, Parameter Sniffing occurs when the Optimizer takes the input parameters of the first execution and uses their cardinality to build a "one-size-fits-all" plan.

💣 The Problem: If your data distribution is non-uniform (skewed), a plan that uses a Nested Loop (perfect for 5 rows) will cause an IO meltdown if the next user passes a parameter that requires a Hash Match for 500,000 rows.

⚡ Key Insight: You aren't looking for a "bad" query; you are looking for a query that has massive variance in execution time or logical reads for the same query_hash.

🧪 QUERY

This script identifies cached plans where the difference between min and max worker time suggests a sniffing issue.

-- 🔍 Parameter Sniffing Diagnostic Query
-- ⚡ Target: Queries with high variance in execution time
SELECT TOP 20
    qs.query_hash,
    qs.execution_count,
    [Avg Time] = qs.total_worker_time / qs.execution_count,
    [Max Time] = qs.max_worker_time,
    [Min Time] = qs.min_worker_time,
    [Variance %] = CAST(((qs.max_worker_time - qs.min_worker_time) / NULLIF(CAST(qs.max_worker_time AS FLOAT), 0)) * 100 AS DECIMAL(5,2)),
    st.text AS [QueryText],
    qp.query_plan
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 qs.execution_count > 5
AND (qs.max_worker_time - qs.min_worker_time) > 1000000 -- Over 1 second difference
ORDER BY [Variance %] DESC;

🚀 FIX

If you catch a sniffing problem, you have three primary weapons:

✔️ OPTION (RECOMPILE): Forces a new plan every time. Great for infrequent, heavy reports.
✔️ OPTIMIZE FOR UNKNOWN: Tells SQL Server to use the average statistical density instead of a specific value.
✔️ Query Store (Plan Forcing): The modern way. Find the "good" plan in Query Store and force it so the engine stops guessing.

🚀 My REAL Strategy

In my 25 years of experience, developers often rush to OPTION(RECOMPILE). Don't do it for high-frequency OLTP queries; the CPU overhead of constant compilation will kill you. Instead, if you are on SQL Server 2022+, leverage Parameter Sensitive Plan (PSP) Optimization. It handles multiple plans for the same query automatically. If you're on older versions, Query Store is your best friend—force a stable "middle-ground" plan and go home early.

📊 TAKEAWAY

Parameter Sniffing is not a bug; it's a feature gone wrong. Use the variance in sys.dm_exec_query_stats to prove it exists, then use Query Store to stabilize it. Stability is usually better than a "perfect" plan that occasionally fails.

Sources: Microsoft: Query Processing Guide | Microsoft: PSP Optimization | Microsoft DevBlogs: Parameter Sniffing

📢 Support the Blog: Did you find this deep-dive helpful? The ads you see here are selected to reflect your interests. If a partner's offer catches your eye, give it a look! Your engagement helps me continue publishing premium SQL Server content for the community.

Biondi Luca @2026 - Sharing over 25 years of Gained Knowledge for Passion. Share if you like my posts!

Search This Blog

SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀