Home / Advanced SQL / SQL Server parameter sniffing / T-SQL / SQL Server Parameter Sniffing: What It Is and How to Fix It

SQL Server Parameter Sniffing: What It Is and How to Fix It

On

You've been there. A stored procedure runs in under a second for most users — then one morning, someone calls it with a slightly different value and it grinds to a halt for 45 seconds. Nothing in the schema changed. No new data was loaded. What happened?

Nine times out of ten, the culprit is SQL Server parameter sniffing.

Parameter sniffing is one of the most misunderstood and frustrating performance problems in SQL Server development. It's not a bug — it's actually a feature of the query optimizer that usually helps. But when data is unevenly distributed, it can silently destroy query performance and leave you scratching your head.

In this guide, you'll learn exactly what SQL Server parameter sniffing is, why it happens, how to diagnose it, and — most importantly — how to fix it using real examples from the WideWorldImporters sample database.

What Is Parameter Sniffing?

When SQL Server executes a stored procedure for the first time, it doesn't just run it blindly. It first compiles an execution plan — a step-by-step strategy for how to retrieve the data as efficiently as possible. During that compilation, it "sniffs" (peeks at) the current parameter values to estimate how many rows will be returned, and builds the plan accordingly.

Here's the key behavior: that compiled plan is cached and reused for all future executions, regardless of what parameter values are passed next time.

This is mostly a good thing. Compilation is expensive, and reusing plans saves CPU cycles. The problem arises when:

  • The first execution uses a rare, highly selective value (e.g., a customer in one city with 3 orders).
  • The plan built for that value uses an Index Seek — fast for small result sets.
  • A later execution passes a common value that returns 50,000 rows.
  • SQL Server reuses the old Index Seek plan — which is catastrophic for large result sets.

Or vice versa: the first call returns a huge result set and the plan is built for a full table scan, then a later call only needs 5 rows and gets punished by the wrong plan.

Step-by-Step: Seeing Parameter Sniffing in Action

Let's work through a realistic example using the WideWorldImporters database.

Step 1 — Create the Stored Procedure

Suppose we want to look up orders for a specific customer. Here's a simple stored procedure against Sales.Orders:

CREATE OR ALTER PROCEDURE Sales.usp_GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        o.ExpectedDeliveryDate,
        c.CustomerName,
        p.FullName AS SalespersonName
    FROM Sales.Orders o
    INNER JOIN Sales.Customers c ON c.CustomerID = o.CustomerID
    INNER JOIN Application.People p ON p.PersonID = o.SalespersonPersonID
    WHERE o.CustomerID = @CustomerID
    ORDER BY o.OrderDate DESC;
END;

Step 2 — First Execution (Rare Customer)

Imagine CustomerID = 1060 is a small account with only 2 orders. SQL Server sniffs the parameter, estimates a tiny result set, and builds a plan using an Index Seek on CustomerID.

-- Clear plan cache for this demo (never do this in production!)
DBCC FREEPROCCACHE;

-- First call: small customer, 2 orders
EXEC Sales.usp_GetOrdersByCustomer @CustomerID = 1060;

SQL Server builds and caches an Index Seek plan — perfectly efficient for 2 rows.

Step 3 — Second Execution (High-Volume Customer)

Now CustomerID = 1 is a major account with thousands of orders across many Sales.OrderLines. When we call the same procedure:

-- Second call: large customer, thousands of orders
EXEC Sales.usp_GetOrdersByCustomer @CustomerID = 1;

SQL Server reuses the cached Index Seek plan from the first execution. But fetching thousands of rows with repeated Index Seeks and Key Lookups is far slower than a simple table scan would be. You now have a bad parameter sniffing problem.

How to Diagnose Parameter Sniffing

Before you fix it, confirm the problem. Use this query to inspect the cached plan and its compiled parameter values:

SELECT
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us,
    qp.query_plan,
    SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE '%usp_GetOrdersByCustomer%'
ORDER BY avg_elapsed_us DESC;

