SQL11UPD02-TSCRIPT-07

This wiki is a transcript of a previously recorded video.

Related content assets: 

  • Presentation: Introducing SQL Server 2012 Extended Events Enhancements (SQL11UPD02-DECK-04
  • Video: Introducing SQL Server 2012 Extended Events Enhancements (SQL11UPD02-REC-07)

Title Slide

Welcome to introducing SQL Server 2012

Slide: Introducing SQL Server 2012 Extended Events Enhancements

In this video we’ll looking at introducing SQL Server 2012 Extended Events enhancements. My name is Jonathan Kehayias – I’m a SQL Server MVP and a Microsoft Certified Master for SQL Server 2008. I’m also a Principal Consultant with SQLskills.

Slide: Improved Diagnostic Troubleshooting

In SQL Server 2012, we get an improved diagnostics and troubleshooting implementation through the use of Extended Events and the new support that is available for Extended Events inside of SQL Server Management Studio.

We also have a new management API associated with being able to use .NET to read Extended Events information to fully support the Extended Events implementation inside of SQL Server as a part of 2012 as well.

Slide: What Happens When…

So in SQL Server 2008, if you wanted to use Extended Events to capture detailed information for diagnostics or troubleshooting inside of your environment, specifically information that’s not available through SQL Trace, you had to have a very extensive background in understanding what the system catalog views were and system DMVs were for Extended Events were to determine what objects were available in the environment for creating an event session.

Event sessions also could only be managed or maintained through the use of Transact-SQL and DDL commands inside of the environment. If you wanted to read the data that was generated by an event session inside of Extended Events, you had to understand XQuery and XML fairly well to be able to parse all the Extended Events data out of the file targets or the ring buffer targets, or any of the other in-memory targets, to actually make leverage-able use of the data that was being collected.

In SQL Server 2012, Management Studio now provides full support for Extended Events under the Management folder in Object Explorer. Additionally, the target data can be read using the Data Viewer inside of Management Studio and there are hundreds of more events, additional events that have been added into SQL Server 2012. All of the trace events, for example, that were previously available inside of SQL Server Trace are now a part of Extended Events – providing full parity between Trace and Extended Events for what you can actually collect, as well as being able to gather tons of information about the internals and the operational execution of SQL Server using Extended Events as well.

Slide: Extended Events UI Support

So for the UI support, the first thing that we get is we get a list of the event sessions that have been created inside of the server. The event session list is available from the Sessions node inside of Object Explorer if you branch out to the Management folder, and then you expand the Extended Events folder, there will be a Sessions folder that has all the sessions that have been created or catalogued inside of our instance of SQL Server for Extended Events.

Additionally we have a new Session Wizard that can be accessed from the Sessions node context menu inside of Object Explorer and this provides a very simplified experience for being able to create an event session by only exposing the most common features and functionalities that you’re going to typically use for creating an event session. This is your fast track way to be able to create and event session. It also allows you to create session from templates and it really reduces the amount of knowledge that you have to have about all the internals associated with Extended Events to actually get up and running with Extended Events. It’s a great addition to the user interface.

There’s also what’s called a New Session dialog, or a session editor dialog, and this provides a more robust way of being able to create a new session, and it also provides you the mechanism for being able to edit an existing session that is already catalogued in the server. This dialog will expose a complete set of options and functionalities so there may be things that you need to know a lot more about the internals or architecture of Extended Events before you’d be able to make leverage-able use of them.

For example, in the New Session wizard, you can only use the ring buffer in-memory target or the file target. The New Session dialog also allows you to use the histogram target or the synchronous event counter, or the pair matching target as well – and you would have to know how to configure those to understand what the dialogs are asking for associated with their creation inside of the event session, in the New Session dialog.

It’s accessible from the Sessions node context menu inside of Object Explorer so if you were to right-click on the Sessions folder you’ll see New Session wizard and New Session stacked on top of each other as options and it’s completely up to you whichever way you choose to go as far as being able to create or define your new session inside of Extended Events.

The last thing we get is an Extended Events event display and these are tabbed windows that display the Extended Events trace data or file data that’s coming off of our SQL Server or being read out of a file target file inside of a window that’s similar to SQL Server Profiler.

Slide: Extended Events Management API

With our Extended Events management API we get the ability to create and modify event sessions and explore all the Extended Events metadata on a server through .NET, and this is really, really useful. There’s also a PowerShell drive implemented on top of the Extended Events management API inside of SQL Server so that you can navigate into your Extended Events sessions on a server just like you would any other object inside of PowerShell when you’re connected to the SQL PowerShell drive.

The complete object model for Extended Events usage is provided through the API and it can be used by managed applications to actually connect to SQL Server and perform diagnostic analysis or automation. For example, third-party ISV applications that want to gather trace data, or previously in the past gathered trace data for analysis and dashboarding, those can now leverage Extended Events through the management API.

There’s also what’s called an XEReader API that allows you to read the Extended Event files that have been created on another server or from an event session in the file system, or it can read an event stream that’s coming live off a running event session for a server. And the way that you would that is using the QueryableXEventData object and that object will allow the system to be able to connect to the SQL Server defined in the connection string and the session that is defined by the session name and then be able to open a stream of events that’s coming from that event session in real time.

Slide: Summary

So in summary, SQL Server 2012 offers a much easier way to do diagnostic tracing and troubleshooting with Extended Events, through the implementation of the Management Studio user interface integration that provides very similar functionality to what SQL Server Profiler provided for trace in past versions of SQL Server.

It also allows you to be able to create Extended Events sessions, modify them, and script them out for recreation on other servers very easily, all inside of a very easy to use set of wizards or dialogs in the UI.

And the management API allows managed applications to be able to be developed, that leverage Extended Events for future use of diagnostic troubleshooting inside of SQL Server.


Return to SQL Server 2012 Developer Training Kit BOM (en-US)