Новое в SQL Server 2022: улучшения в sys.dm_exec_query_statistics_xml

Автор: Vivek Janakiraman Unleashing SQL Server 2022: Enhancements to sys.dm_exec_query_statistics_xml

Одним из улучшений в SQL Server 2022 является дальнейшее совершенствование динамического административного представления (DMV)  sys.dm_exec_query_statistics_xml. Этот DMV предоставляет подробную статистику выполнения запросов, что очень полезно для повышения их производительности и для оптимизации.

Улучшения

В SQL Server 2022 для этого DMV представлено несколько ключевых усовершенствований, в том числе:

1. Расширена информация о плане исполнения запроса, она стала более подробной.
2. Для выявления узких мест включена комплексная статистика ожиданий.
3. Улучшенная интеграция с Query Store для исторического анализа.

 

Пример использования

Тут приведен пример из оригинальной статьи, создание объектов и заполнение тестовыми данными можно посмотреть там.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

    qst.sql_handle,

    qst.plan_handle,

    qst.execution_count,

    qst.total_worker_time,

    qst.total_elapsed_time,

    qst.total_logical_reads,

    qst.total_physical_reads,

    qst.creation_time,

    qst.last_execution_time,

    q.text AS query_text,

    qpx.query_plan

FROM

    sys.dm_exec_query_stats AS qst

CROSS APPLY

    sys.dm_exec_sql_text(qst.sql_handle) AS q

CROSS APPLY

    sys.dm_exec_query_plan(qst.plan_handle) AS qpx

WHERE

    q.text LIKE '%SELECT o.OrderID, o.OrderDate, c.Name AS CustomerName, p.ProductName, oi.Quantity, oi.Price%';

Анализ информации расширенного плана

Ниже представлен пример запроса, который позволяет извлечь недоступные в прежних версиях детали.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

    query_plan.value('(//RelOp/LogicalOp)[1]', 'NVARCHAR(100)') AS LogicalOperation,

    query_plan.value('(//RelOp/PhysicalOp)[1]', 'NVARCHAR(100)') AS PhysicalOperation,

    query_plan.value('(//RelOp/RunTimeInformation/RunTimeCountersPerThread/ActualRows)[1]', 'INT') AS ActualRows,

    query_plan.value('(//RelOp/RunTimeInformation/RunTimeCountersPerThread/ActualEndOfScans)[1]', 'INT') AS ActualEndOfScans

FROM

    (SELECT CAST(qpx.query_plan AS XML) AS query_plan

     FROM sys.dm_exec_query_stats qs

     CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpx

     WHERE qs.sql_handle = (SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = @@SPID)) AS x;

Мониторинг статистики ожидания

Статистика ожидания помогает выявить узкие места производительности, такие как ожидание процессора, ввода-вывода или памяти. SQL Server 2022 предоставляет расширенную статистику ожидания в планах выполнения запросов. Ниже демонстрационный пример:

SELECT

    wait_type,

    wait_time_ms AS total_wait_time_ms,

    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,

    signal_wait_time_ms

FROM

    sys.dm_exec_session_wait_stats

WHERE

    session_id = @@SPID;

Использование интеграции с Query Store

Интеграция с Query Store позволяет проводить исторический анализ производительности запросов, помогая вам понять тенденции и обнаружить регресс производительности.

SELECT

    qsp.plan_id,

    qsp.query_id,

    qsqt.query_sql_text AS query_text,

    qsrs.count_executions AS execution_count,

    qsrs.avg_duration,

    qsrs.avg_cpu_time,

    qsrs.avg_logical_io_reads

FROM

    sys.query_store_runtime_stats qsrs

JOIN

    sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id

JOIN

    sys.query_store_query qsq ON qsp.query_id = qsq.query_id

JOIN

    sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id

WHERE

    qsqt.query_sql_text LIKE '%SELECT o.OrderID, o.OrderDate, c.Name AS CustomerName, p.ProductName, oi.Quantity, oi.Price%';