Follow the clues to better SQL Server performance
SQL Server 2000 Profiler is an invaluable investigative tool that lets you measure query performance, debug applications, diagnose problems, and replay saved traces. Profiler provides the client-side functionality—the GUI—that lets you define and run traces from the client against both SQL Server 2000 and SQL Server 7.0. Tracing in SQL Server 2000 and 7.0 also includes a server-side component called SQL Trace. Microsoft totally rewrote both Profiler and SQL Trace in SQL Server 2000, mainly because SQL Server 7.0 can't guarantee that it will trace all the events that you want to trace. In SQL Server 7.0, if the consumer thread that's responsible for clearing the events queue doesn't clear the queue quickly, the queue can get full and can't hold new events. In SQL Server 2000, when you have the server handle the trace data, a trace file writer is responsible both for collecting the events from the queue and for writing them to the trace file. Guaranteeing that SQL Server will trace all requested events is mandatory for those who need to fully audit SQL Server activity, especially security audit events such as object access and permission usage. "Trace That Event with SQL Profiler," April 2000, and "Problem-Solving with SQL Profiler," April 2000, include extensive coverage about how to use SQL Server 7.0 Profiler to track events; Kalen Delaney's "Tracking Down Event Clues," page 39, contains updated details about tracing with SQL Server 2000.
To define a new SQL Server 2000 Profiler trace, open Profiler from the SQL Server program group and click the New Trace button, which is the leftmost button in the toolbar—or you can choose File, New, Trace. The resulting Connect to SQL Server dialog box requests your server and security information for logging in to SQL Server. After you connect, you'll see a tabbed dialog box in which you can define general options, the events you want to capture, the data columns you want to receive with the traced events, and any pertinent filters. Many of the options in SQL Server 2000 Profiler are new, including the file-rollover option; server-side processing of trace data; certain events, data columns, and filtering capabilities; and more. Let's explore the option tabs one by one.
General
On the General tab, which Figure 1, page 34, shows, you can name the trace and change the target server for tracing. You can also choose a trace template that comes with a predefined set of events, data columns, and filters and that lets you reuse a previously saved trace definition. Some trace templates come with SQL Server, but you can also create your own templates. One template that comes with SQL Server is SQLProfilerSP_Counts, which lets you trap invocations of stored procedures.
On the General tab, you can specify a file for SQL Server to save the trace output to. Saving a trace to a file is much more efficient with SQL Server 2000 Profiler than with the Profiler in SQL Server 7.0 because the 2000 trace file uses a self-describing format. The use of a self-describing file format means that for each event, SQL Server 2000 saves only the relevant data columns, not white spaces, and some events simply point to previous events. For example, completed events such as SQL:BatchCompleted and SQL:StmtCompleted can point to their corresponding starting events—SQL:BatchStarting and SQL:StmtStarting—to save storage space. An important advantage of saving the trace output to a file is that when you reopen the file later for examination or for replay, it loads asynchronously—that is, you can start examining the file while it's still loading.
When you specify that SQL Server should save the trace output to a file, you must also set a maximum file size in the range of 1MB to 1000MB. In addition, the General tab lets you enable file rollover and specify that the server will process the trace data. If you specify a maximum file size but don't enable the file-rollover option, SQL Server stops writing events to the file after the file reaches the size you specified. Note that because newer events don't overwrite previous events, you get only the first events that fit the specified file size. However, SQL Server continues to write events to the screen. If you enable the file-rollover option, as soon as the trace file reaches the specified maximum size, SQL Server creates a new trace file.
The first trace file's name is the name you specified in the Save to file text box. SQL Server uses three elements to construct the next file's name: the file name you specified, an underscore, and a number, starting with 1 and incremented by 1 with every new file. For example, suppose you specify C:\traces\mytrace.trc as the trace filename and you leave the default maximum file size of 5MB. If you trace 17.5MB of trace data, you'll have four files: mytrace.trc, my-trace_1.trc, mytrace_2.trc, and mytrace_3.trc.
The file-rollover option gives you some important capabilities. You can start examining files while Profiler continues to send output to newer files; you don't have to wait until the trace stops before you start analyzing the trace output. Also, if your trace generates large amounts of data and your disk is nearly full, you can move the full inactive files to another disk or even to another server.
By enabling the option Server processes SQL Server trace data, you specify that the server, not the client, should handle the trace data. This means that a server-side trace file writer, which in SQL Server 2000 is based on the transaction log writer, is responsible for writing the trace data to the file. The default in Profiler is for the client to process the trace data. When you enable the option Server processes SQL Server trace data, you're guaranteed to capture all events that you requested. But this option can degrade the server's performance under stress conditions, when the server is already under a heavy load. However, if you don't enable this option—letting the client handle the trace data instead, as SQL Server 7.0 Profiler does—SQL Server might not capture all events under stress conditions.
Prev. page
[1]
2
3
4
next page 
