Performance Tuning • Real-World DBA

Parameter Sniffing 101: Why Your Fast Stored Procedure Suddenly Slows Down

By SQL Server Blog • Read Time: 5 mins

It’s 9:00 AM on a Monday. Your monitoring dashboard lights up red, CPU usage hits 98%, and users report that a core feature is completely hanging. You trace the bottleneck to a stored procedure that has run flawlessly in 15 milliseconds for months—except now it takes 45 seconds to complete.

You open SQL Server Management Studio (SSMS), execute the exact same stored procedure with the exact same parameter, and it returns instantly. Sound familiar? In 9 out of 10 cases, you are dealing with SQL Server’s double-edged sword: Parameter Sniffing.

1. What Actually Happens Behind the Scenes?

When a parameterized stored procedure is compiled for the first time, SQL Server "sniffs" the input parameters to look up distribution statistics and build an optimal execution plan. That cached execution plan is then stored in memory to save CPU time on future calls.

The Skewed Data Trap:

Imagine a GetCustomerOrders procedure. Customer A has 2 orders (requires an Index Seek). Customer B is an enterprise account with 500,000 orders (requires an Index Scan / Parallel Hash Join).

If Customer A triggers the initial compilation, SQL Server caches a plan optimized for 2 rows. When Customer B requests data next, SQL Server forces Customer B to use Customer A's plan—causing a massive performance crash.

2. Reproducing the Setup

Consider this simple, standard stored procedure:

CREATE PROCEDURE dbo.GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderID, OrderDate, TotalAmount
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID;
END;
GO

3. Practical Solutions: How to Fix It

Option A: Statement-Level Recompile (For Low-Frequency / High-Variance Queries)

Adding OPTION (RECOMPILE) instructs the engine to build a fresh, tailored execution plan on every run without clearing the entire procedure cache.

SELECT OrderID, OrderDate, TotalAmount
FROM Sales.Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);

Option B: Optimize for Average Density (Using Local Variables or UNKNOWN)

Telling the optimizer to OPTIMIZE FOR (@CustomerID UNKNOWN) forces SQL Server to use standard statistical averages rather than sniffing a specific value.

SELECT OrderID, OrderDate, TotalAmount
FROM Sales.Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

Option C: Plan Forcing via Query Store (Zero Code Changes)

If you are on SQL Server 2016 or newer and cannot alter application code, open Query Store, find the query ID, compare historical execution plans, and manually force the known-good plan with a single click.

4. Decision Matrix

Scenario	Recommended Action
Runs a few times/minute with wild data variance	OPTION (RECOMPILE)
Executed 1,000s of times/sec (CPU sensitive)	OPTIMIZE FOR UNKNOWN
Vendor Code / No permission to modify SQL	Force Plan in Query Store

```