📜 ⬆️ ⬇️

MS SQL 2005, Parameter Sniffing, Braking the Stored Procedure

Recently I ran into a problem: the script launched in the Query Analyzer was processed in a second, and it was already 50 seconds in the form of a stored procedure. It turned out all the fault of Parameter Sniffing which is designed to ... optimize the query. :)

When compiling the SQL server, the storage creates the Execution Plan, where it assumes that the input parameters of the procedure will be of a certain type. This is called Parameter Sniffing.
When the parameters are somewhat different, the Execution Plan may be somewhat unsuitable for optimal execution of the request.

The easiest way to solve a problem is to disable Parameter Sniffing. This is done as follows: local variables are created in the procedure body 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 a procedure that you need to recompile a procedure (WITH RECOMPILE) each time.
But these options did not help me.
There is also a method with a decision tree (Decision Tree SP), but this is completely monstrous.

ps: I am sure that this is just a crutch, but not the right solution to the problem, but this is all that was enough for me as a programmer. Perhaps the DBA will advise here the best way?

Source: https://habr.com/ru/post/109580/


All Articles