SQL Server Interview Question #139

What is parameter sniffing?

Indexes & Query Performance Senior Advanced

Detailed Explanation

Parameter sniffing occurs when SQL Server compiles a parameterized statement or stored procedure using parameter values available at compilation time and caches the resulting plan for reuse.

This is normally beneficial, but it can cause unstable performance when different parameter values require very different plans because of skewed data distributions.

Possible responses include improving indexes/statistics, rewriting the query, using OPTION (RECOMPILE) for appropriate cases, using OPTIMIZE FOR strategies, or applying newer SQL Server features such as Parameter Sensitive Plan optimization where available. The solution should be based on measured evidence rather than disabling plan reuse indiscriminately.

Code Example

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;