Trace Playback and Diagnosis

• Capture, analyze, and replay SQL Profiler traces. Considerations include lock detection, performance tuning, and trace flags.

After you have identified the problem and implemented corrections, run the trace that was originally collected against the corrected application or process to see whether the proposed solution accomplishes the desired effect. The replay of the original trace can be a useful mechanism in designing solutions. The trace replay feature has advanced debugging support. You can make use of break points and run-to-cursor features.

To enable playback support for any particular trace, a considerable number of properties must be included in the trace definition.

Playback Requirements

A considerable number of classes must be captured to enable playback. The following event classes must be captured in a trace to allow for replay:

• Connect

• CursorExecute (when replaying server-side cursors )

• CursorOpen (when replaying server-side cursors)

• CursorPrepare (when replaying server-side cursors)

• Disconnect

• Exec Prepared SQL (when replaying server-side prepared SQL statements)

• ExistingConnection

• Prepare SQL (when replaying server-side prepared SQL statements)

• RPC:Starting

• SQL:BatchStarting

Many data column settings are also mandatory to enable playback capability. The following data columns must be captured in a trace to enable the trace to be replayed:

• Application Name

• Binary Data

• ClientProcessID or SPID

• Database ID

• Event Class

• Event Sub Class

• Host Name

• Integer Data

• Server Name

• SQL User Name

• Start Time

• Text

NOTE

Replay Template A trace template has already been prepared that can be used as a starting point for gathering traces to be replayed. The SQLProfilerTSQL_Replay trace has all required properties for traces capturing data for replay.

A variety of login requirements, described in the following list, must be in agreement between the source and target computers to enable the trace to be replayed:

• All logins and users contained in the trace must be present on the target.

• All logins and users must be in the same database as the source.

• All logins and users in the target must have the same permissions as they had in the source.

• All login passwords must be the same as the user executing the replay.

When the target computer is going to be other than the computer originally traced, you must ensure that the database IDs on the target are the same as those on the source. You can accomplish this by creating (from the source) a backup of the Master database, as well as any user databases referenced in the trace, and restoring them on the target. In this manner a test SQL Server can be used as a debugging server for any multiple-application environment.

The default database for each login contained in the trace must be set on the target. The default database of the trace activity login must be set to the database that matches that login name, even in cases where the database name might be different. To set the default database of the login, use the sp_defaultdb system stored procedure.

Traces cannot be replayed if the contents of the trace conflicts with security, file, and connection situations that may be specific to the source computer through network, interface, and other identifiers. The following trace content would not enable a trace to be replayed:

• Captured from connections using Windows Authentication Mode.

• Containing replication and other transaction log activity.

• Containing operations that involve globally unique identifiers.

• Containing operations on text , ntext , and image columns involving the bcp utility, BULK INSERT , READTEXT , WRITETEXT , and UPDATETEXT statements, and full-text operations.

• Containing session binding: sp_getbindtoken and sp_bindsession system stored procedures.

• Containing .log file operations that contain SQL Server 6.5 server-side cursor statements ( sp_cursor ).

Replays are rather involved to configure but are one of the most useful devices available to the administrator or the developer alike.

Performing the Replay

You have a lot of flexibility in playing back events from a captured trace. Replay options enable you to specify a server, output file name, and a variety of other options. Any server specified must meet the replay requirements. The output file will contain the result of replaying the trace. If Progress is selected for the output file, then the output file can also be replayed later.

You have the option of replaying the events in the order they were traced. If selected, this option enables debugging. This enables you to implement debugging techniques such as stepping through the trace. Replaying the events using multiple threads will optimize performance but will disable debugging. The default option is to display the results of the replay. If the trace you want to replay is a large capture, you may want to disable this option to save disk space.

SQL Profiler as an all-around tool is a fully functional system-debugging environment. Now it's time to look next at query analysis and optimizing.

Templates and Wizards for Specific Monitoring

The Profiler and accompanying wizard can be used to assist in index tuning. Use the Profiler's Create Trace Wizard to run the Identify Scans of Large Tables trace. This trace tells which tables are being scanned by queries as an alternative to using an index to seek the data. This should provide you with data you can use to identify which tables may need more or better indexes. Of course, the Index Tuning Wizard is also beneficial for this situation.

To help identify deadlock problems, use the Identify the Cause of a Deadlock trace from the Create Trace Wizard. Other locking problems, as well, can be identified using this trace. The data gathered provides you with the information you need to help isolate the causes of a deadlock.

If you are in need of a trace that focuses on performance problems with stored procedures, run the Profile the Performance of a Stored Procedure trace to provide you with the data you need to identify performance problems within stored procedures.

These and many other templates and wizard options are predefined and available for specific purposes. Often after tracing events, other tools can be used to provide assistance in tuning. Particularly useful for diagnosing and tuning long-running queries is the Query Analyzer. This tool interacts with the SQL Server Optimizer to assist in developing the most efficient means of query execution.