{"id":1576,"date":"2013-02-04T00:00:00","date_gmt":"2013-02-04T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/getting-started-with-extended-events-in-sql-server-2012\/"},"modified":"2021-08-24T13:39:58","modified_gmt":"2021-08-24T13:39:58","slug":"getting-started-with-extended-events-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/getting-started-with-extended-events-in-sql-server-2012\/","title":{"rendered":"Getting Started with Extended Events in SQL Server 2012"},"content":{"rendered":"
\n

With the release of SQL Server 2008, Microsoft introduced Extended Events, a system for collecting event-driven data about a SQL Server instance and its databases. The Extended Events feature required a smaller footprint than trace events and supported a robust programmatic interface that let you write customized tools to work with event information. However, SQL Server 2008 included no GUI that interfaced with the events directly. Instead, DBAs had to write complex T-SQL statements to gather the information they needed. And when they did, that information was returned as XML, not always the first choice for DBAs needing quick access to event data. Consequently, the Extended Events feature in SQL Server 2008 saw little adoption throughout the industry.<\/p>\n

But SQL Server 2012 has proven to be a game changer. SQL Server Management Studio (SSMS) now includes a GUI component that makes working with Extended Events and viewing event-related data a series of intuitive and simple operations.<\/p>\n

You manage extended events in SQL Server 2012 through the Extended Events<\/strong><\/code> node in the Object Explorer<\/strong><\/code> window, under the Management<\/strong><\/code> folder. If you expand the Extended Events<\/strong><\/code> node, you’ll find a Sessions<\/strong><\/code> folder. To collect event data in Extended Events, you must create and configure a session that specifies exactly what data to collect.<\/p>\n

In this article, we’ll create a session that collects event information about T-SQL SELECT<\/strong><\/code> statements. The examples are based on querying data in the AdventureWorks2012<\/strong><\/code> sample database-on a local instance of SQL Server 2012-but you can use any database to generate queries whose events you want to capture.<\/p>\n

Creating an Extended\u00a0Events Session<\/h2>\n

In the Sessions<\/strong><\/code> folder in Object Explorer<\/strong><\/code>, you’ll find one or two preconfigured sessions. By default, SQL Server 2012 includes the system_health<\/strong><\/code> session and, depending on which version of SQL Server 2012 you’re running, the AlwaysOn_health<\/strong><\/code> session. You can examine these sessions at your leisure, but for now, we’ll focus on creating a new session so you better understand how Extended Events works. Each session contains the same fundamental components.<\/p>\n

To create a session, you can use the New Session Wizard<\/strong><\/code> or create the session manually. (You can also use T-SQL to create the session, but that’s what we’re trying to avoid.) For this article, we’ll create the session manually. The wizard makes it easier, but does not permit the granular control that a manual creation does. True, you can always access the session properties later to configure the session as you want, but by creating the session manually, you can configure the properties upfront if desired, and you gain a better understanding of how sessions work. Besides, once you’ve created a session manually, the wizard will be a breeze. So let’s get started with our session.<\/p>\n

In Object Explorer<\/strong><\/code>, right-click the Sessions<\/strong><\/code> folder and then click New Session<\/strong><\/code>. When the New Session<\/strong><\/code> dialog box appears, the General<\/strong><\/code> page will be active, as shown in Figure 1. Type a name for your session in the Session name<\/strong><\/code> text box. I used Check Queries<\/strong><\/code>.<\/p>\n

\"1736-figure1small.png\"<\/a><\/p>\n

Figure 1: Creating an Extended Events session in SQL Server 2012<\/p>\n

At this point, you don’t need to configure any of the other options on this page, but they’re worth noting. The first is the Template<\/strong><\/code> drop-down list. You can choose from a list of templates that preconfigure your session with event-related settings. For instance, you can use the Query Detail Sampling<\/strong><\/code> template to retrieve event data about T-SQL statements, stored procedures, and batches, along with other information. You can either modify the preconfigured settings-such as adding or deleting events-or use the settings as configured in the template. Once you save the session, you can’t change the template being used, but you can modify the properties as necessary.<\/p>\n

For the most part, the other options on the General<\/strong><\/code> page should be self-evident. The only one that might not be clear is in the Casualty tracking<\/strong><\/code> section. The Track how events are related to one another<\/strong><\/code> option lets you track events across related tasks, when one task causes work to be done by another.<\/p>\n

Once you’ve provided a name for your session, go to the Events<\/strong><\/code> page (shown in Figure 2) to select the events you want to monitor.<\/p>\n

\"1736-figure2small.png\"<\/a><\/p>\n

Figure 2: Adding events to an Extended Events session<\/p>\n

If you select an event in the Event library<\/strong><\/code> grid, a description is listed below, along with a list of fields associated with the event. Each event is made up of a set of fields that provide specific information about the event. For example, in Figure 2, the query_post_execution_showplan<\/strong><\/code> event is selected. The event returns an XML representation of the query plan, along with details such as the database name and the query’s duration, as reflected in the list of fields.<\/p>\n

To add an event to your session, select the event in the Event library<\/strong><\/code> grid, and then click the right arrow to add it to the Selected events<\/strong><\/code> grid at the right of the page. For this exercise, we’ll add the query_post_execution_showplan<\/strong><\/code> and sql_statement_completed<\/strong><\/code> events. Figure 3 shows what the Events<\/strong><\/code> page looks like after you’ve added the two events.<\/p>\n

\"1736-figure3small.png\"<\/a><\/p>\n

Figure 3: Viewing selected events in the New Session dialog box<\/p>\n

When adding events to your session, remember that the process of tracking events comes at a cost, particularly an event such as query_post_execution_showplan<\/strong><\/code>. So select your events carefully and keep the tracking costs in mind when you run your sessions. Events that are particularly costly, like query_post_execution_showplan<\/strong><\/code>, usually come with a warning in their descriptions, so be sure to take note of those.<\/p>\n

Otherwise, that’s all you need to do to set up a basic session. There are certainly more granular ways we can configure our session, which we’ll do later in the article, but first, let’s try out what we’ve done so far so you can see the session in action. Click OK<\/strong><\/code> to save the session and close the New Session<\/strong><\/code> dialog box. Your event should now be listed beneath the Sessions<\/strong><\/code> folder in Object Explorer<\/strong><\/code>.<\/p>\n

The next step will be to test the session. To do so, you’ll need to run a few queries, unless you created your session on a SQL Server instance that’s actively being queried. On my system, I created the following queries to run against the AdventureWorks2012<\/strong><\/code> database:<\/p>\n

SELECT * FROM Production.TransactionHistory\r\nORDER BY TransactionDate DESC, ReferenceOrderID, ReferenceOrderLineID;\r\n\r\nSELECT * FROM Person.Person\r\nWHERE BusinessEntityID <> 100\r\n  OR BusinessEntityID <> 200\r\nORDER BY PersonType, Lastname, FirstName;\r\n\r\nSELECT * FROM Sales.Currency\r\nORDER BY Name;<\/pre>\n
Once you’ve gotten your queries ready to go, right-click the new event and click Start Session<\/strong><\/code>. Then right-click the event again and click Watch Live Data<\/strong><\/code>. This opens a tab in SSMS that displays the events in the upper pane and details about each event in the lower pane. Now run your queries. Your results should look similar to those shown in Figure 4.<\/p>\n
\"1736-figure4.png\"<\/p>\n
Figure 4: Viewing a query_post_execution_showplan event<\/p>\n
On my system, the session recorded one sql_statement_completed<\/strong><\/code> event for each statement I ran, one query_post_execution_showplan<\/strong><\/code> event for each of those statements, and numerous other query_post_execution_showplan<\/strong><\/code> events related to SQL Server management.<\/p>\n
Along with the list of events, you can view details about an event by selecting the event in the top pane. For example, the first query_post_execution_showplan<\/strong><\/code> event shown in Figure 4 is selected. Below you can find details about the event, broken down by field, such as duration<\/strong><\/code>, estimated_rows<\/strong><\/code>, and showplan_xml<\/strong><\/code>. If you double-click the showplan_xml<\/strong><\/code> value, the XML will open in its own tab, rendered in a more readable format.<\/p>\n
Figure 5, on the other hand, shows the details of the first sql_statement_completed<\/strong><\/code> event to be recorded in my session. The event includes some of the same fields as the previous one, but additional fields as well, such as physical_reads<\/strong><\/code> and row_count<\/strong><\/code>.<\/p>\n
\"1736-figure5.png\"<\/p>\n
Figure 5: Viewing a sql_statement_completed event<\/p>\n
If you double-click the value of a non-XML field, the field’s value will be displayed in its own window. For example, Figure 6 shows the value of the statement<\/strong><\/code> field. The value in this case is the SELECT<\/strong><\/code> statement that generated the event.<\/p>\n
\"1736-figure6.png\"<\/p>\n
Figure 6: Viewing the T-SQL statement in a sql_statement_completed event<\/p>\n
Although we added only two events to our session, we still receive plenty of information about our query executions. However, if we keep the session running, we’ll end up with a lot more events than we want or need. To prevent this from happening, we can add filters to our session to limit the returned events to those that most interest us.<\/p>\n
Adding Filters to a Session<\/h2>\n
Filters let you limit the number of events you return based on specified criteria. Before adding any filters to your session, first close the Live Data<\/strong><\/code> tab and then, in Object Explorer<\/strong><\/code>, right-click the session and click Properties<\/strong><\/code>. In the Session Properties<\/strong><\/code> dialog box, go to the Events<\/strong><\/code> page and click the Configure<\/strong><\/code> button in the top-right corner. You should be presented with a screen that lets you select fields and configure filters. Go to the Filter (Predicate)<\/strong><\/code> tab and select the query_post_execution_showplan<\/strong><\/code> event in the Selected events<\/strong><\/code> grid, as shown in Figure 7.<\/p>\n
\"1736-figure7small.png\"<\/a><\/p>\n
Figure 7: Adding filters to the query_post_execution_showplan event<\/p>\n
You create filters based on the selected event. To create a filter, you specify a field, operator, and value. The events returned must match these criteria. For example, notice in Figure 7 that I’ve added two filters for the query_post_execution_showplan<\/strong><\/code> event. The first filter specifies the duration<\/strong><\/code> field, the greater-than (><\/strong><\/code>) operator, and the value 500000<\/strong><\/code>. As a result, for a query_post_execution_showplan<\/strong><\/code> event to be returned by the session, it must have a duration<\/strong><\/code> value greater than 500,000 microseconds. This way, only longer running query_post_execution_showplan<\/strong><\/code> events are returned.<\/p>\n
The second filter works in a similar fashion. It specifies that the source_database_id<\/strong><\/code> value must equal 7<\/strong><\/code>, which, on my system, is the ID assigned to the AdventureWorks2012<\/strong><\/code> database. I added this filter so my session includes only query_post_execution_showplan<\/strong><\/code> events related to that database.<\/p>\n
I next added a filter on the sql_statement_completed<\/strong><\/code> event, as shown in Figure 8. This is the same filter I added on the duration field for the query_post_execution_showplan<\/strong><\/code> event. As a result, only sql_statement_completed<\/strong><\/code> events whose duration is greater than 500,000 microseconds will be included in the session results.<\/p>\n
\"1736-figure8small.png\"<\/p>\n
Figure 8: Adding a filter to the sql_statement_completed event<\/p>\n
Once you’ve configured your filters, click OK<\/strong><\/code> to close the Session Properties<\/strong><\/code> dialog box, right-click the session in Object Explorer<\/strong><\/code>, and then click Watch Live Data<\/strong><\/code>. If necessary, run your T-SQL statements again, or wait for other statements to run. On my system, I ran the three queries, but only two of the statements returned events that were not filtered out, as indicated by the Live Data<\/strong><\/code> tab shown in Figure 9.<\/p>\n
\"1736-figure9.png\"<\/p>\n
Figure 9: Viewing the filtered events in SQL Server Management Studio<\/p>\n
Because only two of my SELECT<\/strong><\/code> statements ran over 500,000 microseconds, my session included only events generated by those statements. And because I limited query_post_execution_showplan<\/strong><\/code> events to the AdventureWorks2012<\/strong><\/code> database, my session no longer includes all those extra query_post_execution_showplan<\/strong><\/code> events. As a result, my session now includes only two sql_statement_completed<\/strong><\/code> events and two query_post_execution_showplan<\/strong><\/code> events.<\/p>\n
Your session results might be different from mine, but you can still see how useful filters can be to help ensure you’re returning only the data you want to return.<\/p>\n
Adding Fields to a Session<\/h2>\n
Now let’s look at another way you can configure your session to include the information you need. Close the Live Data<\/strong><\/code> tab and again open your session’s properties. Once more, go to the Events<\/strong><\/code> page and click the Configure<\/strong><\/code> button. This time, however, go to the Global Fields (Actions)<\/strong><\/code> tab. Global fields, such as database_id<\/strong><\/code> or database_name<\/strong><\/code>, are fields available to multiple events. At times, it can be useful to add one or more of these fields to provide necessary information in an event. For example, it can be sometimes be difficult to know which query_post_execution_showplan<\/strong><\/code> event is associated with which sql_statement_completed<\/strong><\/code> event in our results. If we add the transaction_id<\/strong><\/code> field to each event we have a better idea how to associate the events.<\/p>\n
To add a global field, select the event in the Selected events<\/strong><\/code> grid and then select the global fields you want to include. Figure 10 shows the transaction_id<\/strong><\/code> field selected for the query_post_execution_showplan<\/strong><\/code> event.<\/p>\n
\"1736-figure10small.png\"<\/a><\/p>\n
Figure 10: Adding a global field to the query_post_execution_showplan event<\/p>\n
In some cases, you can also decide which event-specific fields to include. If you go to the Event Fields<\/strong><\/code> tab (with the query_post_execution_showplan<\/strong><\/code> event still selected), you can see that the database_name<\/strong><\/code> field is optional, as shown in Figure 11. You can choose to include the field or not. For this exercise, we’ll include it. Note, however, some fields are listed as global, but are also included in the event fields, as is the case with the database_name<\/strong><\/code> field.<\/p>\n
\"1736-figure11small.png\"<\/a><\/p>\n
Figure 11: Adding an event field to the query_post_execution_showplan event<\/p>\n
Regardless of where the field is listed, keep in mind that you should add fields only when necessary because any components you add contribute to the overhead, even if only slightly. You could argue, of course, that we do not need to include the database_name<\/strong><\/code> field on this event because our filter returns events only for one database. However, including the database name in this case is meant only to demonstrate how optional event fields work.<\/p>\n
Now let’s repeat the process for the sql_statement_completed<\/strong><\/code> event. Select the event in the Selected events<\/strong><\/code> grid, go to the Global Fields (Actions)<\/strong><\/code> tab, and select the database_name<\/strong><\/code> and transactio n _id<\/strong><\/code> fields, as shown in Figure 12. (Note that, in this case, database_name<\/strong><\/code> is not included in the event fields.)<\/p>\n
\"1736-figure12small.png\"<\/a><\/p>\n
Figure 12: Adding global fields to the sql_statement_completed event<\/p>\n
Now let’s look at the Event Fields<\/strong><\/code> tab associated with the sql_statement_completed<\/strong><\/code> event (shown in Figure 13). Notice that you can choose to include the parameterized_plan_handle<\/strong><\/code> field and statement<\/strong><\/code> field. By default, the first is not selected and the second is. For this exercise, we’ll stick with the default settings. Click OK<\/strong><\/code> to save your session and close the Session Properties<\/strong><\/code> dialog box.<\/p>\n
\"1736-figure12small.png\"<\/a><\/p>\n
Figure 13: Viewing event fields for the sql_statement_completed event<\/p>\n
Next, launch the Live Data<\/strong><\/code> tab and run your test queries. Your session should include the same events as before; however, it’s when you view the individual events that you’ll see the difference. For example, Figure 14 shows the details for the first query_post_execution_showplan<\/strong><\/code> event. Notice that the information now includes the database name and a transaction ID, in this case, 309748<\/strong><\/code>.<\/p>\n
\"1736-figure14.png\"<\/p>\n
Figure 14: Viewing the transaction_id and database_name fields<\/p>\n
My next event, sql_statement_completed<\/strong><\/code>, also shows a transaction ID of 309748<\/strong><\/code>, as well as the database name, both of which are shown in Figure 15.<\/p>\n
\"1736-figure15.png\"<\/p>\n
Figure 15: Viewing the transaction_id and database_name fields<\/p>\n
As you can see, including the additional information can help to better understand what you’re looking at, where the data comes from, and how the pieces fit together. But so far, we’ve only looked at events as they’ve occurred. It’s time to learn how to capture and save that data.<\/p>\n
Saving Session Data<\/h2>\n
Once again, close the Live Data<\/strong><\/code> tab and open the session’s properties, but this time go to the Data Storage<\/strong><\/code> page, as shown in Figure 16. Here you specify how you want to capture event data so you can save it for later viewing.<\/p>\n
\"1736-figure16small.png\"<\/a><\/p>\n
Figure 16: Adding data storage to an Extended Events session<\/p>\n
SQL Server lets you save your session events in a number of formats:<\/p>\n