Анализ параметров (или подмена) в SQL Server

К вашему сведению - вам нужно знать кое-что еще, когда вы работаете с SQL 2005 и храните процессы с параметрами.

SQL Server скомпилирует план выполнения хранимой процедуры с первым используемым параметром. Итак, если вы запустите это:

usp_QueryMyDataByState 'Rhode Island'

План выполнения лучше всего работает с данными небольшого штата. Но если кто-то обернется и убежит:

usp_QueryMyDataByState 'Texas'

План выполнения, разработанный для данных размера Род-Айленд, может быть не столь эффективным для данных размера Техаса. Это может привести к неожиданным результатам при перезапуске сервера, потому что вновь сгенерированный план выполнения будет нацелен на любой параметр, который используется первым, не обязательно лучший. План не будет перекомпилирован до тех пор, пока для этого не появится серьезная причина, например, если будет перестроена статистика.

Вот тут-то и появляются планы запросов, и SQL Server 2008 предлагает множество новых функций, которые помогают администраторам баз данных закрепить конкретный план запроса в долгосрочной перспективе, независимо от того, какие параметры вызываются в первую очередь.

Меня беспокоит то, что когда вы перестраивали сохраненную процедуру, вы заставляли план выполнения перекомпилировать. Вы вызвали его с вашим любимым параметром, и тогда, конечно, это было быстро, но проблема могла быть не в сохраненной процедуре. Возможно, сохраненная процедура была перекомпилирована в какой-то момент с необычным набором параметров и, следовательно, с неэффективным планом запроса. Возможно, вы ничего не исправили, и вы можете столкнуться с той же проблемой при следующем перезапуске сервера или перекомпиляции плана запроса.

Да, я думаю, вы имеете в виду анализ параметров, который используется оптимизатором SQL Server для определения значений / диапазонов параметров, чтобы он мог выбрать лучший план выполнения для вашего запроса. В некоторых случаях SQL Server плохо справляется с анализом параметров и не выбирает лучший план выполнения для запроса.

Я считаю, что эта статья в блоге http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx имеет хорошее объяснение.

Кажется, что администратор баз данных в вашем примере выбрал вариант № 4, чтобы переместить запрос в другой sproc в отдельный процедурный контекст.

Вы также могли использовать с перекомпиляцией в исходном sproc или использовать параметр optimize for для параметра.

Простой способ ускорить это - переназначить входные параметры локальным параметрам в самом начале sproc, например

CREATE PROCEDURE uspParameterSniffingAvoidance
    @SniffedFormalParameter int
AS
BEGIN

    DECLARE @SniffAvoidingLocalParameter int
    SET @SniffAvoidingLocalParameter = @SniffedFormalParameter

    --Work w/ @SniffAvoidingLocalParameter in sproc body 
    -- ...

По моему опыту, лучшим решением для сниффинга параметров является «Динамический SQL». Следует отметить две важные вещи: 1. вы должны использовать параметры в своем динамическом sql-запросе 2. вы должны использовать sp_executesql (а не sp_execute), который сохраняет план выполнения для каждого значения параметра.

Анализ параметров - это метод, который SQL Server использует для оптимизации плана выполнения запроса для хранимой процедуры. Когда вы впервые вызываете хранимую процедуру, SQL Server просматривает заданные значения параметров вашего вызова и решает, какие индексы использовать, на основе значений параметров.

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

Вы можете обойти это, либо

• используя WITH RECOMPILE
• копирование значений параметров в локальные переменные внутри хранимой процедуры и использование локальных переменных в ваших запросах.

Я даже слышал, что лучше вообще не использовать хранимые процедуры, а отправлять запросы прямо на сервер. Недавно я столкнулся с той же проблемой, решения которой у меня пока нет. Для некоторых запросов копирование в локальные вары помогает вернуться к правильному плану выполнения, для некоторых запросов производительность ухудшается из-за локальных варов.

Мне все еще нужно провести дополнительные исследования того, как SQL Server кэширует и повторно использует (неоптимальные) планы выполнения.

У меня была аналогичная проблема. План выполнения моей хранимой процедуры занял 30-40 секунд. Я попытался использовать операторы SP в окне запроса, и для их выполнения потребовалось несколько мс. Затем я разработал объявление локальных переменных внутри хранимой процедуры и перенос значений параметров в локальные переменные. Это сделало выполнение SP очень быстрым, и теперь тот же SP выполняется в течение нескольких миллисекунд вместо 30-40 секунд.

Очень простой и удобный оптимизатор запросов использует старый план запроса для часто выполняемых запросов. но на самом деле размер данных также увеличивается, поэтому в это время требуется новый оптимизированный план, и оптимизатор запросов по-прежнему использует старый план запроса. Это называется анализом параметров. Я также написал подробный пост по этому поводу. Посетите этот URL: http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/

Изменение процедуры хранения для выполнения в виде партии должно увеличить скорость.

Выбор пакетного файла, т.е .:

exec ('select * from order where  order id ='''+ @ordersID')

Вместо обычной хранимой процедуры выберите:

select * from order where  order id = @ordersID

Просто передайте параметр как nvarchar, и вы получите более быстрые результаты.