Extended Events

Extended Events were introduced in SQL Server 2008 as an alternate way to monitor database events and to collect data for troubleshooting. Using Extended Events will have a smaller impact on systems than Profiler, and is more configurable.
With SQL Server 2012, a GUI was added to manage setting up the monitoring. Under Management => Extended Events => Sessions, right click on Sessions to start the New Session Wizard to begin the setup.

You create a Session to collect the event data. Second, you add an Event (or multiple events) to specify what it is that you want to track in the database. You also specify an Action, to designate what information that you want to track, such as User Name, Database Id, etc. You can also apply a filter to the Action, so that you only capture events that meet your specified criteria.
You specify a Target to capture the data for the Session, such a writing the events to a flat file. There are options to limit the size of the file, rolling over data to another file, and other similar options. When the event data is written to a flat file, the data will be stored in XML format. There will also be a metadata file created with an XEM extension.

So here is SQL to create and start the session. In this example, I’m capturing the ‘SQL Statement Completed’ Event, the SQL Text, User Name and Client App Name Actions, and I’m filtering to only capture the events I trigger in the database. I’m writing the events to a flat file target.

— Start SQL
——————————
— Start Session ————-
——————————

— Drop the session if it already exists
if exists(select * from sys.server_event_sessions where name = ‘eventSession’)
drop event session eventSession on server
go
— Create the Session, adding Events, Actions and Targets
create event session eventSession on server
add event sqlserver.sql_statement_completed
(action (sqlserver.sql_text, sqlserver.username, sqlserver.client_app_name)
where sqlserver.username = ‘rob’
)
add target package0.asynchronous_file_target
(set filename = N’C:\XETarget.xel’)
go
— Start the session
alter event session eventSession on server state=start
go

— Once our activity is done, we stop the session

——————————
— Stop Session ————–
——————————

— Stop the Session
alter event session eventSession on server state=stop

— Drop the session
drop event session eventSession on server
go

— End SQL

So once we have the events, we’ll need to read them from XML in the Target. We’ll use the fn_xe_file_target_read_file function to return these records.
This example will return one XML record for each event captured. Note the * wildcard character in the file name, since we may have multiple target files created with unique IDs appended to the file name.

— Return XML

select cast(event_data as xml) as XEData
from sys.fn_xe_file_target_read_file
(‘D:\XETarget*.xel’,’C:\XETarget*.xem’, null, null)

go

Alternatively, if we want to parse the results into relational format.

— Parse XML

select XEData.value(‘(event/action[@name=”sql_text”])[1]’,’nvarchar(2000)’) as SqlText,
XEData.value(‘(event/action[@name=”username”])[1]’,’nvarchar(2000)’) as UserName,
XEData.value(‘(event/data[@name=”duration”])[1]’,’int’) as Duration,
XEData.value(‘(event/data[@name=”reads”])[1]’,’int’) as Reads,
XEData.value(‘(event/data[@name=”writes”])[1]’,’int’) as Writes,
XEData.value(‘(event/action[@name=”client_app_name”])[1]’,’nvarchar(2000)’) as ClientAppName
from (
select cast(event_data as xml) as XEData
from sys.fn_xe_file_target_read_file
(‘D:\XETarget*.xel’,’D:\XETarget*.xem’, null, null)
) as t

go

Finally, some queries to return the available Targets, Events and Actions

select name, description
from sys.dm_xe_objects
where object_type = ‘target’
order by name

select name, description
from sys.dm_xe_objects
where object_type = ‘event’
order by name

select name, description
from sys.dm_xe_objects
where object_type = ‘action’
order by name

MSDN has information on the CREATE EVENT SESSION statement which goes over the options in detail:
http://msdn.microsoft.com/en-us/library/bb677289.aspx

Also, Simple-Talk has a good post that goes over the 2012 GUI:
https://www.simple-talk.com/sql/database-administration/getting-started-with-extended-events-in-sql-server-2012/

Lastly, a detailed post by Jonathan Kehayias:
http://msdn.microsoft.com/en-us/library/dd822788.aspx

Advertisements

One Response to Extended Events

  1. Shane says:

    Having reaad this I thought it was extremely enlightening.
    I appreciate you finding the time and effort to put this article together.
    I nce again find yself spending way too much time both reading and commenting.
    But soo what, it was still worthwhile!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: