Планом запроса называется последовательность логических и физических операторов, которые должны быть выполнены СУБД для того чтобы получить результат SQL-запроса. Получение плана запроса может потребоваться в различных ситуациях, а в первую очередь при задачах оптимизации запросов.
Я хочу описать три способа получить план запроса используя инструменты СУБД MS SQL Server, каждый из способов может оказаться предпочтительнее в той или иной ситуации.
Получение плана запроса с помощью Profiler’а (или Extended Events)
Получение плана запроса с помощью Profiler’а (или нового механизма — Extended Events) является, наверное, наиболее популярным способом и причина заключается в том, что с помощью Profiler’а можно перехватить информацию о любом запросе, исполняемом в данный момент экземпляром SQL Server’а. Типичным, с целью получения плана запроса, является приведенный ниже способ работы с Profiler’ом (в связке с 1С:Предприятие):
- Настроить Profiler (или Extended Events) и поставить его на паузу
- Если трассировка содержит данные, очистить окно трассировки
- С помощью отладчика в 1С:Предприятие остановиться перед выполнением необходимого запроса
- Снять Profiler с паузы
- Выполнить запрос
- Остановить Profiler
- Проанализировать полученную информацию
Profiler является инструментом для создания трассировок и управления ими. С помощью данного инструмента можно получать различную информацию о работе СУБД. В данном случае мы будем использовать его для получения информации о выполняемом запросе, в частности, для того чтобы получить его план выполнения.
Для того чтобы получить план запроса в Profiler следует добавить следующие события:
| Событие | Описание |
|---|---|
| Showplan All | Выводит подробную информацию о предполагаемом плане запроса в текстовом виде |
| Showplan Statistics Profile | Выводит подробную информацию о действительном плане запроса в текстовом виде |
| Showplan XML | Выводит подробную информацию о предполагаемом плане запроса в XML формате (может быть представлен графически) |
| Showplan XML Statistics Profile | Выводит подробную информацию о действительном плане запроса в XML формате (может быть представлен графически) |
Также можно добавить необходимые отборы: по имени/идентификатору базы данных, номеру соединения, длительности выполнения.
Более подробная информация о работе с профайлером приведена в статье «Работа с Profiler SQL Server. Примеры настройки трассировок».
Получение кэшированного плана запроса с помощью динамической функции
Перед выполнением запроса СУБД проверяет наличие актуального кэшированного плана запроса. Если такой план запроса существует, тогда СУБД использует его, а не компилирует план запроса заново. Это позволяет сократить время выполнения запроса и именно поэтому, после выполнения очистки процедурного кэша, запросы выполняются дольше (происходит компиляция плана запроса). Таким образом, если мы знаем текст искомого запроса, мы можем получить его план из кэша (если он есть в кэше). Для этого необходимо обратиться к следующим динамическим функциям:
| Функция | Описание |
|---|---|
| dm_exec_query_stats | Возвращает суммарную статистику производительности для кэшированных планов запросов в SQL Server |
| dm_exec_sql_text | Возвращает текст пакета SQL, который определен указанным параметром |
| dm_exec_query_plan | Возвращает события инструкции Showplan в XML-формате для пакета, указанного в дескрипторе плана |
Для поиска плана запроса необходимо выполнить в SQL Server Management Studio нижеприведенный запрос (также доступен во вложении к статье), при этом изменив условия отбора:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT TOP 20 qs.last_execution_time AS Last_execution_time, SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS Query_text, qp.query_plan AS Query_plan, qs.execution_count AS Execution_count FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp WHERE qs.last_execution_time > '2016-08-01 11:30:00.000' /* 1. Date & Time filter */ and qt.text like '%FROM dbo._AccumRg17539 T1%' /* 2. SQL query text filter */ and qt.text not like '%Query Finder%' /* 3. Special condition */ |
В запросе добавлены условия по:
- Времени последнего выполнения
- Тексту искомого запроса (таких фильтров можно добавить несколько, уточняя результат поиска)
- Специальное условие для того чтобы сам запрос поиска не попадал в результат поиска (менять не надо)
Результатом запроса будет таблица с колонками: Last_execution_time (последнее время выполнения), Query_text (текст SQL-запроса), Query_plan (План SQL-запроса) и Execution_count (количество выполнений).
Щелкнув в Management Studio по ссылке в колонке Query_plan будет открыто графическое представление плана запроса.
Получение плана запроса в Management Studio
Еще один способ получить план запроса — выполнить SQL-запрос непосредственно в Management Studio. Специально для этих целей в командном меню «Запрос» (Query) присутствует два пункта:
- Показать предполагаемый план выполнения (Display Estimated Execution Plan)
- Включить действительный план выполнения (Include Actual Execution Plan)
Для получения предполагаемого плана необходимо ввести текст запроса и нажать на пункт «Показать предполагаемый план выполнения», выполнять сам запрос не требуется. Получение актуального плана непосредственно связано с выполнением запроса, поэтому пункт «Включить действительный план выполнения» включает/отключает возможность вывода информации об актуальном плане запроса. Таким образом, для получения действительного плана выполнения необходимо включить вышеуказанную опцию и выполнить запрос.