DEV Community

Cover image for Set up Extended Events in SQL Server
Evgeniy Gribkov
Evgeniy Gribkov

Posted on

Set up Extended Events in SQL Server

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:
Fig.1 system_health session

Despite deadlocks being collected in the system_health session, it’s often handy to create a separate session for this:

Fig.2 The Deadlocks session

Fig.3 Events in the Deadlocks session

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:
Fig.4 Extended events in 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):
Fig.5 Selecting the tracing type-1

Fig.6 Selecting the tracing type-2

Fig.7 Selecting Execution

Fig.8 Selecting actions

You can also specify filters:
Fig.9 Specifying tracing filters

You can also configure what columns will be displayed:
Fig.10 Selecting columns

An example of a compiled trace file:
Fig.11 Tracing results

Top comments (1)

Collapse
 
oleg26dev profile image
Oleg26Dev

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,...)"