Your first extended event

Extended events are used to track down what happens in the SQL Server, and you can very easily setup an  extended event to e.g. montioring what SQL Statements a user is executing, what waits there are on the servers and e.g.

I recommend that you try this demo first, and then read the webpages from MSDN that introduces Extended events (http://msdn.microsoft.com/en-us/library/bb630354%28v=sql.105%29.aspx).

Learning Extened events are quite easy when you have an SQL server 2008 (and in 2012 it is getting easier again). It is a three step process:

  1. Setting up your first extened event session.
  2. Understanding the output
  3. Learning what events and actions you can use.

But let me start with a warning. Extened events are lightweight and very efficent, but if you set it up wrongly and try to catch to many events and outputs, then you can basically drain the performance on the SQL Server.

So lets try to get working (you need to have the adventureworks database installed, and you can download it from here. You..  also need an SQL Server 2008 or never).
OK. Lets start to setup an extended event that catches all sql statements in the adventureworks database.

  1. Open a command promt (go to start and type CMD).
  2. Write MD c:\XEtest hit enter

Extened events requires a folder to store the XML files that are the output (at least we choose to output to a file in this example). The service account that has started the SQL Server must have access to write to that folder.

We want to create an extended event called testSQL and therefore we must make sure that we drop it if it exists on the server (so that we can set it up). Open Microsoft Sql Server Management studio and start a new query. Write:

use master
go
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’testSQL’)
DROP EVENT SESSION testSQL ON SERVER

This makes sure that we can setup a new SQL Extended event called testSQL, since we have just dropped the old one (if it existed).

Before we can setup the event session, then we must find the ID of the database that we want to track the SQL statements from. Execute the following statement and write down the number that it returns (for me it is 30)

select DB_ID(‘adventureworks’)

Now, lets try to setup the new extended event, and first of all we must decide what event to track, what action to setup and where to output. Let’s write the code

Create event session testSQL on server
    add event sqlserver.sql_statement_completed
        (    action (sqlserver.sql_text)
            where sqlserver.database_ID=30)
    add target package0.asynchronous_file_target
        (set filename = N’C:\xetest\SQLTEST.xml’
        , metadatafile= N’C:\xetest\SQLTESTmeta.xml’
        )
    with (max_dispatch_latency = 1 seconds)

The words I have written in red, is the important words, and let me try to explain.

First of all we Create an event session, and the syntax is CREATE EVENT SESSION xxxxx ON SERVER, where xxxxx is the name.

To create the event session, we must add an EVENT to track, and in this situation it is and event called sqlserver.sql_statement_completed. In a later post I will go through some of the cool events that can be used.

When the event is added, then we must add a target, and in this situation we just write to a file (I will also go through this in a later post).

Finally we can set some options with the with clause, and in our situation we have set a max_dispatch_latency on one second, which basically means that we store what we have cached in memory for max of 1 second.

If you have set it up correctly, then you now can start the event:

alter event session testSQL on server
State = Start

Now the event is starting, and you can try to write some sql statements:

use AdventureWorks
go

select *
from Person.Address

when you have done that, then you can stop the eventsession

alter event session testSQL on server
State = Start

and we are ready to see what we have captured.

select DATA as xmldata
        , Data.value(‘(/event/action[@name="sql_text"]/value)[1]’, ‘varchar(max)’)
from (
select CONVERT(xml, event_data) as data
from sys.fn_xe_file_target_read_file(
        ‘c:\xetest\sqltest*.xml’
        , N’c:\xetest\sqltestmeta*.xml’
        , null
        , null
        )
     ) entries

If you run this script, you will see two columns, one with some xml that you can look at, and one with the statement that has been executed.

And that is basically it. That is how you setup Extended events. But of course there are more to it.

  1. What events can we capture
  2. What actions can we do
  3. Which targets can we send the output to
  4. What options can we use.
  5. How can we format the output.

Especially 5 is very interesting and difficult, but basically now you know how to setup an extended event.

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