Database Administration x_men_logo_destroyed_by_fireheart47-d3kd6wt

Published on December 19th, 2014 | by Warner Chaves

2

Presentation: Introduction to SQL Server Extended Events

This presentation is a product of many conversations I had over the last couple of years regarding SQL Server Extended Events. Many times, I would ask DBA’s if they were already using Extended Events and they would tell me that they still would prefer to keep creating the older Profiler traces. When I asked why, many told me that Extended Events just seemed “too complicated”, “too in depth” or something along those lines. Granted, at the beginning it was a bit painful to be dealing with so much XML but now with the Extended Events GUI that is available in SQL Server 2012 and SQL Server 2014, it’s very easy to get a trace up and running and process the results.

So, I set myself the goal of creating an introductory presentation that would show Extended Events in the least intimidating way possible. I go through only the necessary theory to get started and then we go straight through 3 intro level demos:

  1. Tracking Wait Types Per Session: this is something that wasn’t previously even possible, the alternative was to have clunky jobs constantly polling sys.dm_os_waiting_tasks to see what WAITS a session was encountering. With Extended Events we can easily track it and aggregate it.
  2. Tracing an entire sequence of events that end up generating a specific error: this was previously possible with Profiler but it was cumbersome and error prone. With Extended Events “causality tracking” feature this becomes much more simpler and it’s very easy to debug and troubleshoot complex chains of events.
  3. Size and amount of Read IOs a specific workload is doing: another one that wasn’t previously possible from a SQL Server tracing perspective. With the histogram target and the file read events we can easily run a workload and profile it’s IO behavior in the amount and size of the IOs it makes. This is very powerful, for example, you could capture this information and then create a SQLIO test file to test new hardware before an upgrade.

During the presentation I use the Extended Events GUI introduced in SQL Server 2012 to prepare the scripts to make it as user friendly as possible and then go over what the actual T-SQL code generated looks like. I presented it for SQLSaturday in Philadelphia and also to the DBA PASS Virtual Chapter and had a great experience on both occasions.

Here’s the recording from the PASS presentation:

 

And also, here’s the link if you want to download the scripts and the database backup we use in the demos: Introduction to SQL Server Extended Events

Enjoy!

Tags: , ,


About the Author

SQL Server Certified Master and MVP. DBA for MS technologies for over 10 years.



2 Responses to Presentation: Introduction to SQL Server Extended Events

  1. wsalkin@hotmail.com' DataWrangler says:

    Warner,

    Loved your SuperTrace video. You certainly know how to present material to those not familiar with it!

    Using xEvents is there a way to aggregate queries (from a duration perspective)? For example I regard

    select * from myTable where myname = ‘jack’
    select * from myTable where myname = ‘jill’

    as being essentially the same query from a duration perspective. Under SSMS, xEvent output, I would like to see a single (“consolidated”) statement and (consolidated) duration for both statements. (And this notion should extend to any number of such similar queries) . Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed


    sqlturbo.com A SQL Server Web Blog
    Canada, Ontario, Ottawa.
    http://sqlturbo.com

    The Authors