监视错误
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 ) GOALTER EVENT SESSION [error_trap] ON SERVER STATE = START; GO
IF (OBJECT_ID('tempdb..#e') IS NOT NULL) DROP TABLE #e goWITH 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([email protected]', 'varchar(100)') , event_timestamp = T.x.value([email protected]', 'datetimeoffset') , event_data FROM cte CROSS APPLY event_data.nodes('/event') T(x) ) SELECT * INTO #e FROM cte2 goWITH cte3 AS ( SELECT c.event_number, c.event_timestamp, --data_field = T2.x.value('local-name(.)', 'varchar(100)'), data_name = T2.x.value([email protected]', '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 goselect 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([email protected]', '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([email protected]', '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 goselect 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]