Mastering Parameter Sniffing in SQL Server

When it comes to SQL Server performance issues, parameter sniffing is one of those topics that both frustrates and fascinates. It’s often subtle, intermittently problematic, and notoriously tricky to explain to people who aren't knee deep in query plans all day.

What is Parameter Sniffing?

Parameter sniffing occurs when SQL Server compiles a query plan using the parameter values provided the first time a stored procedure or parameterised query runs. That plan is then reused for subsequent executions - even when the parameters change - under the assumption that the plan remains optimal.

But that assumption often fails.

For example, if the first execution receives a very selective value (returning only a few rows), SQL Server may generate a plan using a seek strategy. If the next execution has a non selective value (returning thousands of rows), that same seek based plan might be wildly inefficient.

Example:

Imagine a table Customers with a column Country.

CREATE PROCEDURE GetCustomersByCountry
    @CountryCode NVARCHAR(3)
AS
BEGIN
    SELECT *
    FROM Customers
    WHERE Country = @CountryCode;
END

Let’s say the first execution is:

EXEC GetCustomersByCountry @CountryCode = 'IS';

Iceland might have 12 customers. SQL Server compiles a plan using an Index Seek strategy.

Later:

EXEC GetCustomersByCountry @CountryCode = 'US';

The United States might have 100,000+ customers - but SQL Server still uses the plan optimised for 'IS', leading to massive logical reads and a very unhappy user.

How to Detect Parameter Sniffing

1. Compare Execution Times

Run the same stored procedure with different parameters and see if performance fluctuates wildly.

2. Examine Execution Plans

Use SQL Server Management Studio (SSMS) to get the Actual Execution Plan. Look for differences in:

• Estimated vs Actual Rows

• Index usage (Seek vs Scan)

• Plan reuse indicators

3. Query Plan Cache

Use sys.dm_exec_cached_plans and related DMVs to inspect cached plans:

SELECT  cp.plan_handle, qp.query_plan, st.text
FROM    sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE   st.text LIKE '%GetCustomersByCountry%';

Look for a plan that doesn’t match your current parameter’s ideal strategy.

Solutions: Fixing or Avoiding Parameter Sniffing

1. OPTION (RECOMPILE)

Force SQL Server to recompile the query every time, ensuring the plan is always tailored to the current parameter:

SELECT *
FROM Customers
WHERE Country = @CountryCode
OPTION (RECOMPILE);

Pros: Best performance for every execution.

Cons: Increased CPU usage due to frequent recompilation.

2. Dynamic SQL

Use EXEC to build SQL dynamically inside a stored procedure:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM Customers WHERE Country = @CountryCode';
EXEC sp_executesql @sql, N'@CountryCode NVARCHAR(3)', @CountryCode;

Pros: Fresh plan per execution.

Cons: Harder to maintain, limited plan reuse.

3. OPTIMIZE FOR hint

Tell SQL Server to always compile for a specific parameter:

SELECT *
FROM Customers
WHERE Country = @CountryCode
OPTION (OPTIMIZE FOR (@CountryCode = 'GB'));

Pros: Consistent plan.

Cons: Might not be optimal for all cases — can backfire if 'GB' isn't representative.

4. Multiple Procedures

Split the procedure into specialised versions:

-- GetCustomers_US
-- GetCustomers_Others

Choose which to call in app code based on logic.

Pros: Clear intent and isolation.

Cons: More code to maintain.

5. Plan Guides or Forced Plans (Query Store)

Use Query Store to lock in a known good plan:

-- View plans
SELECT * FROM sys.query_store_query_text;
-- Force a plan
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;

Pros: Keeps control in DBA hands.

Cons: Needs monitoring — forced plans can grow stale as data changes.

---

When to Use What?

---

Detecting Parameter Sensitivity with Query Store

In SQL Server 2022+, Query Store Hints and Parameter Sensitive Plan (PSP) detection are now part of the engine.

Look out for:

• Multiple plans for one query in Query Store

• Plan IDs tagged with different parameter values

If your SQL Server is newer and PSP optimisation is enabled, SQL Server may auto correct parameter sniffing with multiple plan variants. Still, being proactive with hints or better design remains a good practice.

Parameter sniffing isn’t a bug - it’s a performance optimisation feature that can become a liability under the wrong conditions. The key is knowing when it's hurting you, and applying the right fix based on your query shape and system constraints.