Monday, February 17, 2014

SQL Server Profiler : Create a Trace Template, Import, Export Template and Delete a Template

SQL Server Profiler

How to save a template, to import a template, export a trace template, edit a template and delete a template file?

Using SQL Server Profiler we can create our own template to trouble shoot issues like identifying slow queries, to view the locks and for few other purposes.

Steps to Save User defined Template file.
From SQL Server Profiler

File – New Trace – Server Name - Specify a server name on which you have access to run profiler and then – Connect
Trace Name - Specify Name (Optional while creating Template file)
Click on Events Selection Tab
Remove all events,

Select RPC:Completed, RPC:Starting ( in Stored Procedures Event Class)
Select SQL:BatchCompleted, SQL:BatchStarting (in TSQL Event Class)
Uncheck Show all events and Show all columns to hide unwanted information.

Click on Organize Columns, in which you can specify the order of columns and you can also group the columns on which you want view details.
Here I am not moving any column into Groups Section but changing order of SPID as first column in the Columns Section and hit OK
Hit Run and Stop
File – Save As – Trace Template – Specify Name of the Template -- Template to View Stored Procs
Once you save the template definition the same will be stored under Use the template list on General Section of New Trace window.

To Export the Template
Open SQL Profiler
File – Templates – Export Template
Server Type - Microsoft SQL Server 2008
Template Name - Select the template which we created in the earlier step – OK
File Name – Specify location and name of the template, which is stored with .tdf extension 
The same template file can be imported in other server using File – Templates – Import template
While specifying template in the profiler you can select the user defined template and then move on.
Using the above steps we can edit / delete the template.

No comments: