Сниффинг параметра (или спуфинг) в SQL Server
4 ответа
К вашему сведению - необходимо знать о чем-то еще, когда Вы работаете с SQL 2005 и сохраненный procs с параметрами.
SQL Server скомпилирует план выполнения сохраненного proc с первым параметром, это используется. Таким образом, если Вы выполняете это:
usp_QueryMyDataByState 'Rhode Island'
план выполнения будет работать лучше всего с данными маленького состояния. Но если кто-то оборачивается и работает:
usp_QueryMyDataByState 'Texas'
план выполнения, разработанный для данных род-айлендского размера, не может быть столь же эффективным с данными размера Техаса. Это может привести к неожиданным результатам, когда сервер перезапущен, потому что недавно сгенерированный план выполнения будет предназначен для любого параметра, используется сначала - не обязательно лучший. План не будет перекомпилирован, пока не будет большая причина сделать это, как то, если статистические данные восстановлены.
Это - то, где планы запросов входят, и SQL Server, который 2008 предлагает большому количеству новых возможностей, которые помогают DBAs прикрепить конкретный план запросов, на месте долгосрочный, неважно, какие параметры называют первыми.
Мое беспокойство - то, что при восстановлении сохраненного proc Вы вынудили план выполнения перекомпилировать. Вы назвали его со своим любимым параметром, и затем конечно, это было быстро - но проблемой не мог быть сохраненный proc. Возможно, случилось так, что сохраненный proc был перекомпилирован в какой-то момент с необычным набором параметров и таким образом, неэффективный план запросов. Вы, ничего возможно, не зафиксировали, и Вы могли бы столкнуться с той же проблемой в следующий раз, когда перезапуски сервера или план запросов перекомпилированы.
Простой способ убыстриться, который должен повторно присвоить входные параметры локальным параметрам в самом начале sproc, например,
CREATE PROCEDURE uspParameterSniffingAvoidance
@SniffedFormalParameter int
AS
BEGIN
DECLARE @SniffAvoidingLocalParameter int
SET @SniffAvoidingLocalParameter = @SniffedFormalParameter
--Work w/ @SniffAvoidingLocalParameter in sproc body
-- ...
Да, я думаю, что Вы имеете в виду сниффинг параметра, который является техникой использование оптимизатора SQL Server, чтобы попытаться выяснить значения/диапазоны параметра, таким образом, он может выбрать лучший план выполнения относительно Вашего запроса. В некоторых случаях SQL Server делает плохое задание в параметре, осуществляющем сниффинг & не выбирает лучший план выполнения относительно запроса.
я полагаю, что эта статья блога http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx имеет хорошее объяснение.
кажется, что DBA в Вашем примере выбрал опцию № 4 переместить запрос в другой sproc к отдельному процедурному контексту.
Вы, возможно, также использовали с, перекомпилировали на исходном sproc или использовал эти , оптимизируют для опция на параметре.
Сниффинг параметра является SQL Server техники использование для оптимизации плана выполнения запросов относительно хранимой процедуры. При первом вызове хранимой процедуры SQL Server смотрит на данные значения параметров вызова и решает который индексы использовать на основе значений параметров.
Поэтому, когда первый вызов содержит не очень типичные параметры, SQL Server мог бы выбрать и сохранить субоптимальный план выполнения в отношении следующих вызовов хранимой процедуры.
можно работать вокруг этого любым
- использование
WITH RECOMPILE - копирование значений параметров к локальным переменным в хранимой процедуре и использовании местных жителей в запросах.
я даже слышал, что лучше не использовать хранимые процедуры вообще, но отправить Ваши запросы непосредственно на сервер. Я недавно столкнулся с той же проблемой, где у меня еще нет действительного решения. Для некоторых запросов копия в локальный Вар помогает возвращению к правильному плану выполнения, поскольку некоторая производительность запросов ухудшается с локальным Варом.
я все еще должен провести больше исследования на как кэши SQL Server и повторные использования (субоптимальные) планы выполнения.