sqlserver 扩展事件查询脚本

查询前一天 08:00:00 到 当天08:00:00 的慢查询及阻塞信息

declare @star_date varchar(100)
declare @end_date varchar(100)
declare @slow_file varchar(max)
declare @blocked_file varchar(max)


set @star_date = convert(varchar(10),dateadd(day,-1,getdate()),120) + ' 08:00:00'
set @end_date = convert(varchar(10),getdate(),120) + ' 08:00:00'

select @slow_file = st.target_data
from sys.dm_xe_sessions s
inner join sys.dm_xe_session_targets st
on s.address=st.event_session_address
where s.name='slow_query';

select @slow_file = fn from (SELECT SUBSTRING(inputString, startPos, endPos - startPos) AS fn
FROM (
SELECT
inputString,
CHARINDEX('name="', inputString) + LEN('name="') AS startPos,
CHARINDEX('"', inputString, CHARINDEX('name="', inputString) + LEN('name="')) AS endPos
FROM (
SELECT @slow_file AS inputString
) AS subquery
) AS subquery2) as a

select @blocked_file = st.target_data
from sys.dm_xe_sessions s
inner join sys.dm_xe_session_targets st
on s.address=st.event_session_address
where s.name='blocked_query';


select @blocked_file = fn from (SELECT SUBSTRING(inputString, startPos, endPos - startPos) AS fn
FROM (
SELECT
inputString,
CHARINDEX('name="', inputString) + LEN('name="') AS startPos,
CHARINDEX('"', inputString, CHARINDEX('name="', inputString) + LEN('name="')) AS endPos
FROM (
SELECT @blocked_file AS inputString
) AS subquery
) AS subquery2) as a

--select @slow_file
--select @blocked_file

--set @slow_file = 'E:\db_backup\event\s_query_0_133353445589010000.xel'
--set @blocked_file = 'E:\db_backup\event\b_query_0_133353445554800000.xel'


--select convert(varchar(10),dateadd(day,-1,getdate()),120)
--select @star_date,@end_date

SELECT
DATEADD(hour,8,[XML Data].value('(/event[@name=''sql_batch_completed'']/@timestamp)[1]','DATETIME')) AS [TimeStamp],
[XML Data].value('(/event/action[@name=''database_name'']/value)[1]','SYSNAME') AS [Database Name],
[XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','SYSNAME') AS [SQL_Text],
[XML Data].value('(/event/action[@name=''username'']/value)[1]','SYSNAME') AS username,
[XML Data].value('(/event/action[@name=''task_time'']/value)[1]','BIGINT') AS task_time,
[XML Data].value('(/event/data[@name=''duration'']/value)[1]','BIGINT') AS [Duration (us)],
[XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','BIGINT') AS [cpu_time],
[XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','BIGINT') AS physical_reads,
[XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','BIGINT') AS logical_reads,
[XML Data].value('(/event/data[@name=''writes'']/value)[1]','BIGINT') AS writes,
[XML Data].value('(/event/data[@name=''row_count'']/value)[1]','BIGINT') AS row_count,
[XML Data].value('(/event/data[@name=''batch_text'']/value)[1]','SYSNAME') AS batch_text
FROM
(SELECT CONVERT(XML, event_data) AS [XML Data]
FROM sys.fn_xe_file_target_read_file(@slow_file,NULL,NULL,NULL) where OBJECT_NAME='sql_batch_completed') AS v
where DATEADD(hour,8,[XML Data].value('(/event[@name=''sql_batch_completed'']/@timestamp)[1]','DATETIME'))
between @star_date and @end_date


SELECT
-- [XML DATA] AS [Raw XML],
DATEADD(hour,8,[XML Data].value('(/event[@name=''blocked_process_report'']/@timestamp)[1]','DATETIME')) AS [TimeStamp],
[XML Data].value('(/event/data[@name=''database_name'']/value)[1]','SYSNAME') AS [Database Name],
[XML Data].value('(/event/data[@name=''lock_mode'']/text)[1]','SYSNAME') AS [Lock Mode],
[XML Data].value('(/event/data[@name=''duration'']/value)[1]','BIGINT')/1024 AS [Duration (ms)],
[XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/@waitresource)[1]','SYSNAME') AS [Wait Resource],
[XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/@spid)[1]','SYSNAME') AS [Blocked Spid],
[XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/inputbuf)[1]','SYSNAME') AS [Blocked Query],
[XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]','SYSNAME') AS [Blocked sqlhandle],
[XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocking-process/process/@spid)[1]','SYSNAME') AS [Blocking Spid],
[XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocking-process/process/inputbuf)[1]','SYSNAME') AS [Blocking Query],
[XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[1]','SYSNAME') AS [Blocking sqlhandle]
FROM
(SELECT CONVERT(XML, event_data) AS [XML Data]
FROM sys.fn_xe_file_target_read_file(@blocked_file,NULL,NULL,NULL) where OBJECT_NAME='blocked_process_report') AS v
where DATEADD(hour,8,[XML Data].value('(/event[@name=''blocked_process_report'']/@timestamp)[1]','DATETIME'))
between @star_date and @end_date