Sample SQL Server Extended Events Sessions Templates

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

Last Update: 6/14/2019

Sample SQL Server Extended Events Sessions Templates

MENU