Extended events are used to monitor the DBMS’s state.
There is a standard preconfigured session called system_health, and the most important criteria are gathered in it:
Despite deadlocks being collected in the system_health session, it’s often handy to create a separate session for this:
The code for Deadlocks session creation will be similar to this:
CREATE EVENT SESSION [Deadlocks] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'Deadlocks')
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
Extended events can also be created by a popular monitor called Spotlight:
The code for Spotlight session creation will be similar to this:
CREATE EVENT SESSION [SpotlightMonitoring_com_sqlserver] ON SERVER
ADD EVENT sqlos.wait_info(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.plan_handle,sqlserver.session_id,sqlserver.tsql_frame,sqlserver.username)
WHERE ([sqlserver].[is_system]=(0) AND [opcode]=(1) AND ([duration]>(0) OR [signal_duration]>(0)) AND [wait_type]<>(755) AND [wait_type]<>(177) AND [wait_type]<>(413) AND [wait_type]<>(285) AND [wait_type]<>(359) AND [wait_type]<>(137) AND [wait_type]<>(132) AND [wait_type]<>(232) AND [wait_type]<>(231) AND [wait_type]<>(230) AND [wait_type]<>(356) AND [wait_type]<>(141) AND [wait_type]<>(140) AND [wait_type]<>(143) AND [wait_type]<>(144) AND [wait_type]<>(121) AND [wait_type]<>(813) AND [wait_type]<>(393) AND [wait_type]<>(296) AND [wait_type]<>(801) AND [wait_type]<>(839) AND [wait_type]<>(382) AND [wait_type]<>(151) AND [wait_type]<>(651) AND [wait_type]<>(767) AND [wait_type]<>(661) AND [wait_type]<>(855) AND [wait_type]<>(775) AND [wait_type]<>(857) AND [wait_type]<>(856) AND [wait_type]<>(884) AND [wait_type]<>(871) AND [wait_type]<>(854) AND [wait_type]<>(859) AND [wait_type]<>(858) AND [wait_type]<>(853) AND [wait_type]<>(873) AND [wait_type]<>(881) AND [wait_type]<>(782) AND [wait_type]<>(780) AND [wait_type]<>(841) AND [wait_type]<>(798) AND [wait_type]<>(799) AND [wait_type]<>(130) AND [wait_type]<>(786) AND [wait_type]<>(96) AND [wait_type]<>(816) AND [wait_type]<>(605) AND [wait_type]<>(1021) AND [wait_type]<>(991) AND [wait_type]<>(993) AND [wait_type]<>(883) AND [wait_type]<>(131) AND [wait_type]<>(283) AND [wait_type]<>(335) AND [wait_type]<>(347) AND [wait_type]<>(273) AND [wait_type]<>(100) AND [wait_type]<>(102) AND [wait_type]<>(107) AND [wait_type]<>(105) AND [wait_type]<>(109) AND [wait_type]<>(108) AND [wait_type]<>(754) AND [wait_type]<>(165) AND [wait_type]<>(348) AND [wait_type]<>(756) AND [wait_type]<>(392) AND [wait_type]<>(221) AND [wait_type]<>(800) AND [wait_type]<>(897) AND [wait_type]<>(358) AND [wait_type]<>(202) AND [wait_type]<>(32) AND [wait_type]<>(868) AND [wait_type]<>(409) AND [wait_type]<>(752) AND [wait_type]<>(394) AND [wait_type]<>(398) AND [wait_type]<>(407) AND [wait_type]<>(408) AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring 2)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server XEventsReader (Monitoring)'))),
ADD EVENT sqlos.wait_info_external(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.plan_handle,sqlserver.session_id,sqlserver.tsql_frame,sqlserver.username)
WHERE ([sqlserver].[is_system]=(0) AND [opcode]=(1) AND [duration]>(0) AND [wait_type]<>(755) AND [wait_type]<>(177) AND [wait_type]<>(413) AND [wait_type]<>(285) AND [wait_type]<>(359) AND [wait_type]<>(137) AND [wait_type]<>(132) AND [wait_type]<>(232) AND [wait_type]<>(231) AND [wait_type]<>(230) AND [wait_type]<>(356) AND [wait_type]<>(141) AND [wait_type]<>(140) AND [wait_type]<>(143) AND [wait_type]<>(144) AND [wait_type]<>(121) AND [wait_type]<>(813) AND [wait_type]<>(393) AND [wait_type]<>(296) AND [wait_type]<>(801) AND [wait_type]<>(839) AND [wait_type]<>(382) AND [wait_type]<>(151) AND [wait_type]<>(651) AND [wait_type]<>(767) AND [wait_type]<>(661) AND [wait_type]<>(855) AND [wait_type]<>(775) AND [wait_type]<>(857) AND [wait_type]<>(856) AND [wait_type]<>(884) AND [wait_type]<>(871) AND [wait_type]<>(854) AND [wait_type]<>(859) AND [wait_type]<>(858) AND [wait_type]<>(853) AND [wait_type]<>(873) AND [wait_type]<>(881) AND [wait_type]<>(782) AND [wait_type]<>(780) AND [wait_type]<>(841) AND [wait_type]<>(798) AND [wait_type]<>(799) AND [wait_type]<>(130) AND [wait_type]<>(786) AND [wait_type]<>(96) AND [wait_type]<>(816) AND [wait_type]<>(605) AND [wait_type]<>(1021) AND [wait_type]<>(991) AND [wait_type]<>(993) AND [wait_type]<>(883) AND [wait_type]<>(131) AND [wait_type]<>(283) AND [wait_type]<>(335) AND [wait_type]<>(347) AND [wait_type]<>(273) AND [wait_type]<>(100) AND [wait_type]<>(102) AND [wait_type]<>(107) AND [wait_type]<>(105) AND [wait_type]<>(109) AND [wait_type]<>(108) AND [wait_type]<>(754) AND [wait_type]<>(165) AND [wait_type]<>(348) AND [wait_type]<>(756) AND [wait_type]<>(392) AND [wait_type]<>(221) AND [wait_type]<>(800) AND [wait_type]<>(897) AND [wait_type]<>(358) AND [wait_type]<>(202) AND [wait_type]<>(32) AND [wait_type]<>(868) AND [wait_type]<>(409) AND [wait_type]<>(752) AND [wait_type]<>(394) AND [wait_type]<>(398) AND [wait_type]<>(407) AND [wait_type]<>(408) AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring 2)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server XEventsReader (Monitoring)'))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(0)
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.username)
WHERE ([sqlserver].[is_system]=(0) AND [duration]>(0) AND ([physical_reads]>(0) OR [logical_reads]>(0) OR [writes]>(0) OR [cpu_time]>(0)) AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring 2)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server XEventsReader (Monitoring)'))),
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(0)
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.username)
WHERE ([sqlserver].[is_system]=(0) AND [duration]>(0) AND ([physical_reads]>(0) OR [logical_reads]>(0) OR [writes]>(0) OR [cpu_time]>(0)) AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring 2)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server XEventsReader (Monitoring)'))),
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer(SET max_events_limit=(1000),occurrence_number=(1000))
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=15 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_NODE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
We can also use other profilers, such as the one from Microsoft or those from third-party companies like Devart (Event Profiler for SQL Server):
You can also specify filters:
You can also configure what columns will be displayed:
An example of a compiled trace file:
Top comments (1)
The scripts can be shorter and more readable if these "...[wait_type]<>(X) AND [wait_type]<>(Y) ..." replace with "[wait_type] not in(X,Y,...)"