План запроса MS SQL

Существует несколько способов получения плана выполнения, который использовать будет зависеть от ваших обстоятельств. Обычно вы можете использовать SQL Server Management Studio для получения плана, однако, если по какой-то причине вы не можете запустить свой запрос в SQL Server Management Studio, вам может оказаться полезным получить план через SQL Server Profiler или путем проверки кеш плана.

Способ 1 — Использование SQL Server Management Studio

В SQL Server есть несколько опрятных функций, которые упрощают сбор плана выполнения, просто убедитесь, что пункт меню «Включить фактический план выполнения» (найденный в меню «Запрос» ) отмечен галочкой и запустит ваш как обычно.

Если вы пытаетесь получить план выполнения для операторов в хранимой процедуре, вы должны выполнить хранимую процедуру, например:

exec p_Example 42

Когда ваш запрос завершен, вы увидите дополнительную вкладку «План выполнения», которая появится в панели результатов. Если вы запустили много утверждений, вы можете увидеть много планов, отображаемых на этой вкладке.

Здесь вы можете проверить план выполнения в SQL Server Management Studio или щелкнуть правой кнопкой мыши по плану и выбрать «Сохранить план выполнения как…», чтобы сохранить план в файл в формате XML.

Способ 2 — Использование опций SHOWPLAN

Этот метод очень похож на метод 1 (на самом деле это то, что делает SQL Server Management Studio внутренне), однако я включил его для полноты или если у вас нет доступной SQL Server Management Studio.

Перед выполнением запроса запустите один следующих операторов. Оператор должен быть единственным оператором в пакете, т.е. Вы не можете одновременно выполнять другой оператор:

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS XML ON — The is the recommended option to use

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

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

SET <<option>> OFF

Сравнение форматов плана выполнения

Если у вас есть сильное предпочтение, я рекомендую использовать параметр STATISTICS XML. Эта опция эквивалентна опции «Включить фактический план выполнения» в SQL Server Management Studio и предоставляет самую большую информацию в наиболее удобном формате.

• SHOWPLAN_TEXT — отображает базовый оценочный план выполнения, основанный на тексте, без выполнения запроса
• SHOWPLAN_ALL — отображает оценочный план выполнения на основе текста с оценкой стоимости без выполнения запроса
• SHOWPLAN_XML — отображает оценочный план выполнения на основе XML с оценкой стоимости без выполнения запроса. Это эквивалентно опции «Отобразить примерный план выполнения…» в SQL Server Management Studio.
• STATISTICS PROFILE — Выполняет запрос и отображает фактический план выполнения на основе текста.
• STATISTICS XML — Выполняет запрос и отображает фактический план выполнения на основе XML. Это эквивалентно опции «Включить фактический план выполнения» в SQL Server Management Studio.

Способ 3 — Использование профилировщика SQL Server

Если вы не можете запустить запрос напрямую (или ваш запрос не запускается медленно при его непосредственном запуске — помните, что мы хотим, чтобы план запроса выполнялся плохо), тогда вы можете зафиксировать план с помощью SQL Server Profiler след. Идея состоит в том, чтобы запустить ваш запрос, пока трассировка, которая захватывает один из событий «Showplan», запущена.

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

• Откройте SQL Server Profiler и создайте новую трассировку, соединяющую нужную базу данных, с которой вы хотите записать трассировку.
• На вкладке «Выбор событий» установите флажок «Показать все события», проверьте строку «Производительность» → «Showplan XML» и запустите трассировку.
• Пока трассировка работает, сделайте все, что вам нужно, чтобы запустить медленный запрос.
• Дождитесь завершения запроса и остановки трассировки.
• Чтобы сохранить трассировку, щелкните правой кнопкой мыши по плану xml в профиле SQL Server и выберите «Извлечь данные о событиях…», чтобы сохранить план в файл в формате XML.

Полученный вами план эквивалентен опции «Включить фактический план выполнения» в SQL Server Management Studio.

Метод 4 — Проверка кеша запросов

Если вы не можете запустить свой запрос напрямую, и вы также не можете захватить трассировку профилировщика, вы все равно сможете получить оценочный план, проверив кеш-план SQL-запроса.

Мы проверяем кеш плана, запрашивая SQL Server DMVs. Ниже приведен базовый запрос, в котором будут перечислены все кэшированные планы запросов (как xml) вместе с их текстом SQL. В большинстве баз данных вам также необходимо будет добавить дополнительные условия фильтрации, чтобы отфильтровать результаты вплоть до интересующих вас планов.

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Выполните этот запрос и щелкните на плане XML, чтобы открыть план в новом окне — щелкните правой кнопкой мыши и выберите «Сохранить план выполнения как…», чтобы сохранить план в файл в формате XML.

Примечания:

Поскольку существует так много факторов (начиная от таблицы и схемы индекса до хранящихся данных и статистики таблицы), вы должны всегда пытаться получить план выполнения из интересующей вас базы данных (обычно тот, который испытывает проблему с производительностью).

Вы не можете зафиксировать план выполнения для зашифрованных хранимых процедур.

«фактические» и «оцененные» планы выполнения

Фактический план выполнения — это тот, где SQL Server фактически выполняет запрос, тогда как оценочный план выполнения SQL Server работает над тем, что он мог бы сделать, не выполняя запрос. Хотя логически эквивалентный, фактический план выполнения намного полезнее, поскольку он содержит дополнительные данные и статистику о том, что на самом деле произошло при выполнении запроса. Это важно при диагностике проблем, когда оценки SQL-серверов отключены (например, когда статистика устарела).

• Ожидаемый план фактического исполнения

Как интерпретировать план выполнения запроса?

Это тема, достойная достаточно для бесплатного book.

См. также:

• Основы плана выполнения
• SHOWPLAN Разрешения и транзакции Transact-SQL
• SQL Server 2008 — Использование хешей запросов и хэшей плана запроса
• Анализ кэша плана SQL Server