Share via

Demo Transcript: SQL Server 2012 Extended Events Enhancements

  • Article

SQL11UPD02-TSCRIPT-08

This wiki is a transcript of a previously recorded video.

Related content assets:

New Session Wizard

Hello. Welcome to introducing SQL Server 2012. This is a demo for SQL Server 2012 Extended Events Management Studio enhancements. My name is Jonathan Kehayias – I’m as SQL Server MVP, a Microsoft Certified Master for SQL Server 2008, as well as a Principal Consultant for SQLskills.

One of the greatest features that was added into SQL Server has to be Extended Events and the level of detail that you can get with diagnostic troubleshooting in the environment. However in 2008 there was no user interface associated with it so there was a slow level of adoption to be able to use it because it required so much time to figure out.

In 2012, if we expand our Management tab, we now have an Extended Events folder where we can get direct access to Extended Events through the user interface.

We can see all the sessions that have been defined in the Sessions folder here. There are two default sessions that are created with the instance – there’s one for monitoring AlwaysOn and there’s the system health session as well.

So to create a new event session what we do is we click on the Sessions folder and we’ll make use of the New Session wizard here, which is the fastest way to get up and running with a SQL Server Extended Events session inside of SQL Server 2012. So if we open the wizard, it will open with an introduction page and in the introduction page we can just click Next and on the Set Session Properties page we can provide a name for our session. What we’re going to name this one is NonSSMSConnections, and we’re going to set up a session that looks for connectivity that’s not coming from Management Studio. So maybe it’s restricted to just one of our applications.

We also have the option to allow our session to start any time our server start up. Now in this case we’re not going to enable this but if you have a session that you wanted to start up, have start up and running every time that SQL Server restarted, this would be where you set that option so that it starts automatically when the instance starts.

Now we’re going to use a session template associated with this and what we’re going to use is the Connection Tracking template. And what this template does is it tracks connection activity for the server and it does it through the login and logout events, and what we’re going to do is we’re going to start with the template as the basis for our event session and we’re going to search our event library by typing in the event library box for our sql_batch_completed and our sql_statement_completed events inside of Extended Events. And we’re going to add those two events using multi-select over to our event session. We could do the filtering by the event names only, we could filter by the name and the description, or just by the fields associated with an event, or we could view all associated with that. We could also filter by out category or we could filter by our event channels as well – there are lots of options for how can find the events that we want to find inside of Extended Events inside of this UI.

So if we click Next, what we’re going to do is we have global fields – these are called actions inside of Extended Events – and they allow you to collect additional data, and inside of the wizard here it’s going to add each of these that we check to all of the events inside of our event session. So we’re going to add the client application name and the hostname that it’s coming from and once we’ve added our global fields we have the ability to set global filters that are going to create filters across all of the events inside of our event session. And through this we are going to create a filter on the sqlserver.client_app_name  we’re going to use a not-equal-to operator and we’re going to do Microsoft SQL Server Management Studio. So any application that connects that is not Microsoft SQL Server Management Studio will be captured by our event session.

And when we clock Next, we have the option of where are we going to store our data. There are multiple storage areas inside of Extended Events, known as targets and for target configuration, we’re going to opt to save the data to a file, which allows us to do post-processing of the information that we captured, and will allow me to show you a couple of the newer features of the UI as well. You can also have it stored in an in-memory target, which is a ring buffer, and you can specify how many events you want to keep inside of the target – and if you have an event session that’s rolling events really fast, you can specify that you want to maintain a specific number of events by type inside of the target, instead of having it function like a first-in, first-out buffer… or queue for holding the events.

So we’re going to turn that one off, we’re going to leave the save the file for later analysis and we’re going to click Next, and we have the option to script our event session here if we wanted to look at the DDL and make manual changes associated with it, but what I’m going to do is just go ahead and click Finish.

After it’s a created our event session it gives us two more options. One would be to start the event session immediately after we close the dialog, and the other is to watch the live data on-screen as it’s captured – which we are going to do.

Live Data Window

So our server has a lot of activity going on with it and because I have previously created a similar session to this, the UI here is going to open up with a lot of things that it wouldn’t typically open up with. So what I’m going to do is I’m going to go really fast and clear all of these items and the only two things that you typically get by default in the UI is the name and the timestamp.

So when you open up or create an event session and you view live data, the way that you see the data is just like this. You have the name and timestamp for the event and then you have all the details for the currently selected event in the UI down here in the details pane.

So the way that you can add the columns would be the reverse of what you just saw me do to remove the them, or you can right-click on an individual column down here, do Show Column in Table, and it will actually bring that column into the column. The alternative is to right-click on the column headers, do Choose Columns, and you can add or remove as you need to. There is a neat functionality here with Merge Columns, where you can actually select the multiple column outputs from different events. For example, a really good one to do this with would be batch_text and statement, which would correlate to what would have been TextData inside of a SQL Trace, and you could do TextData here, and if you’re looking at a sql_statement_completed or a sql_batch_completed event inside of the environment, you’ll see that it now puts both of these pieces of data in the same column for the two events that are generating those data. So there are a lot of interesting things that you can do associated with that.

If we go up to the Extended Events menu, we have a bunch of options that are very similar to what we had with Trace – we can stop the data feed, we can clear the data in the window, we can enable auto-scroll, show the details pane. One thing we can do is we can create local, client-side filters of the data that’s being shown so while it’s gathering the information we can filter that information locally so that we can focus in on certain problem areas or points that we want to look at.

We have Display Settings and we have the option the save our display settings so if you’re opening files and it doesn’t retain the display settings, one of the neat things to do is you define it once, save it, and then you have the template so any time that you open a file up you actually have the display settings to revert back to.

If we stop our data feed, we get the option to be able to export the results of our table that it’s currently showing to an Extended Events file in the file system, we could write it to a table inside of a database, or we could dump it out to a CSV file as well. That’s very useful to be able to do.

The other thing we can do is we can do grouping and aggregation. So if we click on Grouping, we can tell it to group by event name and it rolls up all of our events to that we can see how many times each of them have executed. If we wanted to do an aggregation we can aggregate – in this case there’s not really any good data that I added in here to aggregate by – but if we go to Choose Columns and maybe we want to add CPU time. So we can go back and do an aggregate – the sum of the CPU time associated with each of them, and there’s no real workload running here, so there’s not really a large amount of CPU time that’s being reported by my events, but that would be how you would do aggregation inside of the UI really, really quickly.

The last thing you can do is you can go open a file and if you had a file target… we need to go first stop our event session… if we go open the file that we generated – in this case it should be NonSSMSConnections. It opens in the default display and this where we can go open our recent display settings and apply our display settings that we previously saved to a template, back out to our display for our file that we just opened.

And that concludes the demonstration of how you can make use of the Extended Events UI – there’s a lot more functionality that’s in there so have a look around and enjoy.

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