- December 19, 2014
- Posted by: Warner Chaves
- Category: Database Administration, Online Training
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:
- 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.
- 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.
- 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