Based on popular demand from my colleagues and you, the kind reader, I am currently writing a series of posts on the SQL Server Profiler. You can read the first part on What is a SQL Trace? Permissions, space requirements and launching the Profiler. 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: TSQL_Duration, TSQL_Locks and TSQL_SPs.

Template Name	Description
Blank	This is a blank template that can be used during template customization
SP_Counts	Captures the event raised whenever a stored procedure starts execution
Standard	This is the default trace. This is the most generic and commonly used trace that captures: 1. Connections (connect and disconnects) 2. Completion of Remote Procedure Calls 3. Start & completion of T-SQL statements & batches
TSQL	Captures all client-submitted T-SQL statements and the time issued. Use to debug client applications.
TSQL_Duration	Captures all client-submitted T-SQL statements, their execution time (in milliseconds), and groups them by duration. Use to identify slow queries.
TSQL_Grouped	Captures all client-submitted T-SQL statements and the time they were issued. Groups information by user or client that submitted the statement. Use to investigate queries from a particular client or user.
TSQL_Locks	Captures the client-submitted T-SQL statements and associated exceptional lock information. Use to troubleshoot deadlocks, lock time-out, and lock escalation events.
TSQL_Replay	Captures detailed information about Transact-SQL statements that is required if the trace will be replayed. Use to perform iterative tuning, such as benchmark testing.
TSQL_SPs	Although not the default, this is perhaps the most useful trace for most systems (provided it is not to be replayed). Captures detailed information about all executing stored procedures. Use to analyze the component steps of stored procedures. Add the SP:Recompile event if you suspect that procedures are being recompiled.
Tuning	Captures information about stored procedures and Transact-SQL batch execution. Use to produce trace output that Database Engine Tuning Advisor can use as a workload to tune databases.

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” checkbox. 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>>>AppDataRoamingMicrosoftSQL Profiler11.0TemplatesMicrosoft SQL Server110

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:

• SQL Server Profiler Templates
• Creating a trace
• Set Trace Definition Defaults

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!