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

Для начала мы можем создать сеанс расширенных событий с использованием T-SQL. Следующий скрипт захватывает все SQL-пакеты, выполняемые на сервере, которые выполняют более 200 000 чтений:

USE [master];
GO
CREATE EVENT SESSION [QueriesWith200kReads] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([logical_reads]>200000))
ADD TARGET package0.event_file(SET filename=N'C:\SQLServer\XEvents\QueriesWith200kReads.xel')
GO

После создания сеанса его можно запустить с помощью следующей команды:

ALTER EVENT SESSION [QueriesWith200kReads]
ON SERVER
STATE = START;
GO

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

SELECT COUNT(*)
FROM sys.fn_xe_file_target_read_file
 ('C:\SQLServer\XEvents\QueriesWith200kReads*.xel',NULL,NULL,NULL);
GO

Для запроса захваченных данных мы можем использовать XQUERY. Следующий скрипт извлекает все данные из сеанса и предоставляет общий вид запросов:

WITH CTE_ExecutedSQLStatements AS
(SELECT
[XML Data],
[XML Data].value('(/event[@name=''sql_statement_completed'']/@timestamp)[1]','DATETIME')AS [Time],
[XML Data].value('(/event/data[@name=''duration'']/value)[1]','int')AS [Duration],
[XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','int')AS [CPU],
[XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','int')AS [logical_reads],
[XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','int')AS [physical_reads],
[XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')AS [SQL Statement]
FROM
(SELECT 
OBJECT_NAME AS [Event], 
CONVERT(XML, event_data) AS [XML Data]
FROM 
sys.fn_xe_file_target_read_file
('C:\SQLServer\XEvents\QueriesWith200kReads*.xel',NULL,NULL,NULL)) as v)
SELECT
[SQL Statement]AS [SQL Statement],
SUM(Duration)AS [Total Duration],
SUM(CPU)AS [Total CPU],
SUM(Logical_Reads)AS [Total Logical Reads],
SUM(Physical_Reads) AS [Total Physical Reads]
FROM
CTE_ExecutedSQLStatements
GROUP BY
[SQL Statement]
ORDER BY
[Total Logical Reads] DESC
GO

Если мы хотим изменить сеанс, мы можем использовать графический интерфейс, введенный в SQL Server 2012. В Обозревателе объектов перейдите в Управление > Расширенные события > Сеансы. Щелкните правой кнопкой мыши на сеансе и перейдите к свойствам. Во вкладке События нажмите настроить, а затем перейдите на вкладку фильтр. Здесь мы можем добавить дополнительные фильтры, чтобы включить запросы, выполняющие большое количество физических чтений.

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

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