SQL Server › Query Optimization & Plans

What is parameter sniffing, and how can it sometimes lead to unexpectedly poor query performance?

Intermediate

Answer

Parameter sniffing happens when SQL Server caches an execution plan based on the specific parameter values used the first time a query or stored procedure runs, and if a very different, less typical parameter value is used later, that same cached plan might be poorly suited for the new value, leading to unexpectedly slow performance for that particular execution.

-- A plan optimized for a common, small parameter value
-- might perform poorly when reused for a very different, large value
CREATE PROCEDURE GetOrdersByCustomer @CustomerId INT
AS
SELECT * FROM Orders WHERE CustomerId = @CustomerId;

Real-world example A stored procedure that runs quickly for most customers suddenly performs terribly for one specific large customer, and the team discovers this is caused by parameter sniffing, where the cached plan was optimized for a smaller, more typical customer's data volume.

Common follow-ups: What are some common techniques for mitigating parameter sniffing issues?;How do you force a stored procedure to recompile its execution plan?

Stored Procedures & Functions;Query Store