Параметр Sniffing (или Spoofing) в SQL Server

Некоторое время назад у меня был запрос, который я использовал довольно много для одного из моих пользователей. Он все еще эволюционировал и настраивался, но в итоге он был стабилизирован и работал довольно быстро, поэтому мы создали из него хранимую процедуру.

До сих пор такой нормальный.

Однако хранимая процедура была медленной. Нет существенной разницы между запросом и proc, но изменение скорости было массивным.

[Фон, мы запускаем SQL Server 2005.]

Дружественный локальный администратор баз данных (который больше не работает здесь) рассмотрел хранимую процедуру и сказал, что "спуфинг параметров!" ( Изменить:, хотя кажется, что он, возможно, также известен как "обнюхивание параметров", что может объяснить нехватку хитов Google, когда я пытался его найти.)

Мы отвлекли часть хранимой процедуры ко второму, завернули вызов этого нового внутреннего proc в ранее существовавший внешний, называемый внешним, и, hey presto, он был так же быстро, как исходный запрос.

Итак, что дает? Может ли кто-то объяснить параметризацию спуфинга?

Бонусный кредит для

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

Ответ 1

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

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

usp_QueryMyDataByState 'Rhode Island'

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

usp_QueryMyDataByState 'Texas'

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

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

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

Ответ 2

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

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

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

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

Ответ 3

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

CREATE PROCEDURE uspParameterSniffingAvoidance
    @SniffedFormalParameter int
AS
BEGIN

    DECLARE @SniffAvoidingLocalParameter int
    SET @SniffAvoidingLocalParameter = @SniffedFormalParameter

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

Ответ 4

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

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

Вы можете обойти это с помощью

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

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

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

Ответ 5

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

Ответ 6

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

Ответ 7

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

Ответ 8

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

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

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

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

select * from order where  order id = @ordersID

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