Customizing Extended Events Templates in SQL Server 2012

One of the features of the Extended Events UI in SQL Server 2012 is the ability to export an event session definition out to a template for future reuse.  This can be accomplished through the right-click menu for an event session and then by selecting the Export Session menu item as shown in Figure 1.

Figure 1: Exporting a session

Event sessions that have been exported are saved in the %USERPROFILE%\Documents\SQL Server Management Studio\Templates\XEventTemplates path on the machine that is running SQL Server Management Studio.  When you open up a New Session Wizard or the New Session dialog to create a new event session, the templates will display the user defined templates as shown in Figure 2.

Figure 2: Template selection

The basic event session templates created by Management Studio can be customized to display in the UI similar to how the built-in templates display.  The built-in templates are stored in the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Templates\sql\xevent path on the client machine.  By editing one of these templates, you can grab the additional XML elements that provide the metadata about the event session template for display in the UI.  The XML from the Count Query Locks template is show below to highlight the necessary elements and their location in the XML document

<event_sessions xmlns="http://schemas.microsoft.com/sqlserver/2008/07/extendedeventsconfig">
  <event_session name="lock_counter_final" maxMemory="4" eventRetentionMode="allowSingleEventLoss" trackCausality="true" dispatchLatency="30" maxEventSize="0" memoryPartitionMode="none">
    <templateCategory>
      <!-- _locID_text="templateCategory" _locComment = "" -->Locks and Blocks</templateCategory>
    <templateName>
      <!-- _locID_text = "templateName" _locComment = "" -->Count Query Locks</templateName>
    <templateDescription>
      <!-- _locID_text = "templateDescription" _locComment = "" -->This template counts the number of locks acquired by each query based on the query_hash value. You can use this template to identify the most lock intensive queries for investigation and tuning.</templateDescription>
    <event package="sqlserver" name="lock_acquired">

The templateCategory provides the categorization in the template drop down, and the templateName provides the name that is displayed.  The templateDescription is what shows in the UI once the template is actually selected.  The comments within the XML are not required for the UI to display the information and the XML can be simplified down to:

    <templateCategory>Template Category</templateCategory>
    <templateName>Template Name</templateName>
    <templateDescription>Template Description</templateDescription>

By adding these elements manually into the session template XML between the event_session and first event elements, the UI can interpret the information for display about the template.  However, if the template resides in the user templates path, only the templateDescription element is rendered in the UI.  The templates in the user templates path all display in the UI as shown in Figure 2 above, but once selected the template description will populate as shown in Figure 3.

If you move the template file to the default template path, the UI will appropriately categorize the template based on the templateCategory element and will display the name in the templateName element as shown in Figure 4.

This shows two customized templates from the SQLskills collection of event sessions that be released as a set of templates that we’ve made use of in client work, and that I have written about in different blog posts and articles over the last three years while working with Extended Events.  Watch this blog for a final release of these templates in the near future.