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:
- What is a Profiler Template?
- What are the various types of Templates available within the SQL Server profiler?
- How to create customized 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”.
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.
|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
Captures all client-submitted T-SQL statements and the time issued.
Use to debug client applications.
Captures all client-submitted T-SQL statements, their execution time (in milliseconds), and groups them by duration.
Use to identify slow queries.
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.
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.
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:
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).
Please find below some of the reference Books On Line documentation that I found when researching for this post:
Until we meet next time,
Be courteous. Drive responsibly.