Monday, January 31, 2011

Sql Server–Predefined Profiler Templates

SqlServer profiler comes with some predefined templates that provide a good starting point for diagnosing issues. These templates can be found in the following folder: <ProgramFiles(x86)\Microsoft SQL Server>”\100\Tools\Profiler\Templates\Microsoft SQL Server\100”

The predefined templates can be viewed/edited from within SqlProfiler tool via the “trace template properties” dialog: (File->Templates->Edit)
image

Here is what each of the templates can be used for: (from http://sqlserverpedia.com/wiki/Profiler_Trace_Templates)

Stored procedure counts template (SP_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 (Standard.tdf)

This contains the following events:
  • Audit Login
  • Audit Logout
  • Existing Connection
  • RPC: Completed
  • SQL: Batch Completed

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 (TSQL.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 (TSQL_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 (TSQL_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 (TSQL_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 (TSQL_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 (Tuning.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.

More info:

MSDN: http://msdn.microsoft.com/en-us/library/ms190176.aspx

No comments: