Опубликовано 23 февраля 2022 года
Автор статьи Gianluca Sartori

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

Извлечение данных сеанса из XML, который помещается в кольцевой буфер (ring buffer);
Использование функции master.sys.fn_xe_file_target_read_file для чтения данных сеанса из файла, указанного в качестве цели хранения данных;

Первый подход довольно сложный (нужно копаться в XML — этим всё сказано).
Второй подход не быстрый и требует наличие целевого файла, который не всегда может быть доступен. Он также требует остановки сеанса для чтения всех сохранённых данных или написания мудрёного кода для постепенного чтения данных во время выполнения сеанса.

Именно здесь на помощь приходит XESmartTarget! Все, что вам нужно сделать, это написать некоторую конфигурацию в json-файле и позволить XESmartTarget (требуется .NET 4.8) перенаправить данные сеанса в нужное место.

Для этой задачи используется TableAppenderResponse, который считывает все события из сеанса с помощью API потоковой передачи данных и записывает их в таблицу, которая может быть создана автоматически или определена заранее.

Определение сессии

Для нашего примера используется простой сценарий T-SQL, который создаёт сеанс. Он будет захватывать события rpc_completed и sql_batch_completed, то же самое, что делается в стандартной трассировке Profiler.

В данном случае не нужно настраивать какие-либо цели, достаточно добавить события и запустить сеанс:

IF NOT EXISTS ( SELECT * FROM sys.server_event_sessions WHERE name = 'Recipe02')
 
CREATE EVENT SESSION [Recipe02] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_pid,
        sqlserver.database_name,
        sqlserver.nt_username,
        sqlserver.query_hash,
        sqlserver.server_principal_name,
        sqlserver.session_id
    )
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))
),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_pid,
        sqlserver.database_name,
        sqlserver.nt_username,
        sqlserver.query_hash,
        sqlserver.server_principal_name,
        sqlserver.session_id
    )
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))
)
GO
 
IF NOT EXISTS ( SELECT * FROM sys.dm_xe_sessions WHERE name = 'Recipe02')
    ALTER EVENT SESSION Recipe02 ON SERVER STATE = START;

Конфигурация XESmartTarget

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

{
    "Target": {
        "ServerName": "$ServerName",
        "SessionName": "Recipe02",
        "FailOnProcessingError": false,
        "Responses": [
            {
                "__type": "TableAppenderResponse",
                "ServerName": "$ServerName",
                "DatabaseName": "XERecipes",
                "TableName": "Recipe_02_Queries",
                "AutoCreateTargetTable": false,
                "OutputColumns": [
                    "name", 
                    "collection_time", 
                    "client_app_name", 
                    "server_principal_name", 
                    "database_name",
                    "batch_text",
                    "statement"
                ],
                "Events": [
                    "rpc_completed",
                    "sql_batch_completed"
                ]
            }
        ]
    }
}

Вы, наверное, заметили, что в конфигурации указаны некоторые заполнители, начинающиеся со знака доллара, например, $ServerName. Это глобальные переменные, которые позволяют повторно использовать один и тот же файл JSON в разных ситуациях. Вы можете подставить значения для глобальных переменных XESmartTarget из командной строки в форме –GlobalVariables key1=value1 key2=value2 … keyN=valueN

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

Стоит также отметить свойство AutoCreateTable. Оно определяет будет ли XESmartTarget пытаться создать целевую таблицу на основе колонок, указанных в свойстве OutputColumns. XESmartTarget попытается угадать правильные типы данных, но не сможет определить максимальную длину строк, поэтому они будут созданы как nvarchar(max). Если нужно более точное определение типов данных, лучше создать таблицу вручную и установить для этого свойства значение false.

Существует еще одно свойство, которое управляет поведением TableAppenderResponse, даже если оно не указано в файле конфигурации выше. UploadIntervalSeconds определяет, как часто XESmartTarget загружает данные в целевую таблицу. Значение по умолчанию — 10 секунд. Если вас это устраивает, вы можете не указывать его в файле конфигурации, в противном случае можно установить другое значение. Между загрузками данные хранятся в памяти, поэтому обязательно уменьшите этот интервал, если сеанс захватывает много событий.
Сохраните JSON файл как c:\temp\Recipe_02_Output_Table.json, и после этого можно запускать XESmartTarget. В нашем примере в командной строке передаётся имя сервера, указанное как глобальная переменная:

"%ProgramFiles%\XESmartTarget\xesmarttarget.exe" --File c:\temp\Recipe_02_Output_Table.json --GlobalVariables ServerName=(local)\SQLEXPRESS

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

Теперь проверим данные в базе простым запросом:

Результат запроса демонстрирует, что таблица создана и данные в нее записываются каждые 10 секунд. Это тот результат, который нелегко достичь с помощью встроенных возможностей.

Запись Extended Events в таблицу