MS SQL 2005, Parameter Sniffing, inhibiting the Stored Procedure
Recently I ran into a problem: the script launched in Query Analyzer worked out in a second, and it was already in the form of a stored procedure for 50 seconds. It turned out to be the fault of Parameter Sniffing, which was designed to ... optimize the request. :)
SQL server, when compiling the storage, creates an Execution Plan, where it assumes that the input parameters of the procedure will be of a certain kind. This is called Parameter Sniffing.
When the parameters turn out to be slightly different, then the Execution Plan may be somewhat inappropriate for the optimal execution of the request.
The easiest way to solve the problem is to disable Parameter Sniffing. This is done as follows: local variables are created in the body of the procedure and the values of the input parameters are assigned to them.
You can also use optimization options (OPTION (OPTIMIZE FOR ...)), specify when creating the procedure that you need to recompile the procedure (WITH RECOMPILE) each time.
But these options did not help me.
There is also a Decision Tree SP method, but it’s already completely monstrous.
ps: I am sure that this is just a crutch, and not the right solution to the problem, but that’s all that sufficed me as a programmer. Perhaps DBA will advise here the best way?
SQL server, when compiling the storage, creates an Execution Plan, where it assumes that the input parameters of the procedure will be of a certain kind. This is called Parameter Sniffing.
When the parameters turn out to be slightly different, then the Execution Plan may be somewhat inappropriate for the optimal execution of the request.
The easiest way to solve the problem is to disable Parameter Sniffing. This is done as follows: local variables are created in the body of the procedure and the values of the input parameters are assigned to them.
ALTER PROCEDURE [dbo].[REPORT_USERS_BRANCHES]
@branchId INT,
@branchName NVARCHAR(100) OUTPUT,
@filterByPaymentActivity VARCHAR(50),
@dateFrom DATETIME,
@dateTo DATETIME
AS
DECLARE @dtFrom DATETIME, @dtTo DATETIME, @filter VARCHAR(50), @bId INT
SET @bId = @branchId
SET @dtFrom = @DateFrom
SET @dtTo = @DateTo
SET @filter = @filterByPaymentActivity
...
* This source code was highlighted with Source Code Highlighter.
You can also use optimization options (OPTION (OPTIMIZE FOR ...)), specify when creating the procedure that you need to recompile the procedure (WITH RECOMPILE) each time.
But these options did not help me.
There is also a Decision Tree SP method, but it’s already completely monstrous.
ps: I am sure that this is just a crutch, and not the right solution to the problem, but that’s all that sufficed me as a programmer. Perhaps DBA will advise here the best way?