Недавно столкнулся с проблемой: скрипт запущенный в 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 посоветуют тут лучший путь?

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