Look at the XML plan. If you see a compiled value of @CustomerID = 1060 but the procedure is being called with @CustomerID = 1, that's your smoking gun.

Fix 1: OPTION (RECOMPILE)

The most direct fix is to add OPTION (RECOMPILE) to your query. This tells SQL Server to generate a fresh plan on every execution, so it always optimizes for the actual parameter values in use.

CREATE OR ALTER PROCEDURE Sales.usp_GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        o.ExpectedDeliveryDate,
        c.CustomerName,
        p.FullName AS SalespersonName
    FROM Sales.Orders o
    INNER JOIN Sales.Customers c ON c.CustomerID = o.CustomerID
    INNER JOIN Application.People p ON p.PersonID = o.SalespersonPersonID
    WHERE o.CustomerID = @CustomerID
    ORDER BY o.OrderDate DESC
    OPTION (RECOMPILE);  -- Forces a new plan each time
END;

When to use it: Best for procedures called infrequently, or where parameter values vary wildly. Avoid it on high-frequency procedures — the recompilation CPU cost adds up fast.

Fix 2: OPTIMIZE FOR UNKNOWN

This hint tells SQL Server to compile the plan using average statistics rather than the sniffed parameter value. The result is a "middle of the road" plan that won't be ideal for any one value, but won't be catastrophically bad either.

CREATE OR ALTER PROCEDURE Sales.usp_GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        o.ExpectedDeliveryDate,
        c.CustomerName,
        p.FullName AS SalespersonName
    FROM Sales.Orders o
    INNER JOIN Sales.Customers c ON c.CustomerID = o.CustomerID
    INNER JOIN Application.People p ON p.PersonID = o.SalespersonPersonID
    WHERE o.CustomerID = @CustomerID
    ORDER BY o.OrderDate DESC
    OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));
END;

When to use it: Good when no single set of parameter values dominates and you want consistent, predictable performance across the board.

Fix 3: OPTIMIZE FOR a Specific Value

If you know that most calls use a particular range or "typical" value, you can optimize for that value explicitly:

-- Most orders come from mid-range customers; optimize for a typical one
OPTION (OPTIMIZE FOR (@CustomerID = 500));

This forces the plan to be built for CustomerID = 500's data distribution, which works well if that's representative of typical usage.

Fix 4: Split the Procedure by Scenario

Sometimes the cleanest solution is to separate the logic into different procedures based on what you know about the data. For example, if you know some customers are high-volume accounts, route them differently at the application level:

-- For high-volume customers (e.g., those with > 500 orders)
CREATE OR ALTER PROCEDURE Sales.usp_GetOrdersByLargeCustomer
    @CustomerID INT
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        ol.StockItemID,
        ol.Quantity,
        ol.UnitPrice
    FROM Sales.Orders o
    INNER JOIN Sales.OrderLines ol ON ol.OrderID = o.OrderID
    WHERE o.CustomerID = @CustomerID
    ORDER BY o.OrderDate DESC
    OPTION (RECOMPILE);
END;

-- For standard customers
CREATE OR ALTER PROCEDURE Sales.usp_GetOrdersByStandardCustomer
    @CustomerID INT
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        c.CustomerName
    FROM Sales.Orders o
    INNER JOIN Sales.Customers c ON c.CustomerID = o.CustomerID
    WHERE o.CustomerID = @CustomerID
    ORDER BY o.OrderDate DESC;
END;

Fix 5: Parameter Sensitive Plan (PSP) Optimization — SQL Server 2022+

If you're on SQL Server 2022 or Azure SQL Database, you may not need to do anything at all. Microsoft introduced Parameter Sensitive Plan (PSP) Optimization, which allows the engine to automatically cache multiple execution plans for the same query — one for each distinct "bucket" of parameter values.

Enable it at the database level (requires compatibility level 160):

ALTER DATABASE WideWorldImporters
SET COMPATIBILITY_LEVEL = 160;

With PSP enabled, SQL Server can store a seek plan for small customers and a scan plan for large ones — automatically. It won't solve every sniffing problem, but it closes many of the most common gaps without developer intervention.

