PDFПри анализе проблем производительности запроса план часто является ключевым элементом. Планы бывают оценочными и действительными, получать и те и другие можно разными способами.
В данной заметке мы рассмотрим получение действительных планов запросов в SQL Server 2019, а также новую возможность – получить последний действительный план.
Оценочный vs Действительный
План запроса представляет собой набор конкретных действий сервера для получения результата, описанного запросом. Эти действия представлены в виде дерева базовых блоков – операторов плана. Построение плана называют компиляцией, перестроение рекомпиляцией.
Планы бывают оценочные и действительные. Вы наверняка встречали эту фразу, если читали про планы выполнения. Далее обычно говорится о том, что они могут отличаться. Все это правда, но иногда приводит к неправильному пониманию. Может сложиться впечатление, что сервер хранит два экземпляра плана, один оценочный, другой действительный, хотя это не так.
Внутри сервера план запроса представляет собой объекты определенных классов, это есть результат трудоемкой работы сервера по оптимизации запроса. После того, как скомпилированный план получен, на его основе генерируются более легковесные структуры – выполняемые планы (executable plan), концептуально тоже объекты, но других классов. Эти объекты и выполняют итерации по строкам. Т.к. исполняемые планы генерируются на основе скомпилированного – они не могут отличаться.
Исполняемых планов может быть несколько, они содержат информацию, специфичную для конкретного выполнения, например значения переменных, если вдруг один и тот же запрос выполняют два пользователя одновременно. Эти планы собирают статистику выполнения при помощи специального итератора, которую мы потом видим в действительном плане.
Таким образом, действительный план по сути является комбинацией информации из скомпилированного плана и информации времени выполнения из исполняемого.
И тем не менее получая оценочный и действительный план мы можем увидеть разные планы.
Дело в том, что планы отличаются, если произошла рекомпиляция. Мы просто получаем новый скомпилированный план, который содержит информацию времени выполнения и т.к. это другой план он может отличаться от скомпилированного до начала выполнения.
Рассмотрим пример, выполнив скрипт:
-- Очистим кэш на сервере dbcc freeproccache; drop table if exists #SalesOrderIDs; create table #SalesOrderIDs (SalesOrderID int primary key); go -- 1. Получаем оценочный план set showplan_xml on; go insert #SalesOrderIDs(SalesOrderID) select SalesOrderID from Sales.SalesOrderHeader where TerritoryID = 1 select count(*) from Sales.SalesOrderDetail sod join #SalesOrderIDs i on i.SalesOrderID = sod.SalesOrderID; go set showplan_xml off; go -- 2. Получаем действительный план set statistics xml on; go insert #SalesOrderIDs(SalesOrderID) select SalesOrderID from Sales.SalesOrderHeader where TerritoryID = 1 select count(*) from Sales.SalesOrderDetail sod join #SalesOrderIDs i on i.SalesOrderID = sod.SalesOrderID; go set statistics xml off; go -- 3. Снова получаем оценочный план set showplan_xml on; go insert #SalesOrderIDs(SalesOrderID) select SalesOrderID from Sales.SalesOrderHeader where TerritoryID = 1 select count(*) from Sales.SalesOrderDetail sod join #SalesOrderIDs i on i.SalesOrderID = sod.SalesOrderID; go set showplan_xml off; go
Создаем временную таблицу, после чего получаем оценочный план запроса на шаге 1.
Т.к. в таблице еще нет строк (мы не начинали выполнение и не добавляли строки, а просто компилируем запрос), план строится исходя из минимально возможной оценки – во временной таблице 1 строка. С такой низкой оценкой оптимальным вариантом является соединение вложенными циклами, и мы видим план с Nested Loops Join. (1)
После этого, мы начинаем выполнение, запросив действительный план. Во время выполнения во временную таблицу добавляются строки и при следующем ее использовании возникает рекомпиляция, поскольку статистика по ней изменилась. Сервер строит новый оценочный план, на этот раз с Hash Join, добавляет в него информацию времени выполнения, и мы видим изменившийся план (2).
Таким образом, создается ощущение, что один и тот же план, оценочный или действительный, может отличаться, на самом деле это два разных плана.
Если мы снова запросим оценочный (3), на шаге 3, то мы увидим, что он совпадает с действительным на шаге 2. Что логично, ведь основа – один и тот же скомпилированный план.
Названия Оценочный и Действительный общеприняты, но могут сбивать с толку. Более логичными, на мой взгляд, являются названия расширенных событий для получения плана на разных этапах:
- query_post_compilation_showplan – план после компиляции (то, что мы называем оценочным)
- query_pre_execution_showplan – план перед выполнением
- query_post_execution_showplan – план после выполнения (то, что мы называем действительным)
В таких терминах более понятно, что речь идет об одном плане, но на разных стадиях. Если включить сессию extended events с вышеназванными событиями. То мы увидим, как происходил этот процесс. Сначала был post_compilation_plan (оценочный план) с Nested Loops, потом статистика во временной таблице изменилась, произошла рекомпиляция и был построен другой post_compilation_plan, который позже был дополнен информацией времени выполнения и выдан как действительный.
На данном этапе мы разобрались с оценочным и действительным планом, которые являются по сути одним и тем же деревом операторов, но отличаются наполнением. Остановимся на наполнении более подробно.
Какую информацию содержит действительный план, которой нет в оценочном?
Это информация времени выполнения (runtime statistics), собственно та информация, которая говорит нам как именно выполнялся запрос. По мере развития SQL Server, в эту информацию добавляется все больше и больше сведений, я перечислю некоторые из них:
- информация о количестве реально обработанных строк;
- информация по использованию памяти;
- информация по физическим и логическим чтениям;
- информация по использованию ресурсов процессора;
- предупреждения о сливе данных в tempdb и другие предупреждения;
- реальная степень параллелизма;
- число потоков и распределение работы по ним;
- статистика ожиданий;
- время запроса;
и т.д.
Эти сведения позволяют понять, как выполнялся запрос и где могли возникнуть сложности, поэтому, при анализе проблем производительности действительный план полезнее оценочного.
Проблема с получением действительного плана состоит в том, что это достаточно ресурсоемкий процесс для сервера. Для анализа одного конкретного запроса, разница в производительности может быть незаметна, но, если включить сбор действительных планов для всей рабочей нагрузки, можно серьезно навредить производительности.
Однако, появился новый механизм для профилирования запросов – упрощенная инфраструктура профилирования статистики выполнения запросов (Light Weight Query Profiling, LWP).
Стандартное vs Упрощенное профилирование запросов
Упрощенное профилирование появилось впервые в 2014 SP2 и 2016 RTM. В дальнейшем, от версии к версии его функциональность расширялась, добавлялись новые способы просмотра информации (DMO, Extended Events), снижалась накладные расходы.
Третья версия инфраструктуры упрощенного профилирования была представлена в SQL Server 2019 и, согласно документации, добавляет в среднем до 2% дополнительной нагрузки на сервер, тогда как стандартная инфраструктура до 75%. При этом разница между ними заключается в том, что упрощенное профилирование не собирает метрики CPU.
Lightweight profiling offers a query execution statistics collection mechanism with an expected overhead of 2% CPU, compared with an overhead of up to 75% CPU for the standard query profiling mechanism.
Unlike standard profiling, lightweight profiling does not collect CPU runtime information. However, lightweight profiling still collects row count and I/O usage information.
Начиная с SQL Server 2019 этот механизм включен по умолчанию. Если накладные расходы на профилирование при вашей рабочей нагрузке получились выше ожидаемых, или даже если небольшое увеличение критично, то профилирование можно отключить при помощи опции БД LIGHTWEIGHT_QUERY_PROFILING = ON|OFF (начиная с CTP 2.3).
Ниже я составил сводную таблицу, в каких случаях применяется та или иная инфраструктура профилирования.
Режимы профилирования:
Profiling.xlsx (11 KB)
* LWP vN – Light Weight Profiling version N, упрощенное профилирование определённой версии.
DMO получающие сведения в режиме профилирования:
- dm_exec_query_profiles – позволяет получить операторы плана во время выполнения, с 2014 RTM
- dm_exec_query_statistics_xml — позволяет получить план на текущий момент выполнения, с 2016 SP1, 2017 RTM
- dm_exec_query_plan_stats — позволяет получить последний действительный план, с 2019 CTP 2.4
Инфраструктура упрощенного профилирования позволяет получить сведения о выполняющемся или выполненном запросе с гораздо меньшими накладными расходами, чем при стандартном сборе действительных планов, значительные улучшения в производительности были достигнуты начиная с версии 2. Более детальные сведения можно посмотреть в документации:
Последний действительный план
В предыдущих разделах мы выяснили важность действительного плана при анализе проблем производительности запроса и возможности новой облегченной инфраструктуры профилирования.
В этом разделе мы подробнее остановимся на новой функции sys.dm_exec_query_plan_stats, добавленной в 2019 CTP 2.4.
В действительности, часто бывает необходимо посмотреть, какой план был у выполнявшегося долго запроса. Как правило, это событие не ожидаемое, т.е. заранее сбор планов не включается, поэтому, единственным вариантом остается выполнить запрос повторно, включив сбор действительного плана.
Трудности возникают, если запрос выполнялся долго и, чтобы получить действительный план, приходится ждать пока он выполнится снова. Если же в запросе были параметры, а выполняли его пользователи, дополнительно приходится узнавать, с какими параметрами он был выполнен, чтобы воспроизвести ситуацию.
В SQL Server 2016 появился Query Store, который может помочь в анализе проблемы, т.к. хранит историю планов и метрики выполнения (время, процессор, чтения и т.д.). Так, если план изменился, можно посмотреть предыдущий план и сравнить с тем, который выполнялся медленно. Но что, если план не изменился, а изменились, например, параметры запроса, либо же план изначально был неэффективный и сравнивать не с чем.
SQL Server 2019 предлагает функцию sys.dm_exec_query_plan_stats. Используя ее совместно с упрощенной (или стандартной) инфраструктурой профилирования можно получить последний действительный план запроса, даже если вы не включали никаких трассировок или расширенных событий по сбору планов.
Для того, чтобы включить эту возможность нам понадобится глобально включить документированный флаг трассировки 2451.
Рассмотрим пример:
-- Удостоверимся, что LWP включен (должен быть включен по-умолчанию) alter database scoped configuration set LIGHTWEIGHT_QUERY_PROFILING = ON; -- включим глобально флаг трассировки dbcc traceon (2451, -1); -- Тестовый запрос use AdventureWorks2016CTP3; go select count(*) from Sales.SalesOrderDetail sod join Sales.SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID group by soh.Status; go -- Получим план, хотя мы не включали сбор плана или трассировок с расширенными событиями select ps.query_plan from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_query_plan_stats(cp.plan_handle) ps cross apply sys.dm_exec_sql_text(cp.plan_handle) st where st.text like '%SalesOrderHeader%' and st.text not like '%dm[_]exec[_]cached[_]plans%' go
Результат:
Как видно, мы получили действительный план запроса, хотя не включали специальных режимов (set statistics xml, Profiler showplan events и т.д.). Это возможно благодаря инфраструктуре упрощенного профилирования, включенной в 2019 по умолчанию.
Обратите внимание, что в данных выполнения по операторам отсутствует статистика по CPU, точнее там 0, что нормально и ожидаемо согласно документации, но также 0 в затраченном времени, а атрибут Actual IO отсутствует вовсе, хотя в документации явно сказано: «lightweight profiling still collects row count and I/O usage information». На мой взгляд, это артефакты CTP и в будущем, это либо исправят, либо документируют.
Если вы не будете включать TF 2451, либо выключите Light Weight Profiling, то запрос к представлению не вернет пустое множество или NULL, вместо этого, он вернет оценочный план, как если бы вы запрашивали представление sys.dm_exec_query_plan.
Если же у вас включено стандартное профилирование (например, событие query_post_execution_showplan), то функция вернет полноценный действительный план, включая метрики времени и CPU.
Для сохранения информации о выполнении требуется дополнительная память, за работу с памятью этих объектов отвечает отдельный memory clerk – MEMORYCLERK_LWC (LightWeightCache). Чтобы не перерасходовать память, в LWC помещаются не все планы, а только относительно сложные или зависящие от ресурсов.
Рассмотрим два одинаковых запроса оптимизированных по-разному. Первый использует оператор группировки Stream Aggregate (благодаря подсказке order group) – он не потребляет память. Второй оператор Hash Match (благодаря подсказке hash group) – он требует выделения памяти.
--чистим кэш alter database scoped configuration clear procedure_cache; go -- план не требует памяти select count(*) from Sales.SalesOrderDetail group by ProductID option(order group); -- план требует память select count(*) from Sales.SalesOrderDetail group by ProductID option(hash group); go -- Получим план select ps.query_plan from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_query_plan_stats(cp.plan_handle) ps cross apply sys.dm_exec_sql_text(cp.plan_handle) st where st.text like '%SalesOrderDetail%' and st.text not like '%dm[_]exec[_]cached[_]plans%' go
Посмотрим на результат (обратите внимание, что планы всего пакета инструкций отдаются как один XML):
Мы видим, что первый, не потребляющий ресурсов памяти — оценочный, а второй, требующий память, содержит реальное число строк и является действительным.
Резюме
Мы рассмотрели, что представляет из себя оценочный и действительный планы и чем последний может быть полезен при отладке проблем производительности. SQL Server 2019 предоставляет новую, упрощенную инфраструктуру профилирования, которая имеет небольшие накладные расходы по сравнению со стандартной и позволяет осуществлять постоянный мониторинг действительных планов без дополнительных усилий.
Подытоживая, скажу, что мне нравится то, что SQL Server продолжает развиваться в сторону большего разнообразия средств мониторинга производительности, упрощая нам жизнь.
