Tracing the server side of SQL Server 2000
The SQL Server Trace tool lets you keep track of all the operations that SQL Server performs. For SQL Server 7.0, Microsoft completely rewrote the Trace tool. Microsoft dubbed the new incarnation SQL Server Profiler. However, the SQL Server 7.0 documentation never made it completely clear that Profiler has two separate components: the Profiler and the server-side traces.
Profiler is the GUI that lets you define all the events and data you want to capture from SQL Server. A server-side trace runs a set of extended stored procedures entirely on the server, from which it captures trace data. Itzik Ben-Gan described the relationship between the SQL Server 7.0 version of Profiler and server-side traces in "Problem-Solving with SQL Profiler," April 2000, and "Trace That Event with SQL Server Profiler," April 2000.
As wonderful and powerful as the SQL Server 7.0 tracing capability is, Microsoft knew some changes were in order. Microsoft completely redesigned the trace functionality again, this time for SQL Server 2000. The Profiler GUI for SQL Server 2000 looks like the GUI for SQL Server 7.0. Only when you start to really examine all the tool's possibilities do you begin to see the differences.
The biggest reason to rewrite the tracing tools was to absolutely guarantee that SQL Server 2000 wouldn't lose any trace records under stress. SQL Server 2000 supports C2 auditing (see the sidebar "C2 Certified and Audited," page 40), and losing any trace information is unacceptable if auditing is enabled. In addition, if any records are missing, Microsoft Product Support Services (PSS) will find using Profiler to reproduce problems much more difficult.
Tracing in SQL Server 2000
SQL Server 2000 divides the tracing functionality into two separate but connected components. On the server side is the SQL Trace facility, which manages queues of events. Another thread, called a consumer thread, reads events from the queues and filters the events before it sends them to the requesting process. Events are the main unit of activity for tracing, and an event can be anything that happens inside SQL Server or between SQL Server and a client.
The other component is Profiler. You incur more overhead when you use Profiler to define and capture the events. The server-side consumer thread that captures the requested events returns OLE DB rowsets to a client-side consumer thread, then Profiler must display the rowsets' data appropriately. To reduce the amount of data sent from server to client, each row contains a minimum amount of information—an event code, a data-column code, and a value.
For example, suppose through Profiler a user requests a trace of the text (or name) of each stored procedure when it finishes, the time it finishes, how much time it took to execute, and the requestor's login ID. The server-side trace sends multiple rows to the client every time a stored procedure finishes. Each row contains the event code (43 for Stored Procedure Completed) and one of the requested data-column codes. One row exists for the procedure name, one row for the end time, one row for the execution duration, one row for the user's login name, and so forth. Other rows exist for data columns the user didn't explicitly request because every trace returns a set of default data columns. Profiler must read these columns and pivot them to display one row for each event.
Defining a Server-Side Trace
All the events that are available through Profiler are also available through a server-side trace. For example, creating a table is an event, executing a stored procedure is an event, and sending a T-SQL batch from a client to SQL Server is an event. In "What's New in SQL Server Profiler 2000," page 32, Ben-Gan describes the changes to available events in SQL Server 2000, so I won't elaborate.
You can use a set of stored procedures that Microsoft provides with SQL Server 2000 to define which events you want to trace, what data to collect, and where to save the collected information. You can specify event filters to reduce the amount of information the trace collects and stores, as you can when you use Profiler. However, when you define server-side traces, you must specify numbers instead of Profiler's meaningful names for the events and data columns.
To start a particular trace programmatically, you use the sp_trace_* internal system procedures to define a server-side trace. Although these procedures start with the characters sp_ like most system procedures, these procedures aren't written in T-SQL. If you look at the definition of these procedures through Enterprise Manager or sp_helptext, SQL Server informs you that these procedures are internal. They have the special prefix sp_, so you can invoke them from any database. You can include the calls to these procedures in any SQL Server stored procedure or batch. To use client programs such as Query Analyzer to define a server-side trace, you must know only four (or optionally five) procedures, which Table 1 shows.
Prev. page
[1]
2
3
next page 
