SQL SERVER - EXTENDED EVENT 监视异常和存储过程

SQL SERVER - EXTENDED EVENT 监视错误和存储过程

监视错误

CREATE EVENT SESSION [error_trap] ON SERVER 
ADD EVENT sqlserver.error_reported 
    ( 
        ACTION    (package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username) 
        WHERE    ([severity]>10) 
    ) 
ADD TARGET package0.event_file 
    ( 
        SET filename=N'D:\MSSQL\XEvents\error_trap.xel' 
    ) 
WITH 
    (        
        STARTUP_STATE=OFF 
    ) 
GO

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

 

IF (OBJECT_ID('tempdb..#e') IS NOT NULL) DROP TABLE #e 
go

WITH cte AS 
( 
    SELECT 
        CAST(event_data AS XML) AS event_data 
    FROM 
        sys.fn_xe_file_target_read_file('D:\MSSQL\XEvents\error_trap*.xel', NULL, NULL, NULL) 
), 
cte2 AS 
( 
    SELECT 
        event_number = ROW_NUMBER() OVER (ORDER BY T.x) 
    ,    event_name = T.x.value('@name', 'varchar(100)') 
    ,    event_timestamp = T.x.value('@timestamp', 'datetimeoffset') 
    ,    event_data 
    FROM 
        cte    
    CROSS APPLY 
        event_data.nodes('/event') T(x) 
) 
SELECT * INTO #e FROM cte2 
go

WITH cte3 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(max)'), 
        data_text = T2.x.value('text[1]', 'varchar(max)') 
    FROM 
        #e c 
    CROSS APPLY 
        c.event_data.nodes('event/*') T2(x) 
), 
cte4 AS 
( 
    SELECT 
        * 
    FROM 
        cte3 
    WHERE 
        data_name IN ('error_number', 'severity', 'message', 'database_name', 'database_id', 'client_hostname', 'client_app_name', 'collect_system_time', 'username') 
) 
SELECT 
    * 
FROM 
    cte4 
PIVOT 
    (MAX(data_value) FOR data_name IN ([error_number], [severity], [message], database_name, database_id, username, client_hostname, client_app_name, collect_system_time)) T 
WHERE 
    [severity] > 10 
ORDER BY 
    event_timestamp DESC 
go 

 

监视存储过程

CREATE EVENT SESSION [monitor_procedure_performance] ON SERVER 
ADD EVENT sqlserver.rpc_completed 
    ( 
        ACTION    (package0.collect_system_time,package0.process_id,sqlserver.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 EVENT sqlserver.module_end 
    ( 
        ACTION    (package0.collect_system_time,package0.process_id,sqlserver.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

 

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]