SQL Server Extended Events

What is an Extended Event?

     An Extended Events is a SQL Server tool that allows the DBA to monitor what’s going on in a SQL Server instance.  It monitors, very much like its predecessor, the SQL Profiler, a way to granularly capture events in the SQL environment.  But unlike SQL Server Profiler and SQL Trace it has several great benefits: it has little performance impact, the DBA does not need to write code to extract data and there is an easy front end graphical user interface.

     Extended Events was introduced in SQL Server 2008, but with no GUI that interfaced with the events directly it made the task for the DBA to write complex code to gather data.  In version 2012 and beyond, the introduction of the GUI has made the life of a DBA a lot easier.

Benefits of using the Extended Events:

  • Extended Events built into SQL Server Management Studio
  • Extended Events sessions can be created without any T-SQL commands or query XML data
  • Hardly any overhead when using them on the SQL Server
  • Less than 2% of the CPU’s resource
  • Replaces SQL Profiler and SQL Traces
  • Easy to use and powerful (wizard driven)

The reason to use Extended Events

  • Finding long-running queries
  • Tracking DDL operations
  • Find missing statistics
  • Resolving and finding blocking and deadlocking
  • Queries that cause specific wait stats to occur
  • Monitoring SQL Server memory stress

Terminology:

Package

A package is a container which contains all the extended events objects; like Events, Actions, Targets, and Predicates

Events

SQL categories of event driven data for analysis

Actions

When an event is fired a response to that event is actions.

Targets

The consumers of the events are called Targets.  For example, buffers to disk in the files and Ring Buffer which holds the event data in

Predicates

Filter for events

Session

A session is way of grouping events, their associated actions and predicates for filtering and different targets to process event firing.


--Create database 4MB in size

CREATE DATABASE [SQLSize]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SQLSize', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQLSize.mdf' , 
SIZE = 4096KB ,        --<< original size 4MB
FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SQLSize_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQLSize_log.ldf' , 
SIZE = 1024KB , 
FILEGROWTH = 10%)
GO


--Alter the size of the database

USE [master]
GO

ALTER DATABASE [SQLSize] 
MODIFY FILE ( NAME = N'SQLSize', SIZE = 20480KB )
GO

ALTER DATABASE [SQLSize] 
MODIFY FILE ( NAME = N'SQLSize_log', SIZE = 5120KB )
GO

--scripted out session

CREATE EVENT SESSION [Altered Database Size] ON SERVER 
ADD EVENT sqlserver.database_file_size_change(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[database_name]=N'SQLSize')) 
ADD TARGET package0.event_file(SET filename=N'SQLSize')
WITH (STARTUP_STATE=OFF)
GO






--Extended Event Packages

SELECT pkg.name, pkg.description, mod.* 
FROM sys.dm_os_loaded_modules mod 
INNER JOIN sys.dm_xe_packages pkg 
ON mod.base_address = pkg.module_address 


--Package events

select pkg.name as PackageName, obj.name as EventName 
from sys.dm_xe_packages pkg 
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid 
where obj.object_type = 'event' 
order by 1, 2 


--Package wise actions

select pkg.name as PackageName, obj.name as ActionName 
from sys.dm_xe_packages pkg 
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid 
where obj.object_type = 'action' 
order by 1, 2 

--Package wise targets

select pkg.name as PackageName, obj.name as TargetName 
from sys.dm_xe_packages pkg 
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid 
---where obj.object_type = 'target' 
order by 1, 2 


--Package wise predicates

select pkg.name as PackageName, obj.name as PredicateName 
from sys.dm_xe_packages pkg 
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid 
where obj.object_type = 'pred_source' 
order by 1, 2 


--Event session with its events, actions and targets

SELECT sessions.name AS SessionName, sevents.package as PackageName, 
sevents.name AS EventName, 
sevents.predicate, sactions.name AS ActionName, stargets.name AS TargetName 
FROM sys.server_event_sessions sessions 
INNER JOIN sys.server_event_session_events sevents 
ON sessions.event_session_id = sevents.event_session_id 
INNER JOIN sys.server_event_session_actions sactions 
ON sessions.event_session_id = sactions.event_session_id 
INNER JOIN sys.server_event_session_targets stargets 
ON sessions.event_session_id = stargets.event_session_id 
WHERE sessions.name = 'database size' 
GO 

Creating an extended event session

SQL Server Extended Events can be used also for SQL Server auditing purposes. For example, you can create a SQL Server Extended Events session that audits both the succeeded and failed login processes. To do that, expand the Extended Events option under the Management node, right-click on the Sessions option and choose New Session…, as below:

On the displayed New Session window, provide a meaningful name for the new session, which is Audit_Demo in our example, and set the appropriate scheduling settings from the available options, as shown below:

The New Extended Events Session wizards allows you to choose from the available default events templates, similar to the SQL Server Profiler templates, as shown below:

Or click on the Events tab, to customize your own session and choose the events that you are managed to monitor. In our example here, we will choose the Login event to track the successful login processes and the Error_Reported event to collect the failed logins as follows:

Double-clicking on the selected event will move you to a new window, on which you can customize the columns that will be recorded and received for that event. For example, we are interested in retrieving specific global information about the successful login process, as shown below:

Auditing for failed logins

For the failed login processes, we need to filter on the 18456 SQL Server error message, that is returned when a connection attempt is rejected because of an authentication failure that involves a bad password or user name. This can be performed by choosing the Filter tab and specify a filter for the error_number field, to retrieve only the error with number 18456, as shown below:

The location where the SQL Server auditing session result will be written can be specified from the Data Storage tab, by choosing the type of output target and configure its settings as follows:

For example, you can choose the target as a SQL Server Extended Events event_file, with XEL extension, then configure its location and properties, as bellow:

In the Advanced tab, you can configure the retention and resources settings for the SQL Server Extended Events session. In our example, we will keep the default values, as shown below:

Once the SQL Server Extended Events session created, a new empty window will be displayed in the SQL Server Management Studio, in which the caught events will be displayed, as follows:

If this page is not displayed, right-click on that session and choose the Watch Live Data option, as below:

After performing successful and failed login processes, the events will be collected and displayed by the SQL Server Extended Events session. For example, the successful login process properties, including the user name, the host name, the application used for the login and other useful information will be displayed as shown clearly below:

On the other hand, all useful information about the error message generated when the login attempts to fail, will be caught and displayed in the SQL Server Extended Events session, as shown below:

In addition to writing the logs to the event file for future analysis, as below:

Managing sessions

To start or stop the created session, browse for that session under the Extended Event Sessions, and choose the Start Session or Stop Session, as shown below:

SQL Server Extended Events cannot be used to design a complete database auditing solution. Although it is very useful in auditing the successful and failed logins processes, as shown in the previous example, this feature still limited in terms of auditing the different database DML changes and comparing the values before and after the modification process, that can be easily performed in the SQL Server auditing mechanisms discussed later in this article and the next articles of this series.