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

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

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

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

вы могли бы также использовать С перекомпиляция на оригинальном sproc или использовал оптимизация опция на параметре.

простой способ ускорить это-переназначить входные параметры локальным параметрам в самом начале 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
• копирование значений параметров в локальные переменные внутри хранимой процедуры и использование локальных значений в запросах.

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

Мне все еще нужно провести дополнительные исследования о том, как 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 и вы должны получить более быстрые результаты.