What exactly is “parameter sniffing”?
When a stored procedure is initially run, SQL Server examines the input parameters and builds the query plan with their help. This is referred to as “parameter sniffing.”
This is OK as long as the input parameters for the initial invocation are typical for consecutive invocations. However, if this is not the case. For instance, a method that uses a non-clustered index on the customer column is meant to return all entries for customer orders. It might be most effective to utilize index seek if the initial call only returns a limited number of orders. The initial cached plan with index seek will be utilized in subsequent invocations, even if they are for a high number of orders, resulting in subpar performance (instead of using a scan).
Here is an illustration of a stored process along with various approaches to handle parameter sniffing.
CREATE PROCEDURE GetCustomerOrders
@customerid NCHAR(5)
AS
BEGIN
SELECT orderid, customerid, orderdate, shippeddate
FROM Orders
WHERE customerid = @customerid;
END
Replace parameters with local variables
This method is based on providing stored procedure parameters to local variables, which are eventually used in the query. This works because SQL Server does not sniff local variables, and using local variables in place of parameters forces a plan based on statistics to be generated.
CREATE PROCEDURE GetCustomerOrders
@customerid NCHAR(5)
AS
BEGIN
DECLARE @local_customerid NCHAR(5);
SET @local_customerid = @customerid;
SELECT orderid, customerid, orderdate, shippeddate
FROM Orders
WHERE customerid = @local_customerid;
END
Execute using WITH RECOMPILE
This solution forces the stored procedure to be recompiled on each execution, resulting in a new query plan for the current parameters. It should be noted that this will recompile all statements within the stored method.
EXEC GetCustomerOrders @customerid = N'CACYK' WITH RECOMPILE;
Query hint RECOMPILE
