Parameter Sniffing

Parameter sniffing has a weird symptom which can take your hours to find out what actually is happening. The stored procedure you created is running on one side, but when you deploy it on another machine or server, it never ends executing despite the fact that everything is the same.

This problem usually occurs when the query contains LIKE condition. However, it is not limited to this case. Query without LIKE condition can also face this problem.

For example, here is the normal stored procedure:

CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS
SELECT *
FROM dbo.Table1
WHERE Column1 = @Parameter1
AND Column2 LIKE '%' + @Parameter2 + '%'

To avoid parameter sniffing, there is a need to create new variables to store the values fetched from the parameters.

CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS

DECLARE
@DummyParameter1 INT = @Parameter1,
@DummyParameter2 VARCHAR(200) = @Parameter2

SELECT *
FROM dbo.Table1
WHERE Column1 = @DummyParameter1
AND Column2 LIKE '%' + @DummyParameter2 + '%'

Read More