Microsoft supplied the SQL Trace utility with SQL Server 6.x and replaced it with SQL Profiler in SQL Server 7.0. You can use SQL Trace, an Open Data Services (ODS) sniffer program, to monitor and record SQL Server 6.x database activity and troubleshoot 6.x systems. For example, you can capture five activity types (connections, SQL statements, remote procedure calls, attentions, and disconnections) within SQL Server 6.x. You can save generated traces as a trace file or an SQL script and apply five filters (login name, application, hostname, remote procedure call filter, and SQL statement filter).
Because SQL Trace is external to the SQL Server architecture, it has limited reporting capability. SQL Trace is an intrusive monitoring program that can report on only high-level events. For example, SQL Trace can report that a user is calling a certain stored procedure. If stored procedures contain many conditional statements, the same stored procedure (depending on the system state at that time and parameters passed to it) can perform wildly different actions. You cannot use SQL Trace to determine what the stored procedures will do. In addition, if you run SQL Trace on a severely stressed server, you might bring the server down.
SQL Server stores trace definitions in the Registry. If the user has appropriate permissions, SQL Server stores those permissions in the Registry of the SQL Server being traced. If the user doesn't have appropriate permissions, SQL Server stores user permissions in the Registry of the machine performing the trace. The location is not negotiable. If many developers or database administrators (DBAs) define traces, one server (typically a development server) could hold many trace definitions.
Although you can still use the old stored procedure xp_trace (a stored procedure for controlling trace activity on a server), SQL Server Books Online (BOL) states that this stored procedure is "for backward compatibility only and may not be supported in future versions." In a future article, I'll tell you about a richer mechanism you can use for profiling that involves about 65 stored procedures, but this method of controlling profiles is beyond the scope of this article.
