SQL-Ex blog

Если имеется большой разброс производительности запроса, идеально обратиться к изменению кода или схемы (например, индексированию). Навязывание плана запросу очень похоже на создание руководства по планам - они похожи, но различаются в том, что первое - это временное решение. Я также рассматриваю добавление OPTION (RECOMPILE) как временное решение. Некоторых из вас может это шокировать, но когда я вижу RECOMPILE в запросе, я сразу спрашиваю, почему эта опция добавлена, когда она была добавлена, и начинаю выискивать возможность её удаления.

Имея это в виду, как я оцениваю plan forcing, и как я решаю, когда навязать план? Когда запрос имеет разброс в производительности.

Рассмотрим запрос А, который генерирует множество планов, но все они имеют примерно одинаковые показатели длительности (duration), ввода/вывода (I/O), и использования процессора (CPU). Производительность разных планов одинакова. Я не буду навязывать план для такого запроса.

Запрос с множеством подобных планов

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

Запрос с множеством планов, которые имеют различную производительность

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

Теперь, если моя рабочая нагрузка содержит много запросов с несколькими планами, с чего мне начать? С худших. Если я ограничена в ресурсах (например, по процессору или вводу-выводу), тогда следует обратить внимание на запросы, которые потребляют наибольшие ресурсы, и начать с них. Но я также ищу сценарии типа "смерть от тысячи порезов", т.е. запросы, которые выполняются за минуту сотни или тысячи раз. Когда я собираюсь исследовать множественные планы, я также хочу знать, почему запрос выполняется столь часто.

Пока я говорила об одной рабочей нагрузке. Что если вы поддерживаете сотни экземпляров SQL Server? Очевидно, вы можете применить подход, который я описала выше и который требует обзора медленных запросов с множественными планами, и решить, какой план (если он есть) применить, пока не будет выработано решение проблемы. Или, если у вас установлен SQL Server 2017 Enterprise Edition, выбрать автоматическую корректировку плана (Automatic Plan Correction), которая навяжет план запросу (без вмешательства человека), если будет замечено падение производительности. Я уже писала об автоматической коррекции плана в SQL Server, поэтому не буду подробно на этом останавливаться.

Будете ли вы навязывать план вручную, или позволите SQL Server делать это с помощью Automatic Plan Correction, я все равно расцениваю plan forcing как временное решение. Я не ожидаю, что у вас будут планы на месяцы, не говоря уже о годах. Жизнь навязанного плана будет, конечно, зависеть от того, насколько быстро происходят изменения в коде и схеме на рабочем сервере. Если вы собираетесь "установить и забыть об этом", вручную установленный план теоретически может использоваться в течение очень долгого времени. В этом сценарии в вашей ответственности лежит периодическая проверка, что план все еще "лучший" для данного запроса. Я бы выполняла проверку каждую пару недель; в крайнем случае, раз в месяц. Так или иначе, план остается оптимальным в зависимости от используемых в запросе таблиц, данных в этих таблицах, меняются ли в них данные и как, вносились ли изменения в схему и т.д.

Кроме того, вы не хотите игнорировать принудительные планы, потому что бывает, что принудительный план не используется (вы можете использовать Extended Events для мониторинга этих случаев). Даже если вы навязываете план вручную, это может произойти. Например, если принудительный план использует индекс, который удаляется, или же его определение меняется так, что не может быть использован в плане тем же самым образом, и тогда принуждение не работает. Важно: если принуждение не работает, запрос пройдет все этапы обычной оптимизации и компиляции, и будет выполнен; SQL Server не позволит вашему запросу сбоить! Если у вас есть навязанные планы и вы не знаете о ресурсах, которые могут повлиять на их исполнение, обратите внимание на значение last_force_failure_reason в результатах sys.query_store_plan. Если вы вручную установили план для запроса, и этот план сбоит, он остается привязанным. Вы должны вручную "отвязать" его, чтобы не заставлять SQL Server пытаться использовать этот план. Как вы можете понять, существует множество факторов, влияющих на навязывание плана, вот почему вы не можете просто навязать план и забыть о нем.

Это поведение меняется, если вы используете Automatic Plan Correction (APC). Как говорится в документации, если план автоматически привязывается, то он автоматически отвязывается, если:

• навязывание не работает по какой-либо причине;


• при использовании навязанного плана наблюдается падение производительности;


• имеет место перекомпиляция в результате изменения схемы или обновления статистики.

С APC еще предстоит поработать - или вы захотите использовать расширенные события (Extended Events) или sys.dm_db_tuning_recommendations, чтобы увидеть, какие планы были навязаны, а затем решить, стоит ли привязать их вручную. Если вы навязываете план вручную, то он никогда не будет автоматически отвязан.

Есть много соображений, которые говорят в пользу планов принуждения; я полагаю, что это прекрасная альтернатива руководству планов (plan guides), их проще использовать и нет привязки к схеме. Это абсолютно ценно для DBA, а также на время разработки для исследования того, какие планы следует навязать, а затем использовать это как временную меру до решение проблемы на долгосрочной основе.