Today, we will attempt to answer the following questions:

1. What is a Profiler Template?
2. What are the various types of Templates available within the SQL Server profiler?
3. How to create customized templates?

Profiler Templates

We talked yesterday about SQL Traces and it’s components. SQL Server Profiler ships with templates that define the event classes and data columns to include in traces. It is important to understand that when we execute the SQL Server Profiler, we are capturing a SQL Trace and the template gives body to the trace, i.e. the template is used to represent the events and data columns captured in the trace, but the template is itself never “executed”.

Template Types

SQL Server Profiler offers predefined trace templates that allow you to easily configure the event classes that you will most likely need for specific traces. To view the list of available templates, click on the “New Trace” button on the toolbar, and then expand the “Use the template” drop-down.

NOTE: If you are using a version of the SQL Server Profiler that’s different than the SQL Server instance, then the template drop-down may be blank, and you may be required to use a customized template.

Below is a brief description of the various template types available in the SQL Server Profiler. To know more about the various Event Classes associated to each, please refer the Books On Line documentation referenced in the “References” section of this post. The ones I have used most in my career till date are: TSQLDuration, TSQLLocks and TSQL_SPs.

Customizing a Profiler trace

To have a best of all worlds, one might want to create a custom Profiler template. To create a custom template, one can follow the following steps:

Go to File >> Templates >> New Template

Select “Server Type”

Supply a name for the template, and also choose whether the template should be based on an existing template or not

Go to “Events” tab and add/remove events captured using the “Show All Events” check box.

NOTE that I have removed a few default events and have added 2 new ones

Using the “Show All Columns”, I added one additional data column capture

Go to “Column Filters” to apply filters on the data columns

NOTE: If multiple criteria are defined, the “AND” operator is used

Go to “Organize columns” to define the sequence in which columns should appear and also to group by any particular column

Once all adjustments are done, click on “Save” to save the template

The trace will then appear in the “Use the template” drop-down when starting a new trace for the given server type

Setting the Default Trace

Let’s assume that we need to set the trace we just created as the default trace. Here are the very simple sequence of steps to follow:

Navigate to File >> Templates >> Edit Template

Select the Server Type and Template Name to edit

Check the “Use as a default template for selected server type”.

NOTE: You can also use this interface to delete a trace template.

Storage & Exporting/Importing templates

Customization of templates is machine and user specific. That means that the template must be stored on the disk as a physical file. A simple file-system search reveals the location of the template file:

For SQL 2012 (code named: “Denali”), the template is available at: C:\Users\<<<user name>>>\AppData\Roaming\Microsoft\SQL Profiler\11.0\Templates\Microsoft SQL Server\110

So, here’s a tip – the next time you backup your system, also backup your templates so that you don’t have to set them up again! You can Export or Import your existing templates by going to File->Templates->Export Template/Import Template:

In my next post…

In the next post, I will be writing about the various options available to execute the trace. Finally, towards the end of the week we would look at other uses of Profiler (specifically, trace replay). References

Please find below some of the reference Books On Line documentation that I found when researching for this post:

1. SQL Server Profiler Templates
2. Creating a trace
3. Set Trace Definition Defaults