Sample SQL Server Extended Events Sessions Templates



By:
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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools