Краткое руководство. Расширенные события в SQL Server

Статья
04/26/2023

Применимо к:SQL Server

Расширенные события — это упрощенная система мониторинга производительности, которая позволяет пользователям собирать данные, необходимые для мониторинга и устранения проблем в SQL Server. Дополнительные сведения об архитектуре расширенных событий см. в статье Обзор расширенных событий. Эта статья призвана помочь разработчикам SQL, которые незнакомы с расширенными событиями, создать сеанс событий за считанные минуты. С помощью расширенных событий можно просмотреть сведения о внутренних операциях системы SQL и приложения. При создании сеанса расширенных событий система сообщает:

какие события вас интересуют;
как система должна предоставлять вам данные.

В этой статье приводятся следующие сведения:

С помощью снимков экрана иллюстрируются действия в SSMS.exe по созданию сеанса событий.
Снимки экрана сопоставляются с эквивалентными инструкциями Transact-SQL.
Подробно описываются термины и понятия, связанные с интерфейсом и инструкциями T-SQL для сеансов событий.
Показано, как протестировать сеанс событий.
Описываются варианты обработки результатов:
- регистрация и сохранение результатов;
- обработанные и необработанные результаты;
- средства для просмотра результатов различными способами и в различном масштабе времени.
Показано, как можно найти все доступные события.
Предоставляет связи первичного и внешнего ключей, которые являются неявными между динамическими административными представлениями (DMV) для расширенных событий.
Описывается содержимое связанных разделов.

Блоги и другие неофициальные беседы иногда называют расширенные события аббревиацией XEvents.

Примечание

Сведения о расширенных событиях в базе данных Azure SQL, включая примеры кода, см. в разделе Расширенные события в База данных SQL.

Подготовительные действия перед демонстрацией

Для проведения демонстрации необходимо выполнить перечисленные ниже предварительные условия.

Скачивание SQL Server Management Studio (SSMS)

Каждый месяц следует устанавливать последнее ежемесячное обновление для SSMS.
Войдите в Microsoft SQL Server 2014 или более поздней версии.
Ваша учетная запись должна иметь разрешение сервераALTER ANY EVENT SESSION.

Дополнительные сведения о безопасности и разрешениях, связанных с расширенными событиями, можно найти в конце этого раздела в приложении.

Демонстрация интеграции с SSMS

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

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

Этапы демонстрации

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

Общие сведения
События
Хранилище данных
Дополнительно

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

Подключитесь к SSMS.
В обозревателе объектов щелкните Управление>Расширенные события>Создать сеанс. Диалоговое окно Создание сеанса предпочтительнее мастера создания сеанса, хотя во многом они схожи.
В левом верхнем углу выберите страницу Общие . Затем введите МойСеансили любое другое имя в текстовом поле Имя сеанса . Не нажимайте сейчас кнопку ОК — это нужно будет сделать только в конце демонстрации.
В левом верхнем углу выберите страницу События и нажмите кнопку Выбрать .
В области Библиотека событий в раскрывающемся списке выберите элемент Только имена событий.
- В текстовом поле введите sql, чтобы отфильтровать длинный список доступных событий с помощью оператора contains .
- Прокрутите список и щелкните событие с именем sql_statement_completed.
- Нажмите кнопку со стрелкой вправо > , чтобы переместить событие в поле Выбранные события .
Оставаясь на странице События , нажмите кнопку Настроить в правой ее части.

На приведенном ниже снимке экрана, левая часть которого обрезана для наглядности, показана область Параметры конфигурации событий.
Перейдите на вкладку Фильтр (предикат) . Затем щелкните Щелкните здесь, чтобы добавить предложениедля записи всех инструкций SQL SELECT с предложением HAVING.
В раскрывающемся списке Поле выберите sqlserver.sql_text.
- В списке Оператор выберите оператор LIKE.
- В поле Значение введите %SELECT%HAVING% .
Примечание

В этом двухкомпонентном имени sqlserver — это имя пакета, а sql_text — имя поля. Ранее выбранное событие sql_statement_completed должно находиться в том же пакете, что и выбранное поле.
В левом верхнем углу выберите страницу Хранилище данных .
В области Назначения щелкните Щелкните здесь, чтобы добавить назначение.
- В раскрывающемся списке Тип выберите элемент event_file.
- Это значит, что данные события будут сохранены в файле, который можно просмотреть.
Примечание

Хранилище BLOB-объектов Azure нельзя использовать в качестве целевого расположения хранилища данных в локальном экземпляре SQL Server.
В области Свойства в текстовом поле Имя файла на сервере введите полный путь к файлу и его имя.
- Файл должен иметь расширение .xel.
- Для нашего небольшого теста размер файла будет меньше 1 МБ.
В левом верхнем углу выберите страницу Дополнительно.
- Уменьшите значение параметра Максимальная задержка диспетчеризации до 3 секунд.
- Наконец, нажмите кнопку ОК в нижней части окна.
Вернувшись в обозреватель объектов, разверните папку Управление>Сеансы, чтобы увидеть узел МойСеанс.

Редактирование сеанса событий

В обозревателе объектовSSMS можно изменить сеанс событий, щелкнув его узел правой кнопкой мыши и выбрав пункт Свойства. Откроется то же самое диалоговое окно с несколькими страницами.

Соответствующие инструкции T-SQL для сеанса событий

Вы использовали пользовательский интерфейс SSMS, чтобы сформировать скрипт T-SQL для создания сеанса событий. Чтобы просмотреть сформированный скрипт, выполните указанные ниже действия.

Щелкните узел сеанса правой кнопкой мыши и выберите пункты Создать скрипт для сеанса>CREATE в>Буфер обмена.
Вставьте скрипт в любой текстовый редактор.

Ниже приведена инструкция T-SQL CREATE EVENT SESSION для сеанса МойСеанс, которая была сформирована в результате выполнения действий в пользовательском интерфейсе.

CREATE EVENT SESSION [YourSession]
    ON SERVER
    ADD EVENT sqlserver.sql_statement_completed
    (
        ACTION(sqlserver.sql_text)
        WHERE
        ( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%SELECT%HAVING%')
        )
    )
    ADD TARGET package0.event_file
    (SET
        filename = N'C:\Junk\YourSession_Target.xel',
        max_file_size = (2),
        max_rollover_files = (2)
    )
    WITH (
        MAX_MEMORY = 2048 KB,
        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 3 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
    );
GO

Предварительное выполнение инструкции DROP для сеанса событий

Перед выполнением инструкции CREATE EVENT SESSION можно выполнить инструкцию DROP EVENT SESSION на тот случай, если имя сеанса уже существует.

IF EXISTS (SELECT *
      FROM sys.server_event_sessions
      WHERE name = 'YourSession')
BEGIN
    DROP EVENT SESSION YourSession
          ON SERVER;
END
go

Инструкция ALTER для запуска и остановки сеанса событий

Созданный сеанс событий по умолчанию не запускается автоматически. Вы можете запустить или остановить сеанс событий в любой момент с помощью приведенной ниже инструкции T-SQL ALTER EVENT SESSION.

ALTER EVENT SESSION [YourSession]
      ON SERVER
    --ON DATABASE
    STATE = START;   -- STOP;

Вы также можете указать, что сеанс событий должен запускаться автоматически при запуске экземпляра SQL Server. См. ключевое слово STARTUP STATE = ON в инструкции CREATE EVENT SESSION.

В пользовательском интерфейсе SSMS имеется соответствующий флажок на странице Создание сеанса>Общие .

Тестирование сеанса событий

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

В обозревателе объектовSSMS щелкните узел сеанса событий правой кнопкой мыши и выберите пункт Начать сеанс.
Выполните приведенную ниже инструкцию SELECT...HAVING два раза.
- В идеале следует поменять значение HAVING Count между запусками с 2 на 3. Таким образом можно увидеть различия в результатах.
Щелкните узел сеанса правой кнопкой мыши и выберите пункт Остановить сеанс.
Прочитайте следующий подраздел о выборе и просмотре результатов.

SELECT
        c.name,
        Count(*)  AS [Count-Per-Column-Repeated-Name]
    FROM
             sys.syscolumns  AS c
        JOIN sys.sysobjects  AS o
            ON o.id = c.id
    WHERE
        o.type = 'V'
        AND
        c.name like '%event%'
    GROUP BY
        c.name
    HAVING
        Count(*) >= 3   --2     -- Try both values during session.
    ORDER BY
        c.name;

Для полноты ниже приведен образец результата предыдущей инструкции SELECT...HAVING.

/*** Approximate output, 6 rows, all HAVING Count >= 3:
name                   Count-Per-Column-Repeated-Name
---------------------  ------------------------------
event_group_type       4
event_group_type_desc  4
event_session_address  5
event_session_id       5
is_trigger_event       4
trace_event_id         3
***/

Выбор полных результатов в формате XML

В SSMS выполните приведенную ниже инструкцию T-SQL SELECT, чтобы получить результаты, каждая строка которых содержит данные по отдельному экземпляру события. С помощью инструкции CAST AS XML можно легко просмотреть результаты.

Примечание

Система событий всегда добавляет длинный номер к указанному имени XEL -файла event_file. Перед выполнением приведенной ниже инструкции SELECT из файла необходимо скопировать полное имя, предоставленное системой, и вставить его в инструкцию SELECT.

SELECT
        object_name,
        file_name,
        file_offset,
        event_data,
        'CLICK_NEXT_CELL_TO_BROWSE_XML RESULTS!'
                AS [CLICK_NEXT_CELL_TO_BROWSE_XML_RESULTS],
        CAST(event_data AS XML) AS [event_data_XML]
                -- TODO: In ssms.exe results grid, double-click this xml cell!
    FROM
        sys.fn_xe_file_target_read_file(
            'C:\Junk\YourSession_Target_0_131085363367310000.xel',
            null, null, null
        );

Приведенная выше инструкция SELECT позволяет просмотреть полные результаты из любой строки двумя способами.

Выполните инструкцию SELECT в SSMS, а затем щелкните ячейку в столбце event_data_XML . Это очень удобно.
Скопируйте длинную строку XML из ячейки в столбце event_data . Вставьте строку в любой простой текстовый редактор, например Notepad.exe, и сохраните ее в файле с расширением XML. Затем откройте XML-файл в браузере.

Отображение результатов для одного события

Ниже приведена часть результатов в формате XML. Этот код XML сокращен для наглядности. Обратите внимание на то, что в элементе <data name="row_count"> показано значение 6, что соответствует 6 строкам результатов, показанным ранее. И мы можем видеть всю инструкцию SELECT.

<event name="sql_statement_completed" package="sqlserver" timestamp="2016-05-24T04:06:08.997Z">
  <data name="duration">
    <value>111021</value>
  </data>
  <data name="cpu_time">
    <value>109000</value>
  </data>
  <data name="physical_reads">
    <value>0</value>
  </data>
  <data name="last_row_count">
    <value>6</value>
  </data>
  <data name="offset">
    <value>0</value>
  </data>
  <data name="offset_end">
    <value>584</value>
  </data>
  <data name="statement">
    <value>SELECT
        c.name,
        Count(*)  AS [Count-Per-Column-Repeated-Name]
    FROM
             sys.syscolumns  AS c
        JOIN sys.sysobjects  AS o

            ON o.id = c.id
    WHERE
        o.type = 'V'
        AND
        c.name like '%event%'
    GROUP BY
        c.name
    HAVING
        Count(*) &gt;= 3   --2     -- Try both values during session.
    ORDER BY
        c.name</value>
  </data>
</event>

Вывод результатов в SSMS

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

Расширенный просмотр целевых данных из расширенных событий в SQL Server

Основными функциями являются пункты контекстного меню Просмотр целевых данных и Интерактивный просмотр данных.

Просмотр целевых данных

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

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

Просмотр целевых данных в SSMS, Управление расширенными событиями >> Сеансы > Package0.event_file, > щелкните правой кнопкой мыши

Интерактивный просмотр данных

В обозревателе объектовSSMS можно щелкнуть правой кнопкой мыши узел сеанса событий. В контекстном меню выберите пункт Интерактивный просмотр данных. В SSMS будут в режиме реального времени выводиться поступающие данные.

Просмотр динамических данных в SSMS, Управление > расширенными сеансами > событий > YourSession, щелкните правой кнопкой мыши

Сценарии

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

Сценарии использования событий для возникновения блокировок описываются в приведенных ниже статьях. В статьях также показаны некоторые расширенные методы, такие как использование @dbid и использование динамического EXECUTE (@YourSqlString):

Поиск объектов, на которые наложено наибольшее число блокировок
- В этом сценарии используется целевая гистограмма package0.histogram, которая обрабатывает необработанные данные события перед их отображением.
Определение запросов, содержащих блокировки
- В этом примере используется целевое сопоставление package0.pair_matching, в котором парой событий является is sqlserver.lock_acquire и lock_release.

Термины и понятия в расширенных событиях

В следующей таблице перечислены термины, используемые для расширенных событий, и описаны их значения.

Термин	Описание
сеанс событий	Целью является конструкция, основанная на одном или нескольких событиях, а также вспомогательные элементы, такие как действия. Инструкция CREATE EVENT SESSION создает каждый сеанс событий. С помощью инструкции ALTER можно по желанию запускать и останавливать сеансы. Сеанс событий часто называется просто сеансом, если из контекста понятно, что имеется в виду именно сеанс событий. Более подробные сведения о сеансах событий см. в статье Сеансы расширенных событий SQL Server.
event	Определенное событие в системе, наступление которого отслеживается активным сеансом событий. Например, событие sql_statement_completed представляет момент завершения какой-либо инструкции T-SQL. Событие может сообщать различные данные, например длительность.
target	Элемент, который получает выходные данные из регистрируемого события. Служит для вывода данных. Примерами могут служить event_fileи его облегченная версия ring_buffer, хранимая в памяти. Более сложная целевая гистограмма выполняет ряд задач по обработке данных перед их выводом. Любой целевой объект можно использовать для любого сеанса событий. Дополнительные сведения см. в разделе Целевые объекты для расширенных событий в SQL Server.
action	Поле, известное событию. Данные из этого поля отправляются в целевой объект. Поле действия тесно связано с фильтром предиката.
фильтром предиката	Проверка данных в поле события, благодаря которой только нужное подмножество экземпляров события отправляется целевому объекту. Например, фильтр может включать только те экземпляры события sql_statement_completed , в которых инструкция T-SQL содержит строку HAVING.
Пакет	Квалификатор имени, добавляемый к каждому элементу в наборе элементов, связанном с пакетом событий. Например, пакет событий может включать события, связанные с текстом T-SQL. Одно из событий может быть связано с кодом T-SQL в пакете, отделенном командой GO. А другое более частное событие может быть связано с отдельными инструкциями T-SQL. Кроме того, для каждой инструкции T-SQL есть события начала и завершения. Соответствующие событиям поля также содержатся в пакете с событиями. Большинство целевых объектов находятся в пакете package0 и используются с событиями из многих других пакетов.

Обнаружение других событий в пакетах

Приведенная ниже инструкция T-SQL SELECT возвращает строку для каждого доступного события, имя которого содержит строку из трех символов "sql". Конечно, можно изменить значение LIKE для поиска других имен событий. В строках также указывается имя пакета, содержащего событие.

SELECT   -- Find an event you want.
        p.name         AS [Package-Name],
        o.object_type,
        o.name         AS [Object-Name],
        o.description  AS [Object-Descr],
        p.guid         AS [Package-Guid]
    FROM
              sys.dm_xe_packages  AS p
        JOIN  sys.dm_xe_objects   AS o
                ON  p.guid = o.package_guid
    WHERE
        o.object_type = 'event'   --'action'  --'target'
        AND
        p.name LIKE '%'
        AND
        o.name LIKE '%sql%'
    ORDER BY
        p.name, o.object_type, o.name;

Ниже показана возвращаемая строка, формат которой изменен на следующий: имя столбца = значение. Данные получены от события sql-statement_completed , которое использовалось в предыдущем примере. Предложение для столбца Object-Descr особенно полезно.

Package-Name = sqlserver
object_type  = event
Object-Name  = sql_statement_completed
Object-Descr = Occurs when a Transact-SQL statement has completed.
Package-Guid = 655FD93F-3364-40D5-B2BA-330F7FFB6491

Пользовательский интерфейс SSMS для поиска

Другой способ поиска — использование диалогового окна Создание сеанса>События>Библиотека событий в пользовательском интерфейсе SSMS, которое показано на предыдущем снимке экрана.

Классы событий SQL Trace с расширенными событиями

Описание использования расширенных событий с классами событий и столбцами трассировки SQL можно найти в следующей статье: Просмотр эквивалентов расширенных событий для классов событий трассировки SQL

Трассировка событий Windows с расширенными событиями

Описание использования расширенных событий с трассировкой событий Windows можно найти в следующих разделах:

Дополнительные элементы

В этом разделе кратко рассматривается ряд других элементов.

Сеансы событий, устанавливаемые вместе с SQL Server

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

Просмотреть события сеансов можно в обозревателе объектов SSMS на странице Управление>Расширенные события>Сеансы. На июнь 2016 г. список устанавливаемых сеансов событий следующий:

