Pull to refresh

MS SQL 2005, Parameter Sniffing, тормозящий Stored Procedure

Reading time 1 min
Views 6.4K
Недавно столкнулся с проблемой: скрипт запущенный в Query Analyzer отрабатывал за секунду, а он же в виде хранимой процедуры аж 50 секунд. Оказалось всему виной Parameter Sniffing который призван… оптимизировать запрос. :)

SQL сервер при компиляции хранимки создает Execution Plan, где предполагает, что входные параметры процедуры будут определенного вида. Это и называется Parameter Sniffing.
Когда же параметры оказываются несколько иными, то Execution Plan может оказаться несколько неподходящим для оптимального выполнения запроса.

Наиболее простой метод решения проблемы — отключить Parameter Sniffing. Это делается следующим образом: создаются локальные переменные в теле процедуры и им присваиваются значения входных параметров.

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.


Так же можно использовать опции оптимизации(OPTION(OPTIMIZE FOR ...)), указывать при создании процедуры, что нужно каждый раз рекомпилить процедуру (WITH RECOMPILE).
Но эти варианты мне не помогли.
Есть еще метод с деревом решений (Decision Tree SP), но это уже совсем монструозно.

п.с.: уверен, что это всего лишь костыль, а не правильное решение проблемы, но это все на что хватило меня как программиста. Возможно DBA посоветуют тут лучший путь?
Tags:
Hubs:
+1
Comments 1
Comments Comments 1

Articles