Set up Extended Events in SQL Server
Tags: sql, database, sqlserver
Published: 2020-10-01 11:03:12

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](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig1.png "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](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig2.png "Fig.2 The Deadlocks session")

![Fig.3 Events in the Deadlocks session](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig3.png "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](https://www.quest.com/products/spotlight-on-sql-server-enterprise/):
![Fig.4 Extended events in Spotlight](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig4.png "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](https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver15) or those from third-party companies like [Devart (Event Profiler for SQL Server)](https://www.devart.com/dbforge/sql/studio/sql-server-event-profiler.html):
![Fig.5 Selecting the tracing type-1](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig5.png "Fig.5 Selecting the tracing type-1")

![Fig.6 Selecting the tracing type-2](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig6.png "Fig.6 Selecting the tracing type-2")

![Fig.7 Selecting Execution](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig7.png "Fig.7 Selecting Execution")

![Fig.8 Selecting actions](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig8.png "Fig.8 Selecting actions")

You can also specify filters:
![Fig.9 Specifying tracing filters](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig9.png "Fig.9 Specifying tracing filters")

You can also configure what columns will be displayed:
![Fig.10 Selecting columns](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig10.png "Fig.10 Selecting columns")

An example of a compiled trace file:
![Fig.11 Tracing results](https://ondicesu.sirv.com/Images/Set%20up%20Extended%20Events%20in%20SQL%20Server/fig11.png "Fig.11 Tracing results")