SQL SERVER - EXTENDED EVENT监视错误和存储过程(二)
client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.request_id,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([object_name]=N'uspGetManagerEmployees')
)
ADD TARGET package0.ring_buffer
WITH
(
STARTUP_STATE=OFF
)
GO
[sql]
IF (OBJECT_ID('tempdb..#t') IS NOT NULL) DROP TABLE #t
IF (OBJECT_ID('tempdb..#r') IS NOT NULL) DROP TABLE #r
go
select
cast(target_data as xml) xdoc
into
#t
from
sys.dm_xe_sessions s
inner join
sys.dm_xe_session_targets t on t.event_session_address = s.address
where
s.name = 'monitor_procedure_performance'
;
with cte as
(
select
event_number = ROW_NUMBER() over (order by T.x),
event_timestamp = T.x.value('@timestamp', 'datetimeoffset'),
T.x.query('.') as event_data
from
#t
cross apply
xdoc.nodes('/RingBufferTarget/event') T(x)
),
cte2 as (
select
c.event_number,
c.event_timestamp,
--data_field = T2.x.value('local-name(.)', 'varchar(100)'),
data_name = T2.x.value('@name', 'varchar(100)'),
data_value = T2.x.value('value[1]', 'varchar(100)'),
data_text = T2.x.value('text[1]', 'varchar(max)')
from
cte c
cross apply
c.event_data.nodes('event/*') T2(x)
),
cte3 as (
select
*
from
cte2
where
data_name in ('collect_system_time', 'object_name', 'cpu_time', 'duration', 'logical_reads', 'row_count', 'database_name', 'database_id')
)
select
*
into
#r
from
cte3
pivot
(max(data_value) for data_name in (database_id, database_name, object_name, cpu_time, duration, logical_reads, row_count)) T
go
--SELECT * FROM #t
SELECT * FROM #r
go
select
execution_date = cast(event_timestamp as date),
execution_hour = datepart(hour, event_timestamp),
execution_minute = datepart(minute, event_timestamp),
[object_name],
duration_msec = avg(cast(duration as int)) / 1000.
from
#r
group by
cast(event_timestamp as date), datepart(hour, event_timestamp), datepart(minute, event_timestamp), [object_name]