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.
Explanation
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 (i.e., 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 for 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
Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.
- MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017