По мотивам видео — Removing Query Hints with Plan Guides
Если какое-то приложение было спроектировано с использованием хинтов, которые при росте БД стали негативно влиять на работу приложения и у вас нет возможности исправить эти запросы, то данная статья для вас.
Часто бывает так, что хинты помогают решить некоторую проблему, но спустя какое-то время проблема возвращается и хинты начинают мешать выполнению.
Начнём
Самым верным решением будет изменение запроса. Но помните, что менять запросы без ведома разработчиков плохая идея, они обязательно должны включить изменения в проект.
К сожалению, часто бывает так, что у нас нет возможности изменить код приложения и тогда нам на помощь приходят plan guides. Plan guide позволяет добавлять хинты к запросам и фиксировать план выполнения. Но как же plan guide поможет нам убрать хинты из запроса? Дело в том, что если мы создадим plan guide для запроса и передадим вместо параметра @hints значение NULL, то plan guide исключит все хинты из это запроса.
Кто не работал с plan guide может испытывать некоторые трудности, давайте рассмотрим их подробнее:
- Когда мы получаем текст параметризированного запроса из кэша, то он выглядит не совсем очевидно:
(@Country_region int) SELECT * FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country_region OPTION (MAXDOP 1)
Для многих может показаться странным указание параметра вначале запроса, но SQL Server именно так хранит параметры и это надо учитывать.
- Следующий момент заключается в том, что неравенство SQL Server в кэше то же хранит иначе:
(@Country_region int) SELECT * FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode < @Country_region OPTION (MAXDOP 1)
Обратите внимание, что вместо < SQL Server использует аббревиатуру < и для нас это то же является проблемой
- Теперь перейдём к самому процессу создания plan guide. Вот верный код создания plan guide для нашего запроса:
sp_create_plan_guide @name = N'my_plan_guide', @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode < @Country_region OPTION (MAXDOP 1)', @type = N'SQL', @module_or_batch = NULL, @params = N'@Country_region int', @hints = NULL;
Вам следует обратить внимание на:
- Указание параметра из начала запроса перекочевало в @params, так же были удалены скобки
- аббревиатуру < необходимо заменить соответствующим символом <
- Как мы и обсуждали ранее, параметр @hints установлен в NULL, чтобы обнулить все хинты в указанном запросе
- Далее нужно понимать, что если plan guide создался без ошибок, это не говорит нам что их там нет и нужно обязательно выполнить валидацию созданного plan guide:
SELECT plan_guide_id, name, severity, state, message FROM sys.plan_guides CROSS APPLY fn_validate_plan_guide(plan_guide_id);
Если поля severity, state, message вернули NULL, то plan guide прошёл валидацию, но это ещё не означает, что он будет использоваться, это только означает что в нём нет ошибок.
В нашем запросе мы использовали хинт, который запрещает использовать параллелизм для данного запроса (MAXDOP 1). После создания plan guide, наш запрос сможет выполняться параллельно.
Заключение
Кроме всех нюансов использования, следует обратить внимание на:
- В нашем примере мы создали plan guide для запроса, но так же можно создать его и для процедуры, но следует учитывать что после создания plan guide для процедуры, будет невозможным её удаление.
- Plan guide может быть существенным влиянием на систему и необходимо создавать и поддерживать plan guide на всех средах
- Чтобы блокировка процедуры не мешала процессу обновления, рекомендуется удалять plan guide на время выполнения и создавать их заново после
Теперь у вас есть ещё один инструмент влияния на код, который нельзя изменить.
Один комментарий на «Отключение хинтов в запросе без модификации кода»