Profiler Trace Templates
SQL Server comes with a number of built-in trace templates to get you started with the Profiler. These templates define events, data columns and filters for a particular set of functionality. You can add or remove events, data columns and filters to the built-in templates just as you would with any other trace. You're also allowed to save the built-in templates after you make changes. All you have to do is choose File --> Save As --> Trace Template from within the Profiler and choose the existing template as the destination. The Profiler will ask whether you wish to overwrite the existing template.
If you define your own events, data columns and filters and find yourself choosing the same combination of these over and over you should create your own template. To create your own template, open up the Profiler and then choose File --> New --> Trace Template. This opens up a Trace Template Properties dialog which is very similar to the Trace Properties dialog. The only exception is the General tab, which has a single button that lets you specify the destination for a trace template. The default file extension for a trace template is .TDF. We recommend keeping this default.
Once you create a template you can reuse the template definition through the General tab of trace properties. Clicking the folder button next to "Template file name" brings up a new window that lets you choose the template you wish to use.
Now let us review the built-in trace templates. All built-in templates can be found in your SQL Server installation directory under Microsoft SQL Server\80\Tools\Templates\SQL Profiler. Note that there are two copies of each built-in template: one copy is prefixed by SQLProfiler and the other by SQLServerProfiler. Other than the name, there are no differences between the two copies of the same template. All built-in traces exclude the events generated by the Profiler.
Stored procedure counts template (SQLProfilerSP_Counts.tdf)
This contains a single event of SP:Starting and data columns of EventClass, ServerName, DatabaseID, ObjectID and SPID. This trace is very simple: it records each time a stored procedure is executed along with object identifier of the stored procedure found in the sysobjects system table. Data is ordered by the object identifier of the stored procedure, so it is easy to count the number of executions of each procedure. This trace is useful for identifying stored procedures that are executed most often; these would be good candidates for optimization.
Standard template (SQLProfilerStandard.tdf)This contains the following events:
With these events, the Standard template can be used for auditing or for tuning stored procedures and SQL statements.
This template tracks the login name, NT user name, start time, CPU time used, duration, reads and writes performed by each event, application name and the text of the event. Since this trace covers such a wide range of events and data columns it's a good starting point for beginners. Most traces that start out with the Standard template will have to be customized to fit particular needs.
TSQL template (SQLProfilerTSQL.tdf)
This collects statements in the way they were submitted. This trace is a fine way to view the system activity. The events are almost identical to the Standard template, with the exception of RPC: Completed, which is replaced by RPC: Starting. If you anticipate other events happening on your system you should modify this template accordingly. The only data columns collected by TSQL template (other than required SPID and EventClass) are text data and start time.
TSQL by duration template (SQLProfilerTSQL_Duration.tdf)
This shows the SQL statements issued and the number of milliseconds each statement took. Data is ordered by the duration column starting from the least to the greatest. The only two events traced by this template by default are RPC: Completed and SQL: Batch Completed. The collected data columns include text data and duration. This trace can be helpful in tracking down the statements that take longest to complete and are therefore good candidates for tuning.
Grouped TSQL template (SQLProfilerTSQL_Grouped.tdf)
This is almost identical to the TSQL template since it collects the same events and data columns. The difference is that the trace records are grouped by application name, NT user name, login name and client process ID. This template can be useful when troubleshooting issues encountered by a particular user or group of users. You could use Grouped TSQL template, for example, when you're troubleshooting blocking locks or slow performance as reported only by a few users.
TSQL for Replay template (SQLProfilerTSQL_Replay.tdf)
This collects a wealth of detailed information about SQL statements executed against SQL Server. This trace contains all events necessary to replay the trace later on the same or different server. You could replay an existing trace for a number of reasons - for testing new functionality for performance, to see if the blocking locks occur again if the same set of statements is executed after changing indexes, and so fort. Click here for more information on replaying traces.
TSQL within Stored Procedures template (SQLProfilerTSQL_SPs.tdf)
This shows you SQL statements executed by each stored procedure. This template is great for debugging poorly performing stored procedures; you might wish to add the duration data column to this template since it is important to know which statement took a long time to execute. This template is also useful for debugging nested stored procedures (that is, procedures that call other procedures). Events collected by TSQL within Stored Procedures are nearly identical to those of TSQL template, with the addition of SP: Stmt Starting. Data is ordered by the start time of each statement.
Tuning template (SQLProfilerTuning.tdf)
This tracks stored procedures and SQL statements executed against SQL Server. This template includes duration column by default, allowing to quickly pinpoint the long running queries.