Problem-Solving with SQL Profiler

Using SQL Server 7.0's SQL Profiler tracing tool is like having a private investigator at your fingertips to help tune, troubleshoot, and manage your SQL Server environment. "Trace That Event with SQL Profiler," page 27, outlines SQL Server 7.0's trace architecture and shows you how to graphically define a trace with Profiler. You're now ready to dive into deeper waters: replaying Profiler traces and defining autostart traces with SQL Server 7.0's extended stored procedures for tracing. With this firm foundation, you can put Profiler and these extended stored procedures to work investigating everything from long-running queries to deadlocks.

Before the Replay
With Profiler, you can replay saved traces to debug problem applications, provide production-like scripts for test environments, tune databases, and more. But if you want to replay traces, you need to do some up-front work. First, you must define a trace to capture certain events and data columns besides the ones you're interested in. These extra events and data columns ensure that you replay the scenarios exactly as they happened. Second, you must save the trace output to a file, a table, or an SQL script.

All replays require you to capture the Connect, Disconnect, ExistingConnection, and RPC:Starting and SQL:BatchStarting events. In addition, for replays of API server-side cursors (server-side cursors that API cursor functions manage), you must capture the CursorExecute, CursorOpen, and CursorPrepare events. For replays of server-side prepared SQL statements, you add the Exec Prepared SQL and Prepare SQL events. Required data columns for replay are application name, binary data, connection ID or server process ID (SPID), database ID, event class, event subclass, host name, integer data, server name, SQL user name, start time, and text.

Note that a replay doesn't simulate captured events; it re-executes them. So when you replay a trace, you'll likely modify your database. For example, if you replay a trace that includes an INSERT operation, you might find a table with a duplicate key. To keep this problem from happening, revert your database to its initial state if you're replaying the trace against the source server (the server on which you originally traced the events).

If you want to replay the trace on a different server, make sure the database on the target server is in the same state as the one on the source server. You must also use the same logins, users, permissions, and database IDs as you did on the source server.

Using the same database IDs is tricky, especially because Microsoft discourages changing the database ID by directly modifying the sysdatabases system table. To match database IDs, you can copy the user database files from the source server to the same path on the target server, then restore a master database backup from the source server to the target server. Alternatively, you can restore a user database backup from the source server to the target server, then restore the master database backup. Either method ensures that the database files on the target server exist in their original locations and that the master database's system tables contain the original database ID. To avoid the problem entirely, simply remove the database ID data column from the trace and set the target database as the default database for each user the trace captures.

You can also control a script's synchronization level and replay rate. Just choose Settings from the Replay menu to display the Replay SQL Server dialog box. The Synchronization Level, which controls synchronization across connections, supports the following options:

• Full synchronization (default), which ensures events across all connections replay in their original order.
• Partial synchronization, which lets events on one connection start ahead of events with earlier start times on other connections when those events have been delayed.
• No synchronization, which lets events start as soon as the previous event in the same connection finishes without regard for synchronization across connections.

The Replay Rate supports the following options:

• As fast as possible (default), which starts the next event as soon as the previous event finishes.
• Maintain interval between events, which maintains the original time interval between events.
• Maintain relationship to start time, which starts events at the same time, in relationship to the trace's start time, as in the original trace.

Roll the Replay
Let's say you want to replay a trace of server-side prepared SQL statements, which are Transact-SQL (T-SQL) statements the client sends the database through ADO, OLE DB, or ODBC. SQL Server 7.0 implements server-side prepared SQL statements through the pseudo stored procedures sp_prepare and sp_execute, which the client application calls.

A call to sp_prepare causes SQL Server to prepare the T-SQL statements for execution by compiling the statements and caching the statements' execution plans. A call to sp_execute causes SQL Server to execute the precached plans, possibly many times. Each call to the stored procedures generates the RPC:BatchStarting events Prepare SQL and Exec Prepared SQL, which is why you must include these events in your trace definition.

SQL Profiler provides several sample trace definitions you can use as templates, including Sample 6: T-SQL for Replay, which you can use to generate trace output for replay. (See "Using SQL Profiler," July 1999, for more about Profiler's sample traces.) To open saved trace output for replay, select Open from the File menu and supply a trace file, table, or SQL script. You then manage the replay by using the options in Table 1, page 36. These options appear either in the Replay menu or as buttons on the toolbar.

Using Extended Stored Procedures
Some tracing functions aren't available from SQL Profiler, including running a trace on a scheduled basis, defining a trace to start when a certain event occurs or when SQL Server starts, and sending trace output to the Windows NT or Windows 2000 (Win2K) application log. To perform these functions and gain more programmatic control of your traces, you can use a set of extended stored procedures called xp_trace*.

To see how to use these extended stored procedures, let's look at a stored procedure to start a trace and a stored procedure to stop a trace. The first stored procedure, sp_start_mytrace, defines the trace's events, data columns, and filters and creates a queue to hold the captured events. It then retrieves the events from the queue and saves them to a system file. The sp_start_mytrace procedure tracks and communicates with the event queue through an integer value called a queue handle, which the procedure assigns when it creates the queue. The sp_stop_mytrace procedure then retrieves this handle to stop the queue.

Keeping track of the queue handle can be tricky. Although many techniques exist to retrieve the value, the easiest and most functional is probably creating a table that tracks all running traces and collects their queue handles, trace start times, and the computers and logins you activated the traces from. Listing 1 shows the statement to create such a table, which I call activetraces. You can check this table to see which traces are running. To stop a trace, you simply query the table for the appropriate query handle.