Бывают дни, когда я хочу бросить всё это с кэшем планов и Хранилищем запросов!

Автор: Brent Ozar, There Are Days When I Feel Like Giving Up on the Plan Cache and Query Store

В теории мониторинг производительности SQL Server довольно прост:

1. Изучите главные типы ожиданий (wait types) на сервере.
2. Найдите запросы, вызывающие эти типы ожиданий.
3. Исправьте эти запросы или улучшите реакцию сервера на них (индексы, настройки и т.д.).

Но на практике шаг 2 ужасен, потому что:

• Приложения отправляют на сервер баз данных непараметризованные строки.
• Пользователи Entity Framework строят запросы с FromSqlRaw или string.Format().
• Пользователи Entity Framework пишут запросы с .Contains, который создаёт непараметризованный список IN, даже когда ищут всего одно значение (в EF9 стало лучше).
• Люди пишут неаккуратный динамический SQL, который просто вставляет значения прямо в строку запроса.
• Разработчики SaaS-решений помещают каждого клиента в собственную базу данных, и планы не переиспользуются между базами данных.

В результате кэш планов и Хранилище запросов становятся практически бесполезными, потому что каждый поступающий запрос рассматривается как «новый» запрос. Я писал об этом ещё в 2018 году, и с тех пор проблема, кажется, становится всё хуже. За последние пару лет я отслеживал клиентские серверы, и сегодня каждый третий сервер, с которым я сталкиваюсь, имеет эти проблемы. Возможно, моя картина искажена, так как те, у кого нет этих проблем, решают свои вопросы с производительностью с помощью обычных инструментов вроде sp_BlitzCache и Хранилища запросов (Query Store), но даже если это так, проблема существует и она больше, чем просто устранение неполадок в запросах вручную.

Современные версии SQL Server всё больше зависят от правильно параметризованных запросов. Такие инструменты, как автоматическое исправление планов (Automatic Plan Regression, он же Automatic Tuning), адаптивные распределения памяти (Adaptive Memory Grants), адаптивные соединения (Adaptive Joins), обратная связь по оценке кардинальности (CE Feedback), обратная связь по степени параллелизма (DOP Feedback), оптимизация планов, чувствительных к параметрам (Parameter-Sensitive Plan Optimization), и оптимизация планов с необязательными параметрами (Optional Parameter Plan Optimization) — все они полагаются на правильную параметризацию, чтобы адаптироваться к одному и тому же запросу с течением времени. Если каждый запрос приходит в маскировке, эти функции просто не работают.

Существует переключатель на уровне базы данных, который должен помочь: принудительная параметризация (Forced Parameterization). Включите её, и SQL Server (а также Azure SQL DB) начнёт проверять каждый входящий запрос: если он не полностью параметризован, литералы удаляются и заменяются переменными. Проблема в том, что это не работает во многих ситуациях:

• Частично параметризованные запросы – если запрос уже имеет какие-либо параметры, Microsoft предполагает, что он полностью параметризован, что особенно проблематично для .Contains в Entity Framework.
• Литералы в списке SELECT, например SELECT 1 AS ClientId, ... – меня это всегда поражает, когда я их вижу, но, как ни странно, это широко используемый приём по причинам, которые я никогда не пойму.
• Литералы в HAVING, GROUP BY и ORDER BY.
• И многое другое, как описано на странице документации SQL Server 2008 R2, которая с тех пор никогда не обновлялась.

Даже когда принудительная параметризация работает, её внезапное включение вызывает аварийные ситуации, связанные со sniffing'ом параметров. Запросы, которые раньше получали свои собственные «ручные» планы, внезапно начинают использовать переиспользуемые планы, и хотя это отлично для мониторинга производительности, это не так хорошо для производительности конечных пользователей в некоторых сценариях. В вашем приложении может быть 1000 запросов, и 990 из них могут отлично работать с переиспользуемыми запросами, но оставшиеся 10 представляют собой 10 разных аварийных ситуаций со sniffing'ом параметров, которые возникнут из ниоткуда и будут возникать снова и снова, если вы не исправите эти запросы окончательно.

У меня нет ответов.

Просто кажется, что есть два типа компаний:

1. Те, кто правильно параметризует всё и может использовать кэш планов и Хранилище запросов, но страдает от аварий, связанных со sniffing'ом параметров. Поэтому у них хороший мониторинг, но он им нужен, потому что время от времени они сталкиваются с авариями производительности.
2. Те, кто не параметризует хотя бы часть своего кода, поэтому кэш планов и Хранилище запросов в лучшем случае бесполезны, а в худшем — вводят в заблуждение, но у них нет проблем со sniffing'ом параметров. Поэтому у них плохой мониторинг, но … их это не так волнует.

В некоторые дни я смотрю на последнюю группу и говорю: я понимаю. Не всегда! Большую часть времени я хочу использовать современные функции, встроенные в современные версии SQL Server, Azure SQL DB и Intelligent Query Processing. Но в некоторые дни… в некоторые дни я хочу вставлять литералы во все свои запросы.