Home About Us SQL Interview Book Contact Us RSS
Code Snippets
Tips & Tricks
Code Snippets

Saravana Kumar
Vinod Kumar

SQL Profiler - Template Trace

As a SQL programmers or DBA's many open and work with profiler as an extensive tool. But what we more often ignore is the standard templates that SQL Server provides with Profiler. This is a nice information to understand and use to make useful analysis out of our profiler traces.



TSQL Template (SQLProfilerTSQL.tdf)

This trace template allows you to collect all TSQL statements in the order in which they were executed. You can view the various statements from this output and debug the application for potential Deadlocks or Blocking issues.

TSQL Grouped (SQLProfilerTSQL_Grouped.tdf)

This trace template collects data similar to the above trace template, but it automatically groups the data by application name, NT Username, Login name of the user and the Client process ID. This helps in diagnosing a particular users calls and walking through the steps he had taken in SQL Server to reach that point. This will not contain the Batch completed hence can be added manually.

Stored Procedure Counts (SQLProfilerSP_Counts.tdf)

This is a typical template that helps you keep track of stored procedures and how often they have been executed in the system. The events here are also grouped but by EventClass, ServerName, DatabaseID and ObjectID. Now if I were a DBA then I would watch this closely and try tuning the SPs that are called most number of times. Since time is a criterion, instead of tuning all stored procedures it’s better to tune the heavily used one at least.

TSQL Stored Procedure (SQLProfilerTSQL_SPs.tdf)

This is a more exhaustive template available when compared to the above three. This displays the Stored procedures and the statements executing within it. It also contains an extra column to indicate the SP that invoked the SQL code.

TSQL by Duration (SQLProfilerTSQL_Duration.tdf)

This profiler template allows you to get all the TSQL code and the time it took in milliseconds to execute the same code. This template is sorted by EventClass and then by Duration.

TSQL for Replay (SQLProfilerTSQL_Replay.tdf)

This is also an exhaustive detail of the Transact-SQL statements that have been issued. This allows you to rerun the same sequence on the same or different servers.

Tuning Trace (SQLProfilerTuning.tdf)

This template simply provides detailed information on the T-SQL statements and the SP calls made. The duration of the same are also noted. This can be used for tuning exercise only.

Standard Trace (SQLProfilerStandard.tdf)

This is a standard trace and contains tons and tons of information collected. This includes a multitude of data on login/logout, SP, TSQL completion etc. This being the default is used most of the places to audit their system.

These are the standard set of templates provided by SQL Server Profiler tool. And they do give us stndard templates that can be useful to us. So nect time try to use the proper template file to get appropriate data.