AlwaysOn_health
system_health
telemetry_events

Поставщик PowerShell для расширенных событий

Управлять расширенными событиями SQL Server можно с помощью поставщика SQL Server PowerShell. Подробные сведения см. в следующем разделе: Использование поставщика PowerShell для расширенных событий

Системные представления для расширенных событий

Системные представления расширенных событий:

представления каталога: для сведений о сеансах событий, которые были определены инструкцией CREATE EVENT SESSION;
динамические административные представления: для сведений о сеансах событий, активных в настоящее время.

В разделеИспользование SELECT и JOIN в системных представлениях для расширенных событий в SQL Server приводятся следующие сведения:

инструкции по присоединению представлений друг к другу;
несколько полезных инструкций SELECT для представлений;
взаимосвязь между:
- столбцами представлений;
- предложениями CREATE EVENT SESSION;
- элементами управления пользовательского интерфейса SSMS.

Приложение. Инструкции SELECT для предварительного определения владельца разрешения

В этом разделе упоминаются следующие разрешения:

ALTER ANY EVENT SESSION
VIEW SERVER STATE
CONTROL SERVER

Приведенные ниже инструкции Transact-SQL SELECT могут сообщить, кто имеет эти разрешения.

Прямые разрешения UNION, а также разрешения, производные от роли

Следующая выборка... Инструкция UNION ALL возвращает строки, показывающие, у кого есть необходимые разрешения для создания сеансов событий и запроса представлений системного каталога для расширенных событий.

-- Ascertain who has the permissions listed in the ON clause.
-- 'CONTROL SERVER' permission includes the permissions
-- 'ALTER ANY EVENT SESSION' and 'VIEW SERVER STATE'.
SELECT
        'Owner-is-Principal'  AS [Type-That-Owns-Permission],
        NULL                  AS [Role-Name],
        prin.name             AS [Owner-Name],

        perm.permission_name
            COLLATE Latin1_General_CI_AS_KS_WS
            AS [Permission-Name]
    FROM
             sys.server_permissions  AS perm
        JOIN sys.server_principals   AS prin

            ON prin.principal_id = perm.grantee_principal_id
    WHERE
        perm.permission_name IN
            ('ALTER ANY EVENT SESSION',
            'VIEW SERVER STATE',
            'CONTROL SERVER')
UNION ALL

-- Plus check for members of the 'sysadmin' fixed server role,
-- because 'sysadmin' includes the 'CONTROL SERVER' permission.
SELECT
        'Owner-is-Role'
        , prin.name  -- [Role-Name]
        , CAST( (IsNull(pri2.name, N'No members'))
            AS nvarchar(128))
        , NULL
    FROM
        sys.server_role_members  AS rolm
        RIGHT OUTER JOIN sys.server_principals    AS prin
            ON prin.principal_id = rolm.role_principal_id
        LEFT OUTER JOIN sys.server_principals     AS pri2
            ON rolm.member_principal_id = pri2.principal_id
    WHERE
        prin.name = 'sysadmin'
    ORDER BY
        1,2,3,4;

HAS_PERMS_BY_NAME , функция

Приведенная ниже инструкция SELECT сообщает ваши разрешения. В ней используется встроенная функция HAS_PERMS_BY_NAME.

Кроме того, если у вас есть право на временное олицетворение других учетных записей, вы можете раскомментировать инструкции EXECUTE AS LOGIN и REVERT, чтобы получить сведения о других учетных записях.

--EXECUTE AS LOGIN = 'AccountNameHere';
SELECT HAS_PERMS_BY_NAME
    (
       null
       , null
       , 'ALTER ANY EVENT SESSION'
    );
--REVERT;

Ссылки на сведения о безопасности

Ниже приведены ссылки на документацию по этим инструкциям SELECT и разрешениям.

Подробные сведения о встроенной функции HAS_PERMS_BY_NAME (Transact-SQL)
sys.fn_my_permissions (Transact-SQL)
GRANT, предоставление разрешений на сервер (Transact-SQL)
sys.server_principals (Transact-SQL)
Блог: Эффективные разрешения для ядра СУБД
Масштабируемый плакатв формате PDF, на котором показана иерархия всех разрешений SQL Server.

Ссылки на вспомогательную информацию

sys.fn_xe_file_target_read_file (Transact-SQL)