The default extended event system health captures lot of useful information likeclr_allocation_failure
clr_virtual_alloc_failure
memory_broker_ring_buffer_recorded
memory_node_oom_ring_buffer_recorded
scheduler_monitor_deadlock_ring_buffer_recorded,
scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,
scheduler_monitor_non_yielding_ring_buffer_recorded,
scheduler_monitor_non_yielding_rm_ring_buffer_recorded,
scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,
scheduler_monitor_system_health_ring_buffer_recorded,
connectivity_ring_buffer_recorded
error_reported
security_error_ring_buffer_recorded
xml_deadlock_report
clr_virtual_alloc_failure
memory_broker_ring_buffer_recorded
memory_node_oom_ring_buffer_recorded
scheduler_monitor_deadlock_ring_buffer_recorded,
scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,
scheduler_monitor_non_yielding_ring_buffer_recorded,
scheduler_monitor_non_yielding_rm_ring_buffer_recorded,
scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,
scheduler_monitor_system_health_ring_buffer_recorded,
connectivity_ring_buffer_recorded
error_reported
security_error_ring_buffer_recorded
xml_deadlock_report
We can verify them using below scripts
SELECT *, CAST([target_data] as XML) [target_data] FROM sys.dm_xe_session_targets a INNER JOIN sys.dm_xe_sessions b ON b.[address] = a.[event_session_address] --WHERE b.[name] = 'system_health' CREATE EVENT SESSION [system_health] ON SERVER ADD EVENT sqlclr.clr_allocation_failure( ACTION(package0.callstack,sqlserver.session_id)), ADD EVENT sqlclr.clr_virtual_alloc_failure( ACTION(package0.callstack,sqlserver.session_id)), ADD EVENT sqlos.memory_broker_ring_buffer_recorded, ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded( ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded, ADD EVENT sqlos.wait_info( ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text) WHERE ([duration]>(15000) AND ([wait_type]>(31) AND ([wait_type]>(47) AND [wait_type]<(54) OR [wait_type]<(38) OR [wait_type]>(63) AND [wait_type]<(70) OR [wait_type]>(96) AND [wait_type]<(100) OR [wait_type]=(107) OR [wait_type]=(113) OR [wait_type]>(174) AND [wait_type]<(179) OR [wait_type]=(186) OR [wait_type]=(207) OR [wait_type]=(269) OR [wait_type]=(283) OR [wait_type]=(284)) OR [duration]>(30000) AND [wait_type]<(22)))), ADD EVENT sqlos.wait_info_external( ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text) WHERE ([duration]>(5000) AND ([wait_type]>(365) AND [wait_type]<(372) OR [wait_type]>(372) AND [wait_type]<(377) OR [wait_type]>(377) AND [wait_type]<(383) OR [wait_type]>(420) AND [wait_type]<(424) OR [wait_type]>(426) AND [wait_type]<(432) OR [wait_type]>(432) AND [wait_type]<(435) OR [duration]>(45000) AND ([wait_type]>(382) AND [wait_type]<(386) OR [wait_type]>(423) AND [wait_type]<(427) OR [wait_type]>(434) AND [wait_type]<(437) OR [wait_type]>(442) AND [wait_type]<(451) OR [wait_type]>(451) AND [wait_type]<(473) OR [wait_type]>(484) AND [wait_type]<(499) OR [wait_type]=(365) OR [wait_type]=(372) OR [wait_type]=(377) OR [wait_type]=(387) OR [wait_type]=(432) OR [wait_type]=(502))))), ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)), ADD EVENT sqlserver.error_reported( ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack) WHERE ([severity]>=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902)))), ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)), ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1) WHERE ([sqlserver].[is_system]=(1) AND [component]<>(4))), ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)), ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO