Here’s the fifth post in our series of guest posts by Microsoft Most Valued Professionals (MVPs). Since the early 1990s, Microsoft has recognized the inspiring activities of MVPs around the world with the MVP Award. MVPs freely share their deep knowledge, real-world experience, and impartial and objective feedback to help people enhance the way they use technology. Of more than 100 million users who participate in technology communities, around 4,000 are recognized as Microsoft MVPs.
This post is by Grant Fritchey, SQL Server MVP. Thank you, Grant!
This post covers aspects of the about-to-be-released Microsoft SQL Server 2012. (As does “From the MVPs: Get ready for AlwaysOn in SQL Server 2012,” by Denny Cherry.)
Grant here. Introduced with SQL Server 2008, Extended Events are an excellent mechanism for collecting event-driven information about your SQL Server systems. Extended Events have a lighter footprint than the old Trace Events and allow for a more programmatic approach to gathering the events that they respond to. Extended Events are also stored on the SQL Server system and can be simply turned on and off rather than having to be re-created each time you wanted to run them.
Unfortunately, when Extended Events were introduced, there was no GUI to assist people with the somewhat complicated T-SQL needed to create the Sessions (although that T-SQL was much simpler than the T-SQL needed for Trace Events). Another strike against them was that the output was XML, which a lot of people are just not crazy about having to deal with even though there were functions that would let you parse the output into a table. So, with only a few exceptions, there wasn’t much adoption of Extended.
With the release of SQL Server 2012, a set of GUI interfaces have been introduced for dealing with Extended Events: a Wizard, an Extended Events Properties Editor, and a Data Viewer. The Wizard is a handy way to get walked through creating an Extended Events Session, but I’m going to skip past that and talk about the Properties Editor and the Data Viewer. These two interfaces are where you’re going to spend most of your time.
You can find the Extended Events Sessions under the Management folder in SQL Server Management Studio (SSMS). As a replacement (and enhancement, because it does more) for the default trace, a Session comes installed, system_health. You can use this as a great way to learn how Extended Events Sessions are set up because the Session includes many different types of Events, Targets, Filters, and Actions. The same set of windows we’re about to go over can also be used to create new Sessions in addition to edit existing ones.
For illustration purposes, I’m going to stop the system_health Session while we examine its properties, just so that they’re mostly accessible. All I have to do to make this happen is right-click the session and select Stop Session from the context menu. Right-click the Session again and select Properties, and the Editor opens, as you see in Figure 1.
Figure 1 General page of Extended Events Properties window.
I’m not going to go into every possible detail, but you can see that you supply a Session with a name and, when you’re creating a new Session, you can pull from a list of Session Templates. What I like is that you can control when the Session starts. Frequently when creating them from scratch, I will immediately start the event and start watching the live data, which displays in the Data Viewer window.
The next page down is the Events page. Clicking it on the left you’ll see all the power and flexibility of Extended Events on display, as shown in Figure 2.
Figure 2 Events Selection page of the Extended Events Properties Window
This screen has a lot going on. Let’s walk through it. The left side of the screen is where most of the work takes place. At the very top you have a text box and a drop-down selection box that lets you search through all the events in the system. For example, currently no search is in place, so all Events are on display. If I were to type into that text box, the Events, listed below the text box, would be filtered as I typed. It’s a great way to quickly find the event you’re interested in. You can also adjust the search criteria from “Events Names Only” to “Event Names and Descriptions,” “Event Fields Only,” or “All.”
The Events list itself can help you find the Extended Events you’re most interested in. You can change the sorts by clicking the column heads. In Figure 2 the grid is sorted by Name; note the little triangle at the top of the Name column. You also get to filter the list. The Category and Channel columns are drop-down lists with check boxes that let you decide what you’re going to display.
Because the Event names can be somewhat cryptic, in the lower left you get a good description of exactly what the selected Event does. To the right of that you see the Event Fields listed for the selected Event. Fields are like Columns in the old Trace events, but they are more inherent and unique to each Event. If you’re capturing an Event, you automatically capture most of its Fields. I’ll address some exceptions later in this post.
Once you’ve decided which event you’re interested in, you use the large right arrow in the center of the screen to move that Event into the Selected Events list. You can also remove them from there by using the large left arrow. Again, because these names can be somewhat cryptic, you get a second description window below the Selected Events list.
There’s even more functionality on this page. Notice that button in the top right corner that says Configure and points to the right? Click that and you get to a whole new set of functionality, shown in Figure 3.
Figure 3 Events Configuration on the Global Fields Tab
This is where you configure the Events. You can always switch back to Event selection by clicking the Select button.
On the left is just the list of events, plus a little extra. The first column is the name, and here again, you can use that to sort the list and, again, there’s a description of the selected Event down below. The second column (the one with the little lightning bolt) shows how many Global Fields, also known as Actions (and that’s how I’m going to refer to them), have been selected. The last column, with the funnel, shows whether there is a Filter on the Event. These are great ways to quickly identify where you have Filters or Actions. You can see in Figure 3 that error_reported has a filter, but none of the other displayed Events have one.
On the right are three tabs: Global Fields (Actions), Filter (Predicate), and Event Fields. Let’s start with the first one. An Action is, as it says, a Global Field, or an additional column that you can add to any event you’re capturing. For example, the error_reported event that we currently have highlighted doesn’t have a database_id Event Field. If you want to capture the database_id where an error occurred (probably a good idea), you’ll have to use an Action. The thing is, Actions are captured after the Event and executed synchronously. This means that if there’s anything that might cause some performance bottlenecks as part of your Extended Event capture, here’s a likely candidate (and there are a few others). So, rather than call them Global Fields, which makes them sound a little too attractive, I prefer the original nomenclature of Actions so that it’s clear that these are different and that you should use them judiciously. Selecting a particular Action’s check box adds it to the Extended Event selected on the left side of the screen.
As shown in Figure 4, clicking the Filter (Predicate) tab, you get to see how to control which Events get captured.
Figure 4 Filter (Predicate) tab on the Extended Events Configuration page
As you can see, you can add more and more filters. Each of the columns provides you with a drop-down list, except the final one where you’re expected to enter information. The Fields are from the event and a selection of operating system and SQL Server Fields that you can filter on. The comparison operators are the standard set of equals, less than, and so on, divided up into int and int64. The one thing I’d add is that the more immediate your first filter, the less load these will place on the system. Eliminating all errors below 20 as the first criteria is a good example.
The last tab shows the Event Fields, fields that are unique to this event. Don’t misunderstand me: events have lots of fields in common (such as session_id and database_id, because these are common values within SQL Server), but each event has a preselected list of Fields that apply to it. Figure 5 shows the list for one event.
Figure 5 Event Fields tab on the Extended Events Configuration page
Mostly this is just a listing of the Fields and their data types for the selected Event. However, note the event at the top of the list with the check box. By default, all Fields are included, except a few that are more expensive to collect. You have to decide whether you need these Fields when you’re setting up your Extended Events Session. Selecting the check box will include them in the Session.
And that’s it. You’ve now seen how to select a list of Extended Events and to configure those events with Actions, Filters, and Event Fields. Up to now I haven’t talked about where all this information goes. That’s the next page. Clicking the Data Storage page on the left side of the screen via the page listing there opens up a screen like Figure 6.
Figure 6 Data Storage page on the Extended Events Properties window
You can define a number of different Targets for your Extended Events Sessions. It really depends on how you’re trying to consume these events. The interesting thing is, if you just want to watch the Session live, you don’t actually have to designate a target here. I don’t want to try to describe what all these are for and what they can do; there’s better documentation for that. But for most situations, the likely target will be the one I have selected, event_file. This puts all the output into a file. When you select this Target, you get several properties that you must define at the bottom of the screen. For example, the file name and location are naturally included. You also get to decide how large you want the files to be, if you want them to rollover as they’re filled, and, if they’re rolling over, what the maximum number of files ought to be. It’s a great way to capture information like query performance metrics so that you can later load them into tables and start running reports to identify the longest running query, for example.
The final page is the Advanced page, shown in Figure 7.
Figure 7 Advanced page on the Extended Events Properties window
I won’t get into this much except to say that you have a lot of control over how much impact you allow, or force, Extended Events to have on your system. By making adjustments here, you can ensure that you have no losses to Events (and probably a much higher load on your system) or a very lossy process (with a lower impact). Look to Books Online and other resources for when and how to adjust these.
You’re done. You can click OK (although I clicked Cancel because I don’t want to make any changes to my system_health Session), and you have a new Session or you’ve updated a Session. No T-SQL required. However, it’s all still there and you can always script out a Session once you’ve configured it if you want to use that Session on other servers, for example.
But what about the XML output? Let’s talk about the Data Viewer.
For this example, I created a new session by using the Query Batch Tracking template. Now I can watch queries as they go by. All I have to do is right-click the Session and select Watch Live Data, and the Data Viewer window opens as in Figure 8. What’s more, you can use the File, Open, and find *.xel files and open them directly into the same viewer.
Figure 8 The Data Viewer window
The window is split in two. At the top are all the events and the timestamp for when they occurred. At the bottom are the Fields for the selected Event in the window above. You can scroll through the various data, and you’ll see everything you need, no XML required.
Scrolling around to find the data you want can be a pain, so, if you like, you can right-click a Field in the lower window and select Show Column In Table from the context menu. Figure 9 shows where I’ve picked a few columns for display in the grid.
Figure 9 Data Viewer grid with extra columns added
There’s a bunch more functionality built into the Data Viewer. You can double-click a Field to open in a new window, which is handy for viewing long T-SQL strings or XML output. If you’re looking at either a stopped Session or a file, you can sort the grid by columns. You can’t do that while watching Live data. Best of all, and I really love this, you can toggle a bookmark on an event so that you can find your way back to that event quickly. OK, maybe that isn’t best, but it’s pretty good. You can also apply a filter based on a value in a Field to show only that one. So, for example, if I only wanted to look at the error_reported Events from Figure 9, I could right-click that column where that is the value and select Filter On This Value from the context menu.
As shown in Figure 10, a toolbar is included that gives you a bunch more functionality.
Figure 10 Data Viewer toolbar
If you’re looking at a Session, not a file, you can start and stop the session, pick which window you want shown, edit your filters, and do grouping and aggregation. Actually that’s pretty slick, too. What I’ve done in Figure 11 is group by the Event name Field so that I’m seeing all of a particular event as a set.
Figure 11 Grouping by Field Name
From my small run of events I have three different types: error_reported with 18 Events, rpc_completed with 4, and sql_batch_completd with 41. I’ve expanded the rpc_completed to show the individual calls. This does bring out one issue with Extended Events that I found to be a little problematic for gathering query metrics. Note that the batch_text Field is NULL for all the rpc_completed events. This is because the rpc_completed Event has a statement Field that is the equivalent to the batch_text field in the sql_batch_completed Event. A slight pain, and something to be aware of. However, you’re compensated by being able to get the object_name Field, which means you can immediately group all your stored procedure calls by the name of the procedure, no worries about trying to parse out the T-SQL information to remove parameters. That’s a huge win.
You also get the ability to do simple searches, as shown in Figure 12.
Figure 12 Searching within the Extended Events Data Viewer
You can define what field or fields you want to search through. In my case, I chose Table Columns. You can put different criteria in and even make use of wildcards and regular expressions. I left that off in my case so that I could find the literal string ‘SELECT *’.
There’s still more that I haven’t covered, but you get the idea. With SQL Server 2012 you get the ability to do fully fledged data exploration through your Extended Event output.
excellent article. through GUI tools the extend events become more easily
Thank you for this article, I found it very helpful.
One question, how did you transition to the screen in Figure 12?
You lost me there...
Devon, My question is this:
Lets just say that i have a query that I want to audit on regular basis. The query is as follows.
Select FirstName, LastName
Where FirstName like ‘%John%’
1. For the selected events I'm selecting "SQL_Batch_Completed"
2. I'm only selecting the "SQL_Text" Name from Global Fields (Actoins).
3. I want to only filter and look for SQL_Text that is LIKE '%[Test1].[dbo].[Customers]%'
How do i only filter traffic that has SQL_Text LIKE '%[Test1].[dbo].[Customers]%'? I have tried this multiple time and just can not get any results.
Great article, thanks! you can also check a free PASS training on extended events for SQL 2014 at sqlturbo.com/presentation-introduction-to-sql-server-extended-events