T-SQL Best Practices - Parameter Sniffing

Tsql теория > T-SQL Best Practices - Parameter Sniffing
02.04.2013 18:12:20



Статья:

T-SQL Best Practices - Parameter Sniffing

T-SQL Лучшие Методы - Сниффинг Параметра

В этой статье я буду исследовать, как SQL Server оптимизирует и планы выполнения хранимой процедуры кэшей, основанные на параметрах, которые передают в первый раз, когда хранимая процедура (SP) выполняется. Это обсуждение исследует понятие, вызванное “Сниффинг Параметра” и как это может привести к планам выполнения, которые не оптимальны для всех вызовах в SP.

What is Parameter Sniffing?

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

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

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

Рассмотрим проблему, Вызванную Сниффингом Параметра

Прежде, чем мы изучим, как записать Ваши SP, чтобы управлять проблемами, связанными со сниффингом параметра, давайте смотреть на проблемы, вызванные им сначала. Сделать, которые позволяют нам рассматривать следующий код

USE AdventureWorks
GO
CREATE PROC GetCustOrders (@FirstCust int, @LastCust int) 
AS 
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID between @FirstCust and @LastCust;

Этот код принимает два параметра. Эти два параметра используются в, WHERE, чтобы определить, который SalesOrderHeader записывает к возврату. Теперь, давайте смотреть на план выполнения относительно этого SP, когда мы вызываем это с двумя различными наборами параметров. Во-первых, давайте вызывать это со следующим кодом:

USE AdventureWorks
GO
DBCC FREEPROCCACHE
EXEC GetCustOrders 1,1000

Когда я выполняю вышеупомянутый код, я получаю следующий план выполнения:

execution plan

Здесь, можно видеть, что это выполнение SP GetCustomerOrder выполняло работу Сканирования Кластерного индекса, когда я вызывал эту процедуру с диапазоном CustomerID от 1 до 1000. Отметьте, я освободил кэш процедур, используя оператор “DBCC FREEPROCCACHE”, чтобы удостовериться, что оптимизатор запросов скомпилировал SP GetCustOrders, основанный на параметрах, которые я передал.

Теперь, я буду выполнять тот же самый SP с меньшим диапазоном CustomerID и видеть, какой план выполнения я получаю. Вот код, который я буду выполнять:

USE AdventureWorks
GO
DBCC FREEPROCCACHE
EXEC GetCustOrders 600,610

Когда я выполняю этот код, я получаю следующий план выполнения:

execution plan

На сей раз я получаю различный план выполнения. Теперь, можно видеть, что при использовании меньшего диапазона CustomerID (600-610), мы имеем , Index Seek operation. В зависимости от которого выполнение этого SP было первым, чтобы быть, выполняют это, скомпилировал бы и кэшировал бы план выполнения относительно всего последующего выполнения этого SP. Чтобы проверить это позволяют нам выполнять следующий код и рассматривать планы выполнения:

USE AdventureWorks
GO
DBCC FREEPROCCACHE
EXEC GetCustOrders 1,1000
GO
EXEC GetCustOrders 600,610

Вот план выполнения относительно вышеупомянутого пакета T-SQL:

exectuion plan for T-SQL batch

Смотря на этот код, можно видеть, что второе выполнение SP GetCustOrder теперь выполняет работу Сканирования Кластерного индекса, чтобы найти все потребительские заказы от маленького диапазона CustomerID. Это происходит, потому что оптимизатор запросов только вдыхал параметры для первого выполнения GetCustOrder, который имел большой спектр, и затем кэшировал тот план выполнения. Кэшируемый план выполнения тогда использовался для второго выполнения SP GetCustOrder.

Первая компиляция SP создает план выполнения, основанный на параметрах, которые передают, и затем этот план сохранен в кэше процедур для тока и всего будущего выполнения того же самого SP. Последний сегмент кода выше демонстрируемого, как это могло заставить наше второе выполнение GetCustOrder использовать менее эффективный план выполнения - в этом случае, работа Сканирования Кластерного индекса вместо Index Seek operation. Давайте смотреть на способы преодолеть эту проблему.

Устранение проблемы Сниффинга Параметра, Отключая Сниффинг Параметра

В предшествующем примере я демонстрировал, как первое выполнение SP принимало параметры и затем создавало план выполнения, который был оптимизирован для того набора параметров. Это может привести к плохим планам выполнения относительно любого вызова, что использование SP, у которого есть различный набор параметров, которые могли бы привести к различному плану выполнения. На моих демонстрациях выше я показал, что, когда большой спектр CustomerID (1-1000) был отправлен SP GetCustOrder, это использовало индексную работу сканирования, но если маленький диапазон (600-610) был отправлен, оптимальный план был индексом, ищут работу. Однако, потому что SQL Server пытается минимизировать компиляции SP, мой маленький диапазон customerID’s, используемого индексная работа сканирования. Если у Вас есть широкое изменение в параметрах, которые можно было бы передать к SP, можно устранить проблемы сниффинга параметра, отключая сниффинг параметра. Вы делаете это, кодируя Ваш SP особенный метод. Ниже я переписал свой SP, чтобы устранить сниффинг параметров:

