By: Ben Snaidero
Overview
In most cases when I setup a custom extended event session it’s a one-time thing as I am looking into a fairly specific issue but if you don’t have any other monitoring tools in place it is a good idea to have a session or sessions in place, other than the system_health session, that can give you an idea of the health of your SQL Server instance and give you some historical data to check when someone comes to you and says the database was slower earlier in the day.
The following templates cover some of the areas you might want to investigate. Note that in some cases we add filtering so as not to capture all events (ie. we don’t need to track really fast queries) and in all cases we set a limit on the amount of target data collected.
SQL Server Extended Events to Monitor System Activity
The following script will create an extended events session to monitor activity such as creating and deleting objects, page life expectancy, errors, memory status and more.
CREATE EVENT SESSION [System_Activity] ON SERVER ADD EVENT sqlserver.background_job_error, ADD EVENT sqlserver.buffer_node_page_life_expectancy, ADD EVENT sqlserver.checkpoint_end, ADD EVENT sqlserver.database_file_size_change, ADD EVENT sqlserver.databases_log_file_size_changed, ADD EVENT sqlserver.databases_log_flush_wait, ADD EVENT sqlserver.error_reported, ADD EVENT sqlserver.long_io_detected, ADD EVENT sqlserver.memory_manager_database_cache_memory, ADD EVENT sqlserver.memory_manager_free_memory, ADD EVENT sqlserver.memory_manager_target_server_memory, ADD EVENT sqlserver.memory_manager_total_server_memory, ADD EVENT sqlserver.object_altered, ADD EVENT sqlserver.object_created, ADD EVENT sqlserver.object_deleted, ADD EVENT sqlserver.page_split, ADD EVENT sqlserver.server_start_stop, ADD EVENT sqlserver.sort_warning ADD TARGET package0.event_file(SET filename=N'System_Activity',max_file_size=(20));
SQL Server Extended Events to Monitor Blocking
The following script will create an extended events session to monitor blocking activity.
CREATE EVENT SESSION [Blocked_Process_Report] ON SERVER ADD EVENT sqlserver.blocked_process_report ADD TARGET package0.event_file(SET filename=N'Blocked_Process_Report',max_file_size=(10));
SQL Server Extended Events to Monitor Long Running Queries
The following script will create an extended events session to long running queries.
CREATE EVENT SESSION [Long_Running_Queries] ON SERVER ADD EVENT sqlserver.rpc_completed( WHERE ([duration]>(1000000))), ADD EVENT sqlserver.sql_batch_completed( WHERE ([duration]>(1000000))), ADD EVENT sqlserver.sql_statement_completed( WHERE ([duration]>(1000000))) ADD TARGET package0.event_file(SET filename=N'Long_Running_Queries',max_file_size=(10));
SQL Server Extended Events to Monitor Wait Events
The following script will create an extended events session to wait events on the server.
CREATE EVENT SESSION [Wait_Statistics] ON SERVER ADD EVENT sqlos.wait_completed( ACTION(package0.callstack) WHERE ([duration]>(5))), ADD EVENT sqlos.wait_info( ACTION(package0.callstack) WHERE ([duration]>(5))) ADD TARGET package0.event_file(SET filename=N'Wait_Statistics',max_file_size=(10));
Additional Information
- Using Extended Events to Monitor SQL Server Availability Groups
- Capturing SQL Server Deadlocks Using Extended Events
- Using Extended Events to Troubleshoot SQL Server Issues
