In the first post in this series, SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface, we looked at how to use the New Session Wizard in SQL Server 2012 to define an Event Session. In this post we’ll compare the Wizard to the standard New Event Session dialog that can also be used for creating and Event Session in SQL Server 2012. The New Event Session dialog is the same dialog that is used for editing an existing Event Session on the server, and can be accessed from the Extended Events node in Object Explorer, just like the New Session Wizard can be.
Rather than opening up with an Introduction Page, the New Session dialog opens directly allowing you to begin configuring your session. I like to think of the New Session dialog as the power user method of creating a new session, and as we’ll see in this post, it can actually takes less steps to configure an Event Session using the New Session dialog over the New Session Wizard.
The General page of the New Session dialog allows you to specify a name for the Event Session, as well as to select a template to create the Event Session from. You also have the option to specify whether the Event Session should start automatically when SQL Server starts, whether to start the Event Session immediately after creating it with the dialog, and whether you want to immediate begin viewing the Event Session data in the Live Viewer. If you compare this page to the first three, and final pages of the wizard you will see that we have a much more concise configuration using the dialog so far. If you click on the Events page on the left hand side, the Events page will show in the dialog allowing you to customize the events being collected by the Event Session.
On first look, the Events page looks very similar to the Select Events To Capture page from the Wizard. The Event search functionality, and the ability to filter the events by Category and Channel, as well as how you add and remove Events from the Event Session is identical. However, in the New Session dialog, two additional buttons exist, a Configure button, (circled in red) that allows you to begin configuring the Events that have been added to the Event Session, and a Select button, (circled in green) that allows you to return to the Event selection screen from the configuration screen. When the buttons are clicked, the screen will collapse/expand left and right.
The biggest difference between the functionality provided by the New Session Wizard versus the New Session dialog is the level of granularity that you have with assigning Actions or Global Fields, and Predicates or filtering, to the Events that have been added to the Event Session. In the New Session Wizard, any Action or Predicate that is added to the Event Session, is added across the board to all of the Events in the session. The same functionality can be achieved using the New Session dialog by using the multi-select functionality of the UI to select all of the Events, and then adding the appropriate Actions and Predicates. However, typically we don’t actually need the Actions and Filters applied to every Event in the Event Session, and since Actions and global Predicates incur an overhead for data collection, even though it is incredibly small, as a performance best practice. By selecting a single Event, new Actions can be added to the the Event, or as shown below, filtering can be applied at the individual Event level, which allows the filtering definition to be against any column on the Event, not just the shared subset of columns, or global files, across all Events.
If multiple events share the same columns, for example, the sqlserver.sql_batch_completed and sqlserver.sql_statement_completed Events in our session, you can also multi-select those Events and define filtering specific to both of those Events.
Complex Predicates can be defined through the use of the right-click context menu in the Filter table. Keep in mind that Predicates in Extended Events allow short circuiting logic to occur, so the order of Predicates matters during evaluation time. The context menu will allow you to insert a new Predicate above or below the currently selected Predicate in the UI, add or delete additional clauses to the existing Predicate list, group subsets of clauses together so that they evaluate as a complete set, ungroup previously grouped sets of clauses, and to toggle the Not operator which evaluates for the negation of the clause being configured.
A really good example of a complex predicate configuration can be seen by looking at the system_health Event Session and the Predicate on the sqlserver.error_reported event.
The Event Fields tab, will allow you to turn on/off the collection of any customizable columns for the Event that is currently selected. For example, the sql_batch_completed Event has a customizable column for the batch_text, which is turned on by default. If you don’t need the batch text, for example, you may be collecting the tsql_frame action which is much smaller because you know that you will be able to get the batch information from the cache at the point you are analyzing the results, you can turn it off by unchecking the checkbox next to it.
The Data Storage page of the dialog, allows you to configure the targets for the Event Session. The biggest difference here is that you get full use of all of the targets available in Extended Events, not just the event_file and ring_buffer targets provided by the Wizard, though these will typically be the targets that you will use the most.
The Advanced page, allows you to customize the Event Session Options to define how the session will be setup in the Extended Events Engine when it starts.
Once all of the configuration for an Event Session has been completed, you can script the Event Session DDL using the standard SSMS Script button at the top of the UI, or you can create the Event Session immediately by clicking OK. If you need to change the Event Session definition after creating it, the Session Properties dialog can be opened from the right-click context menu in Object Explorer for the session. The Session Properties dialog is exactly the same as the New Session dialog.
In the next post we’ll look at the target data viewer in SLQ Server 2012 and how to use it for analyzing the captured Events from an Event Session.