SQLServer中使用扩展事件获取Session级别的等待信息及SQLServer 2016中Session级别等待信息的增强

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’CollectionSessionWaitStats’)
DROP EVENT session CollectionSessionWaitStats ON SERVER;
GO
— 创建事件会话
CREATE EVENT SESSION CollectionSessionWaitStats ON SERVER
ADD EVENT sqlserver.rpc_completed
(
ACTION
(
sqlos.task_time,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.username,
sqlserver.sql_text,
sqlserver.session_id,
sqlserver.transaction_id
) WHERE [duration]>=3000000
),
ADD EVENT sqlserver.sql_batch_completed
(
ACTION
(
sqlos.task_time,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.username,
sqlserver.sql_text,
sqlserver.session_id,
sqlserver.transaction_id
) WHERE [duration]>=3000000
),
ADD EVENT sqlos.wait_info
(
ACTION
(
sqlos.task_time,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.sql_text,
sqlserver.username,
sqlserver.session_id,
sqlserver.transaction_id
) WHERE session_id>50
and opcode=1
and duration>0
and sql_text not like ‘%sp_MScdc_capture_job%’ –排除某些信息
–and username = ” –限定只记录某些信息
),
ADD EVENT sqlos.wait_info_external
(
ACTION
(
sqlos.task_time,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.username,
sqlserver.sql_text,
sqlserver.session_id,
sqlserver.transaction_id
) WHERE session_id>50
and opcode=1
and duration>0
and sql_text not like ‘%sp_MScdc_capture_job%’
–and username = ”
)
ADD TARGET package0.event_file
(
SET filename=N’D:\XEventFiles\CollectionSessionWaitStats’,
max_file_size=(1024),
max_rollover_files=(10)
)
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON
)
GO
— 启用（停止）事件会话（START / STOP）
ALTER EVENT SESSION CollectionSessionWaitStats ON SERVER STATE=START
GO

— Parse the XML to show rpc_completed,sql_batch_completed details
if object_id(‘tempdb..#t1’) is not null
drop table #t1
SELECT
event_xml.value(‘(./action[@name=”session_id”]/value)[1]’, ‘INT’) as session_id,
event_xml.value(‘(./@timestamp)’, ‘varchar(1000)’) as timestamp,
event_xml.value(‘(./data[@name=”statement”]/value)[1]’, ‘varchar(max)’) as statement,
event_xml.value(‘(./data[@name=”batch_text”]/value)[1]’, ‘varchar(max)’) as batch_text,
event_xml.value(‘(./@name)’, ‘varchar(1000)’) as Event_Name,
event_xml.value(‘(./data[@name=”duration”]/value)[1]’, ‘bigint’) as Duration,
event_xml.value(‘(./data[@name=”cpu_time”]/value)[1]’, ‘bigint’) as cpu_time,
event_xml.value(‘(./data[@name=”physical_reads”]/value)[1]’, ‘bigint’) as physical_reads,
event_xml.value(‘(./data[@name=”logical_reads”]/value)[1]’, ‘bigint’) as logical_reads,
event_xml.value(‘(./action[@name=”username”]/value)[1]’, ‘varchar(max)’) as username
INTO #t1
FROM (
SELECT CAST(event_data AS XML) xml_event_data
FROM sys.fn_xe_file_target_read_file(N’D:\XEventFiles\CollectionSessionWaitStats*’, NULL, NULL, NULL)
) AS event_table
CROSS APPLY xml_event_data.nodes(‘//event’) n (event_xml)
WHERE event_xml.value(‘(./@name)’, ‘varchar(1000)’) in (‘rpc_completed’,’sql_batch_completed’)
order by Event_Name
— Parse the XML to show wait_info,wait_info_external details
if object_id(‘tempdb..#t2’) is not null
drop table #t2
SELECT
cast(event_xml.value(‘(./@timestamp)’, ‘varchar(1000)’) as DATETIME2) as timestamp,
event_xml.value(‘(./data[@name=”duration”]/value)[1]’, ‘bigint’) as duration,
event_xml.value(‘(./action[@name=”session_id”]/value)[1]’, ‘INT’) as session_id,
event_xml.value(‘(./data[@name=”wait_type”]/text)[1]’, ‘VARCHAR(200)’) as wait_type
INTO #t2
FROM (
SELECT CAST(event_data AS XML) xml_event_data
FROM sys.fn_xe_file_target_read_file(N’D:\XEventFiles\CollectionSessionWaitStats*’, NULL, NULL, NULL)
) AS event_table
CROSS APPLY xml_event_data.nodes(‘//event’) n (event_xml)
WHERE event_xml.value(‘(./@name)’, ‘varchar(1000)’) in (‘wait_info’,’wait_info_external’)
if object_id(‘tempdb..#t3’) is not null
drop table #t3
SELECT
a.session_id AS SessionId,
isnull(statement,batch_text) AS SQLTEXT,
a.Duration AS TotalExecuteTime,
CAST(a.timestamp AS DATETIME2) AS CompletedTime,
CAST(b.timestamp AS DATETIME2) AS WaitTypeStartTime,
b.wait_type AS WaitType,
b.duration AS WaitDuration
INTO #t3
FROM #t1 a INNER JOIN #t2 b on a.session_id = b.session_id
and b.timestamp < a.timestamp
and b.timestamp>(
select top 1 timestamp from #t1 c
where a.session_id = a.session_id and a.timestamp > b.timestamp
order by a.timestamp
)
select
case when rn = 1 then SessionId else NULL end as SessionId,
case when rn = 1 then SQLTEXT else NULL end as SQLTEXT,
case when rn = 1 then TotalExecuteTime else NULL end as TotalExecuteTime,
CompletedTime as CompletedTime,
WaitType,
WaitTypeStartTime as WaitTypeStartTime,
WaitDuration
from
(
select ROW_NUMBER()over(partition by SessionId,SQLTEXT,TotalExecuteTime,CompletedTime order by CompletedTime,WaitTypeStartTime) as rn,
* FROM #t3
)