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

Понимание событий

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

Вот пример того, как настроить сеансы событий для захвата этих событий:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='TrapEstExecPlans')
    DROP EVENT SESSION TrapEstExecPlans ON SERVER;
GO
CREATE EVENT SESSION TrapEstExecPlans ON SERVER
ADD EVENT sqlserver.query_pre_execution_showplan(
    ACTION (sqlserver.database_name, sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.plan_handle, sqlserver.sql_text, sqlserver.tsql_stack, package0.callstack, sqlserver.query_hash, sqlserver.session_id, sqlserver.request_id)
    WHERE sqlserver.database_id = 9 AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
)
ADD TARGET package0.event_file(SET filename=N'C:\Database\XE\TrapEstExecPlans.xel', max_file_size=(5), max_rollover_files=(4)),
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON);
GO

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='TrapExecPlans')
    DROP EVENT SESSION TrapExecPlans ON SERVER;
GO
CREATE EVENT SESSION TrapExecPlans ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION (sqlserver.database_name, sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.plan_handle, sqlserver.sql_text, sqlserver.tsql_stack, package0.callstack, sqlserver.query_hash, sqlserver.session_id, sqlserver.request_id)
    WHERE sqlserver.database_id = 9 AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
)
ADD TARGET package0.event_file(SET filename=N'C:\Database\XE\TrapExecPlans.xel', max_file_size=(5), max_rollover_files=(4)),
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON);
GO

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

Анализ захваченных данных

После выполнения запроса можно проанализировать захваченные данные для изучения планов выполнения. В этом примере мы сосредоточимся на сеансе TrapEstExecPlans.

Вот пример запроса для получения захваченных данных:

SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    CAST(event_data.value('(event/action[@name="plan_handle"]/value)[1]', 'varchar(max)') AS XML) as plan_handle,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text,
    planxml.query('.') AS plan_xml
FROM
(
    SELECT evnt.query('.') AS event_data, xmlplan.query('.') AS event_plan
    FROM
    (
        SELECT CAST(target_data AS xml) AS TargetData
        FROM sys.dm_xe_sessions AS s
        INNER JOIN sys.dm_xe_session_targets AS t
            ON s.address = t.event_session_address
        WHERE s.name = 'TrapEstExecPlans'
          AND t.target_name = 'ring_buffer'
    ) AS tab
    CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS split(evnt) 
    CROSS APPLY evnt.nodes('(data[@name="showplan_xml"]/value)[last()]/*') AS showplan(xmlplan)
) AS evts(event_data, planxml);

Этот запрос извлекает имя события, дескриптор плана, SQL-текст и XML-план выполнения. Сравнивая планы выполнения, полученные из расширенных событий, с планом выполнения в режиме реального времени, можно выявить любые отличия.

Работа с отличиями

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

Для решения этой проблемы доступны несколько вариантов:

1. Найти дескриптор плана, который был захвачен действием.
2. Найти QueryPlanHash, доступный в XML данных плана выполнения.
3. Использовать действие sql_text для понимания типа плана (select, update, delete и т. д.), захваченного в данных сеанса расширенных событий.

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

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

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