Tips and Common Mistakes

❌ Mistake 1: Using local variables to "hide" parameters

You'll often see developers do this:

CREATE PROCEDURE Sales.usp_GetStockItems_Bad
    @SupplierID INT
AS
BEGIN
    DECLARE @LocalSupplierID INT = @SupplierID; -- Hides the value
    SELECT StockItemID, StockItemName, RecommendedRetailPrice
    FROM Warehouse.StockItems
    WHERE SupplierID = @LocalSupplierID;
END;

While this does prevent sniffing, it replaces a bad sniffed plan with an even worse generic estimate plan. The optimizer has no information about the value, so it guesses using average statistics. This often performs worse than the sniffed plan, not better.

✅ Better approach: Use OPTION (OPTIMIZE FOR UNKNOWN) explicitly instead — it achieves the same effect but is cleaner and more maintainable.

❌ Mistake 2: Clearing the plan cache in production

-- NEVER do this on a live system!
DBCC FREEPROCCACHE;

This evicts every cached plan for the entire server — causing a massive CPU spike as hundreds of queries recompile simultaneously. If you need to clear a single plan, target it:

-- Get the plan handle first, then remove just that plan
DBCC FREEPROCCACHE (0x060005001A3B4C2...);

✅ Tip: Always test with realistic data distributions

Parameter sniffing problems often hide in development environments where data is clean and uniform. Always test stored procedures against a production-representative dataset — especially for tables like Sales.Orders, Sales.OrderLines, and Warehouse.StockItemHoldings where volume varies enormously by customer or item.

✅ Tip: Use Query Store to catch regressions early

SQL Server's Query Store (2016+) tracks execution plans over time and flags plan regressions. Enable it and set a review cadence:

ALTER DATABASE WideWorldImporters
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    MAX_STORAGE_SIZE_MB = 1024
);

Summary / Key Takeaways

  • Parameter sniffing happens because SQL Server compiles and caches an execution plan based on the first parameter values it sees, then reuses that plan for all future calls.
  • It's a feature, not a bug — plan reuse saves CPU. It only becomes a problem when the cached plan is a poor fit for other parameter values.
  • Signs of bad sniffing: A stored procedure that runs fast sometimes, slow other times, with no schema changes between runs.
  • Fix options (from least to most disruptive):
    1. OPTION (RECOMPILE) — best for infrequent or highly variable queries
    2. OPTION (OPTIMIZE FOR UNKNOWN) — consistent middle-ground plans
    3. OPTION (OPTIMIZE FOR value) — optimize for the typical case
    4. Separate procedures per scenario — most control, most maintenance
    5. PSP Optimization (SQL Server 2022+) — automatic multi-plan caching
  • Avoid the local variable trick — it trades bad sniffing for worse generic estimates.
  • Always diagnose before you fix. Use sys.dm_exec_query_stats and Query Store to confirm the root cause.

FAQ

Q1: Is parameter sniffing always bad?

No — in most cases, parameter sniffing is actually helpful. When the first execution uses typical parameter values and the data distribution is reasonably uniform, the cached plan serves all callers well. Sniffing only causes problems when there's significant skew in data distribution and the "first caller" used an atypical value that locked in an unrepresentative plan.

Q2: Will adding an index fix parameter sniffing?

Not directly. Adding an index may improve the performance of a poorly-sniffed plan by giving the optimizer more options, but it doesn't address the root cause — the wrong plan being reused. In some cases, a new index actually makes sniffing worse because it introduces more plan choices that can go in the wrong direction for certain parameter values.

Q3: How do I know which fix to use?

Start with the least invasive option. If the procedure runs infrequently (less than a few times per minute), OPTION (RECOMPILE) is simple and effective. For high-frequency procedures, OPTIMIZE FOR UNKNOWN or splitting the logic is safer. On SQL Server 2022, enable PSP optimization first and see if the problem resolves before adding query hints manually.

Tags: