Performance tuning is the process of analyzing query plans, execution plans, indexes, partitioning, etc. and finding ways to reduce the time a query takes or reduce resource usage by that query. There are 4 events that I found can help you to identify queries that can be performance tuned. They are deadlocks, application aborts, blocking, and long running queries.

Prior to the release of SQL 2012 the best way to identify and capture these events was through SQL Profiler traces. There are some issues using traces however. First, traces impact the SQL Servers overall performance as they uses resources like CPU and bandwidth. Second, traces are sensitive to network latency and do not automatically restart after a server restart. Third, it is difficult to build reports using traces. Finally, saving traces to a table is a huge resource hog and working with saved files is difficult.

Extended Events solves these issues with using Profiler trace. The fully supported version of Extended Events was released with SQL 2012. Note: A subset of functionality was released with SQL 2008. Configured correctly an Extended Event session has very little impact on server performance, it can automatically restart with a server restart, it generates XML documents that can be shredded into readable tables, and saving event data to files is easy and configurable.

In this, Part 1, article are described the 4 Extended Events, their purpose, and configuration. In part 2, we’ll move on to describe the stored procedures used to retrieve and shred the saved XML data and build an aggregate report.

Extended Event Sessions Common Configuration

Each of the XE sessions shown below have a set of common configuration settings. These common settings have been configured to control resource usage on the server and optimize speed of querying the XE data on my servers. These settings may be different on your servers. Please refer to MSDN article https://msdn.microsoft.com/en-us/library/bb630282(v=sql.120).aspx for details.

File size and number of rollover files (max_file_size, max_rollover_files)

Limiting the size of the file that stores the events and the number of rollover files is important. The larger the file and the more rollover files that exist the longer it takes to query the event data. I have set mine to 250MB maximum file size with 3 rollover files for a total maximum of 3/4GB of event data for each XE session. Note that once all the rollover files are filled data begins to be dropped off in FIFO (first in – first out) order.

Losing Event Data (EVENT_RETENTION_MODE)

When the SQL server is under pressure it is possible for Extended Event sessions to cause performance issues. Allowing the loss of some event data will reduce that impact. This setting can be set to no event loss (highest impact on performance), 1 event loss (acceptable impact), and multiple event loss (minimal impact). I have chosen to allow a single event loss because I feel it’s important to minimize performance impact but I need to collect as much data as possible especially when the server is having issues.

Automatic restart (STARTUP_STATE)

This setting determines if the Extended Event sessions automatically restarts when the SQL server service is started up. I have this feature turned on.

Deadlocks

Arguably the most important of the 4 Extended Event (XE) sessions above is the Deadlock. Because the result of a deadlock means that some transaction has been rolled back or killed it is vital to investigate all deadlocks and determine if there is a way to avoid them.

Deadlock Extended Event Session:

CREATE EVENT SESSION [Deadlocks] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'Deadlocks', max_file_size=(250), max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [Deadlocks] ON SERVER STATE = START
GO

The XE session above is set to record events from xml_deadlock_report . The Deadlocks XE gives detailed information about the deadlock including its cause, which process(s) were the victim and which contributed, what query was being run, the type of lock, and what specifically was being locked, among others.

Application Abort (A.K.A. The Time Out)

Application abort or “time out” is the most actionable of the 4 Extended Events. This event tells you that the calling application has waited the maximum amount of time allowed and is requesting that the call be killed. The application abort request is generated by the calling application and not by SQL. Every query or stored procedure that appears in the Application Abort list is a candidate for performance tuning. However, you must also reconcile these time outs with the Blocking XE (explained below) to ensure that this call is the root cause.

Note: SQL does not have a time out period. Regardless of how long a query takes to complete SQL will continue to run it, unless the calling process sends the abort command. The Application Abort is a fairly common occurrence for databases that are connected to a web application.

Application Abort Extended Event Session:

CREATE EVENT SESSION [TimeOuts] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_output_parameters=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id)
    WHERE ([result]=(2)))
ADD TARGET package0.event_file(SET filename=N'TimeOuts',max_file_size=(250),max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [TimeOuts] ON SERVER STATE = START
GO

The Application Abort XE session is based on the rpc_completed event and is filtered to where the returned error id is 2 (Application Abort). This XE sessions captures information about the calling application, database name, duration, cpu time, read/write information, plan handle, and query call, among others.

Blocking

The Blocking XE session is essential in identifying the root cause of many performance issues including deadlocks and application aborts. Blocking is a normal occurrence and in most cases is not a problem. However, when blocking last for an extended period of time it can cause application aborts to occur and can cause cascading blocking, which can result in deadlocks. So, investigating blocking events in conjunction with deadlocks and application aborts is useful in determining a root cause. Also, analyzing lead blockers can point to performance tuning opportunities.

A blocking event occurs when the duration of blocking exceeds the configuration value set for “blocked process threshold (s)”. Use sp_configure to change the block duration threshold as shown below.

The example below changes the blocked process threshold to 20 seconds:

EXEC sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'blocked process threshold',20;
GO
RECONFIGURE;
GO

Blocking Extended Event Session:

CREATE EVENT SESSION [Blocking] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file(SET filename=N'Blocking',max_file_size=(250),max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [Blocking] ON SERVER STATE = START
GO

The Blocking XE session is based on the blocked_process_report event and captures information about the duration of blocking, which processes were blocked and which caused the blocking, wait resource, isolation level, and the SQL text, among others.

Long Running Queries

The Long Running Queries XE session fires whenever a query or stored procedure exceeds a specified duration threshold. This XE session is useful in analyzing what queries are taking a long time to complete and if there are any performance tuning opportunities available.

Long Running Queries Extended Event Session:

CREATE EVENT SESSION [DurationGT25Seconds] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_output_parameters=(1),collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id)
    WHERE ([duration]>(25000000)))
ADD TARGET package0.event_file(SET filename=N'DurationGT25Seconds',max_file_size=(250),max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [DurationGT25Seconds] ON SERVER STATE = START
GO

The Long Running XE captures events from the rpc_completed event. It is filtered down to events that exceed 25 seconds. This XE sessions captures information about the duration of the call, the cpu time, read/write counts, the plan handle, and SQL text, among others.

Extended Events can be very useful in finding performance tuning opportunities. In Part 2 I’ll describe the stored procedure used to retrieve and shred the XML data. In Part 3 I’ll show how to build an aggregate report to find performance tuning opportunities.