SQL-Ex blog

Итак, что такое parameter sniffing?

Давайте начнем с таблицы dbo.CoffeeInventory, которую вы можете взять на Github.



Об этой таблице нужно знать следующее:

1. Имеется некластеризованный индекс на столбце Name.


2. Данные не распределены равномерно (это легко увидеть).

Теперь давайте напишем хранимую процедуру, которая возвращает отфильтрованный по стране список кофе в нашей таблице. Поскольку здесь нет отдельного столбца Country, мы нашишем её так, чтобы фильтрация выполнялась по столбцу Name:

DROP PROCEDURE IF EXISTS dbo.FilterCoffee

GO

CREATE PROCEDURE dbo.FilterCoffee

@ParmCountry varchar(30)

AS

BEGIN

 SELECT Name, Price, Description 

 FROM Sandbox.dbo.CoffeeInventory

 WHERE Name LIKE @ParmCountry + '%'

END

GO

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

EXEC dbo.FilterCoffee @ParmCountry = 'Costa Rica'

EXEC dbo.FilterCoffee @ParmCountry = 'Ethiopia'

Выполнение вышеприведенных запросов даст нам идентичные планы выполнения, использующие сканирование таблицы:



Это странно. Мы дважды выполнили запрос, был использован один и тот же план, и ни один из них не использовал наш некластеризованный индекс на столбце Name!

Вернемся на шаг назад и попробуем снова. Сначала почистим кэш планов запросов для этой хранимой процедуры:

DECLARE @cache_plan_handle varbinary(44)

SELECT @cache_plan_handle = c.plan_handle

FROM 

 sys.dm_exec_cached_plans c

 CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t

WHERE 

 text like 'CREATE%CoffeeInventory%' 

-- Никогда не выполняйте DBCC FREEPROCCACHE без параметра, 

-- если вы не хотите потерять все ваши кэшированные планы

DBCC FREEPROCCACHE(@cache_plan_handle)

Затем выполним те же хранимые процедуры с теми же параметрами, но теперь сначала используем параметр со значением ‘Ethiopia’. Взгляните на план выполнения:

EXEC dbo.FilterCoffee @ParmCountry = 'Ethiopia'

EXEC dbo.FilterCoffee @ParmCountry = 'Costa Rica'

Теперь наш некластеризованный индекс на Name уже используется. Оба запроса по прежнему получают один и тот же (хотя и другой) план.

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

Что, черт возьми, здесь происходит!?

Это пример прослушивания параметра. Сначала хранимая процедура (или запрос) запускается на SQL Server, сервер будет генерировать план выполнения для неё и сохранять этот план в кэше планов запросов:

SELECT

 c.usecounts,

 c.cacheobjtype,

 c.objtype,

 c.plan_handle,

 c.size_in_bytes,

 d.name,

 t.text,

 p.query_plan

FROM 

 sys.dm_exec_cached_plans c

 CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t

 CROSS APPLY sys.dm_exec_query_plan(c.plan_handle) p

 INNER JOIN sys.databases d

 ON t.dbid = d.database_id

WHERE 

 text like 'CREATE%CoffeeInventory%'

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

Замечание: запрос с различными значениями переданных параметров по-прежнему считается "тем же запросом" в глазах SQL Server.

В случае приведенного выше примера сначала первым выполнялся запрос с параметром “Costa Rica”. Помните, я говорил о сильном перекосе множества данных? Давайте посмотрим на количества:

SELECT 

  LEFT(Name,CHARINDEX(' ',Name)) AS Country, 

  COUNT(*) AS CountryCount 

FROM dbo.CoffeeInventory 

GROUP BY 

  LEFT(Name,CHARINDEX(' ',Name))

“Costa Rica” содержится в более чем 10000 строках этой таблицы, в то время как все другие страны присутствуют лишь единицами.

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

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

Однако, поскольку план для Costa Rica был получен первым, именно он попал в кэш планов запросов, и все остальные выполнения использовали тот же самый план со сканированием таблицы.

После очистки кэша от нашего плана выполнения с помощью DBCC FREEPROCCACHE мы снова выполнили нашу хранимую процедуру, но теперь с ‘Ethiopia’ в качестве параметра. SQL Server определил, что план с поиском в индексе является оптимальым для получения только 6 строк из таблицы с 10052 строками. Затем он закэшировал этот план с Index Seek, вот почему в следующий раз с параметром ‘Costa Rica’ был получен план выполнения с Index Seek.

Хорошо, а как мне избежать прослушивания параметра?

Этот вопрос на самом деле должен звучать так: "Как мне предотвратить использование в SQL Server не самого оптимального плана из кэша планов запросов?"

Использование WITH RECOMPILE или OPTION (RECOMPILE)

Мы можем просто добавить эти хинты запроса в любой из наших операторов EXEC:

EXEC dbo.FilterCoffee @ParmCountry = 'Ethiopia' WITH RECOMPILE

EXEC dbo.FilterCoffee @ParmCountry = 'Costa Rica' WITH RECOMPILE

или в саму хранимую процедуру:

DROP PROCEDURE IF EXISTS dbo.FilterCoffee

GO

CREATE PROCEDURE dbo.FilterCoffee

@ParmCountry varchar(30)

AS

BEGIN

 SELECT Name, Price, Description 

 FROM Sandbox.dbo.CoffeeInventory 

 WHERE Name LIKE @ParmCountry + '%'

 OPTION (RECOMPILE)

END

GO

Хинт RECOMPILE заставляет SQL Server генерировать новый план выполнения всякий раз, когда выполняются эти запросы.

Использование RECOMPILE устраняет нашу проблему прослушивания параметров, поскольку SQL Server будет заново генерировать план запроса всякий раз, когда мы выполняем запрос.

Недостатком является то, что мы теряем всю экономию времени процессора, которую обеспечивал SQL Server, кэшируя планы выполнения запросов.

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

Если ваш запрос с прослушиванием параметра не будет выполняться часто или если запрос выполняется недостаточно часто, чтобы план постоянно оставался в кэше, то RECOMPILE - хорошее решение.

Использование OPTIMIZE для хинта запроса

Другой способ, который у нас имеется, это добавить в запрос любой из следующих хинтов. Один из них должен быть добавлен в то же место, что и OPTION (RECOMPILE) в вышеупомянутой хранимой процедуре:

OPTION (OPTIMIZE FOR (@ParmCountry UNKNOWN))

или

OPTION (OPTIMIZE FOR (@ParmCountry = 'Ethiopia'))

OPTIMIZE FOR UNKNOWN будет использовать план, который генерируется на основании средней статистики распределения для этого столбца/индекса. Зачастую это приводит к среднему или плохому плану, поэтому я не люблю использовать этот хинт.

OPTIMIZE FOR ЗНАЧЕНИЕ создает план, использующий любое указанное значение параметра. Это замечательно, если вы знаете, что ваши запросы будут возвращать данные, которые оптимизированы относительно указанного значения, большую часть времени.

В наших примерах, если мы знаем, что значение ‘Costa Rica’ редко запрашивается, мы можем оптимизировать поиск в индексе. Тогда большинство запросов будут запускать оптимальный план запроса из кэша, и только запрос ‘Costa Rica’ будет вызывать проблемы.

IF/ELSE

Это решение обеспечивает максимальную гибкость. В основном вы создаете различные хранимые процедуры, которые оптимизированы для различных значений. Эти хранимые процедуры имеют свои кэшированные планы, а затем оператор IF/ELSE определяет, какую процедуру выполнить для переданного параметра:

DROP PROCEDURE IF EXISTS dbo.FilterCoffee

GO

CREATE PROCEDURE dbo.FilterCoffee

@ParmCountry varchar(30)

AS

BEGIN

 IF @ParmCountry = 'Costa Rica'

 BEGIN

  EXEC dbo.ScanningStoredProcedure @ParmCountry

 END

 ELSE

 BEGIN

  EXEC dbo.SeekingStoredProcedure @ParmCountry

 END

END

GO

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

Заключение

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


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


3. Использование хинта RECOMPILE является хорошим решением, когда ваши запросы не выполняются очень часто или не находятся в кэше запросов большую часть времени.


4. Хинт OPTIMIZE FOR хорош для использования, когда вы можете задать значение, которое будет генерировать план, эффективный для большинства значений параметра и хорошо справляется с неоптимальным планом по нечасто запрашиваемым значениям.


5. Использование сложной логики (типа IF/ELSE) даст вам максимальную гибкость и производительность, но может стать худшим вариантом в долгосрочной перспективе.