Есть ли способ преодоления перехвата параметров в SQL Server?

Question

Я наткнулся на анализ параметров, когда выполнение одного из моих запросов заняло гораздо больше времени, чем ожидалось. Когда я углубился в эту проблему, я узнал, что:

Когда первый раз запрос выполняется, он (SQL Server) создает план выполнения для этого запроса и для другого n раз, когда выполнялся один и тот же запрос, и если он имеет большие расхождения в наборе результатов с первым выполнением, тогда возникает проблема с анализом параметров ".

Это было в моем сценарии.

Теперь мой вопрос: есть ли способ или обходной путь для преодоления перехвата параметров в SQL Server в этих сценариях?

• Я знаю бегом sp_updatestats Я могу проверить, происходит это или нет.

• И также я знаю, чтобы уловить проблему, мне нужно контролировать кэш процедур, по query_hash а также query_plan_hash поля sys.dm_exec_query_stats Я могу сделать это.

• Я не хочу использовать RECOMPILE в SET переменная секция, поскольку это будет создавать новые планы выполнения каждый раз, когда выполняется запрос.

Но вместо того, чтобы проверять проблему, я хочу ее преодолеть, делая что-то в самом запросе, я имею в виду, например, "Обнаружение проблемы во время выполнения и создание нового плана выполнения только по мере необходимости (не каждый раз)".

Я часто сталкиваюсь с проблемой перехвата параметров, поэтому мы будем благодарны за каждое полезное предложение и помощь. Заранее спасибо!

Answer 1

Вы можете назначить параметры локальным переменным.

CREATE PROCEDURE SP_NAME

    @param1 INT,
    @param2 INT

AS
DECLARE @local_param1 INT
DECLARE @local_param2 INT

SET @local_param1  = @param1 
SET @local_param2 = @param2 ... 

Как поясняется в этом посте. Присвоение параметров локальным переменным указывает SQL Server использовать статические плотности вместо статических гистограмм, что позволяет избежать проблемы перехвата параметров.

Answer 2

Вы можете применить опцию OPTION(OPTIMIZE FOR UNKNOWN) к запросам, использующим параметры, чтобы запрос был оптимизирован для использования статистики, а не оптимизирован для конкретных параметров. Это обходит параметр сниффинг.

Это объясняется (кратко) в подсказках запросов для T-SQL:

Оптимизировать для неизвестных

Указывает оптимизатору запросов использовать статистические данные вместо начальных значений для всех локальных переменных при компиляции и оптимизации запроса, включая параметры, созданные с принудительной параметризацией.