CREATE PROC GetCustOrders (@FirstCust int, @LastCust int) 
AS
DECLARE @FC int
DECLARE @LC int
SET @FC = @FirstCust
SET @LC = @LastCust
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN @FC AND @LC

Здесь, может видеть, что я объявил две локальных переменные (@FC и @LC) и затем заполнил их со значениями параметров, которые передают к моему SP. Делая это, фактические значения параметров больше не содержатся в BETWEEN пунктом в операторе SELECT, вместо этого только те локальные переменные присутствуют. Из-за этой мелочи оптимизатор запросов смотрит на всю статистику, связанную с объектами в моем запросе, и определяет в среднем, что могло бы быть лучшим планом выполнения, чтобы использовать основанный на статистике.
Однако, этот метод устранения проблемы сниффинга параметра не означает, что Вы получите оптимальный план относительно каждого выполнения SP. Вы все еще только сохранили один план выполнения в кэше процедур, который будет использоваться для всего выполнения SP. Хотя тот один план выполнения в среднем выполнит оптимальный, если Вы вызовете SP много раз со многими различными значениями параметра. Если Вы действительно будете хотеть создать различные планы выполнения, основанные на параметрах, которые передают тогда, то Вы будете нуждаться к использованию в другом подходе..

Разрешение проблемы Сниффинга Параметра Используя SP Дерева решений

Если у Вас есть SP, который вызывают со многими различными значениями параметра, где в зависимости от параметров, которые передают, они получают различные планы выполнения, можно решить проблему сниффинга параметра, создавая SP дерева решений. У подхода дерева решений есть единственный SP, который вызывают, который решает, который передал SP вызвать основанный на параметрах. Это позволяет больше чем одному SP поддерживать Ваши переменные значения параметра и учитывает больше, оптимизируют план, который будет использоваться основанные на параметрах, которые передают. Позвольте мне показывать Вам пример подхода дерева решений к разрешению проблемы сниффинга параметра.
Давайте использовать ту же самую ситуацию в качестве вышеупомянутых примеров, где я хочу возвратить записи SalesOrderHeader, основанные на диапазоне CustomerIDs. Вместо SP GetCustOrders, выбирающего записи заголовка заказа, это вместо этого определит различие в диапазоне и затем вызовет различный SP, основанный на том, является ли диапазон CustomerID маленьким или большим. Давайте смотреть на код для следующих трех SP:

CREATE PROC GetCustOrders (@FirstCust int, @LastCust int) 
AS 
IF @LastCust - @FirstCust < 100
	EXEC GetCustOrdersNarrow @FirstCust, @LastCust
ELSE
	EXEC GetCustOrdersWide @FirstCust, @LastCust 
GO
-- Proc for Large Range of Customers
CREATE PROC GetCustOrdersWide (@FirstCust int, @LastCust int) 
AS 
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN @FirstCust AND @LastCust  
GO
-- Proc for Small Range of Customers
CREATE PROC GetCustOrdersNarrow (@FirstCust int, @LastCust int) 
AS 
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN @FirstCust AND @LastCust  
GO

Здесь, можно видеть, что я создал три различных SP. Первый SP (GetCustOrders) является моим SP дерева решений. Этот SP определяет, является ли диапазон CustomerIDs меньше чем 100. Если это, это вызывает GetCustOrdersNarrow иначе, это вызывает GetCustOrdersWide. Эти два SP являются точно тем же самым. Я выполню следующий код, чтобы определить, какие планы выполнения сгенерированы основанные на переданном использовании параметров этого кода:

DBCC FREEPROCCACHE
EXEC GetCustOrders 1,1000
EXEC GetCustOrders 600,610

Вот два различных плана выполнения, произведенные двумя различными операторами EXEC:

two different execution plans produced by the two different EXEC statements

Как можно видеть, первое выполнение, которое сделало, чтобы CustomerID расположился больше чем 100, использовало работу Сканирования Кластерного индекса, чтобы разрешить ее выполнение. Принимая во внимание, что второе выполнение, у которого был диапазон CustomerID меньше чем 100, использовало Index Seek operation , сопровождаемую Ключевым Поиском, чтобы разрешить этот меньший запрос диапазона. Так, создавая подход дерева решений, каждое выполнение с различными параметрами было разрешено с более эффективным планом выполнения.

Controlling Parameter Sniffing

.Оптимизатор запросов использует сниффинг параметра, чтобы помочь определить, какой план выполнения должен использоваться, чтобы оптимизировать выполнение SP. Этот процесс, как можно видеть, когда-то заставляет Ваш SP выполнять план, который был оптимизирован основанный на различном наборе параметров из-за кэширования плана. Я демонстрировал способ отключить сниффинг параметра, таким образом, SQL Server использует статистику, чтобы определить в среднем, что было бы оптимальным планом. Кроме того, я показал Вам, как создать SP дерева решений, чтобы помочь удостовериться, что у Вас есть различные кэшируемые планы относительно различных диапазонов значений параметра. Если Вы находите, что Ваши запросы работают медленный иногда с различными значениями параметра тогда, Вы могли бы хотеть определить, вызывает ли сниффинг параметра Ваши проблемы производительности