Extended events – Your first setup

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.

One comment

  1. I am now not certain where you are getting your info, however good topic.
    I needs to spend a while learning much more or figuring out more.

    Thank you for excellent information I used to be on the lookout
    for this info for my mission.

    Like

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