Much of the SQL Server Profiler functionality is available through a set of system-stored procedures. Through these procedures, you can define a server-side trace that can be run automatically or on a scheduled basis, such as via a Task Manager Job, instead of through the Profiler GUI. Server-side traces are also useful if you are tracing information over an extended period of time, or are planning on capturing a large amount of trace information. The overhead of running a server-side trace is less than running a client-side trace with Profiler. To start a server-side trace, you need to define the trace using the trace- related system procedures. These procedures can be called from within a SQL Serverstored procedure or batch. You can define a server-side trace using the following four procedures:
You will find that manually creating procedure scripts for tracing can be rather tedious . Much of the tedium is due to the fact that many numeric parameters drive the trace execution. Tables 7.2 and 7.3 provided the ID numbers you would need to specify to for the events and data columns you would want to include in your trace. In addition, you have to call the sp_trace_setevent procedure once for each data column for each event in the trace. To set up filters, you must pass the column ID, the filter value, and numeric values for the logical operator and the column operator to the sp_trace_setfilter procedure. The logical operator can be either 0 or 1. A value of 0 indicates that the specified filter on the column should be ANDed with any other filters on the column, while a value of 1 indicates the OR operator should be applied. Table 7.4 describes the values allowed for the column operators Table 7.4. Column Operator Values for sp_trace_setfilter
Fortunately, there is an easier way of generating a trace definition script! You can set up your traces using the SQL Profiler GUI and script the trace definition to a file. Once you have the trace defined and have specified the events, data columns, and filters you want to use, select the Script Trace menu option from the File menu in Profiler to save the SQL commands to define and invoke the trace to a file. You have the option to generate a script for either SQL Server 7.0 or 2000. The script file generated contains all of the SQL, including the stored procedure executions that you will need to run to set up your server-side trace. The SQL script generated by Profiler will only contain the commands to define the events and data columns that are contained in the Profiler trace it was generated from. If you prefer a SQL script template that is a bit more dynamic for generating your server-side traces, you can use the SQL script as shown in Listing 7.2. This script provides all the event and data column options available and lets you specify which ones to use by commenting or uncommenting out the appropriate lines. It also provides some good examples and more complete syntax for using the Profiler-related sp_trace stored procedures. A copy of this script is available on the accompanying CD. Listing 7.2 A SQL Script for Creating and Starting a Server-Side Traceset nocount on go -- Declare needed variables DECLARE @Trace_Name varchar(255), @strTraceDirectory nvarchar (1000), @maxfilesize bigint, @TraceID int , @rc int, @strTraceFile nvarchar (245), @tracefile_basename nvarchar(30), @stoptime datetime -- Specify trace name, trace file directory, tracefile_basename, -- max file size in MB, and stop time for trace -- The trace directory is a directory on SQL Server machine -- If you want to write from a remote SQL Server to a local drive, -- or to a drive on another server, use UNC path and make sure SQL server has -- write access to the specified network share -- The tracefile_basename will be appended with a datetime stamp when -- the trace is run -- If stoptime if null, trace runs until manually stopped select @Trace_Name = 'Sample Trace', @strTraceDirectory = 'c:\temp', @maxfilesize = 100, @tracefile_basename = 'sampletrace', @stoptime = NULL -- Create table variables to hold desired events and columns declare @trace_events TABLE (eventid int) declare @trace_columns TABLE (columnid int) -- Specify which events to trace -- (uncomment insert statements for desired events) --insert @trace_events (eventid) values (10) -- RPC:Completed --insert @trace_events (eventid) values (11) -- RPC:Starting insert @trace_events (eventid) values (12) -- SQL:BatchCompleted --insert @trace_events (eventid) values (13) -- SQL:BatchStarting insert @trace_events (eventid) values (14) -- Login insert @trace_events (eventid) values (15) -- Logout insert @trace_events (eventid) values (16) -- Attention insert @trace_events (eventid) values (17) -- ExistingConnection --insert @trace_events (eventid) values (18) -- ServiceControl --insert @trace_events (eventid) values (19) -- DTCTransaction --insert @trace_events (eventid) values (20) -- Login Failed --insert @trace_events (eventid) values (21) -- EventLog --insert @trace_events (eventid) values (22) -- ErrorLog --insert @trace_events (eventid) values (23) -- Lock:Released --insert @trace_events (eventid) values (24) -- Lock:Acquired insert @trace_events (eventid) values (25) -- Lock:Deadlock --insert @trace_events (eventid) values (26) -- Lock:Cancel insert @trace_events (eventid) values (27) -- Lock:Timeout --insert @trace_events (eventid) values (28) -- Degree of Parallelism1 --insert @trace_events (eventid) values (29) -- Degree of Parallelism2 --insert @trace_events (eventid) values (30) -- Degree of Parallelism3 --insert @trace_events (eventid) values (31) -- Degree of Parallelism4 insert @trace_events (eventid) values (33) -- Exception --insert @trace_events (eventid) values (34) -- SP:CacheMiss --insert @trace_events (eventid) values (35) -- SP:CacheInsert --insert @trace_events (eventid) values (36) -- SP:CacheRemove --insert @trace_events (eventid) values (37) -- SP:Recompile --insert @trace_events (eventid) values (38) -- SP:CacheHit --insert @trace_events (eventid) values (39) -- SP:ExecContextHit --insert @trace_events (eventid) values (40) -- SQL:StmtStarting insert @trace_events (eventid) values (41) -- SQL:StmtCompleted insert @trace_events (eventid) values (42) -- SP:Starting insert @trace_events (eventid) values (43) -- SP:Completed --insert @trace_events (eventid) values (44) -- SP:Statement Starting insert @trace_events (eventid) values (45) -- SP:Statement Completed --insert @trace_events (eventid) values (46) -- Object:Created --insert @trace_events (eventid) values (47) -- Object:Deleted --insert @trace_events (eventid) values (48) -- Object:Opened --insert @trace_events (eventid) values (49) -- Object:Closed --insert @trace_events (eventid) values (50) -- SQL Transaction --insert @trace_events (eventid) values (51) -- Scan:Started --insert @trace_events (eventid) values (52) -- Scan:Stopped --insert @trace_events (eventid) values (53) -- CursorOpen --insert @trace_events (eventid) values (54) -- Transaction Log --insert @trace_events (eventid) values (55) -- Hash Warning insert @trace_events (eventid) values (58) -- Auto Stats insert @trace_events (eventid) values (59) -- Lock:Deadlock Chain --insert @trace_events (eventid) values (60) -- Lock:Escalation --insert @trace_events (eventid) values (61) -- OLE DB Errors --insert @trace_events (eventid) values (67) -- Execution Warnings --insert @trace_events (eventid) values (68) -- Execution Plan --insert @trace_events (eventid) values (69) -- Sort Warnings --insert @trace_events (eventid) values (70) -- CursorPrepare --insert @trace_events (eventid) values (71) -- Prepare SQL --insert @trace_events (eventid) values (72) -- Exec Prepared SQL --insert @trace_events (eventid) values (73) -- Unprepare SQL insert @trace_events (eventid) values (74) -- CursorExecute --insert @trace_events (eventid) values (75) -- CursorRecompile --insert @trace_events (eventid) values (76) -- CursorImplicitConversion --insert @trace_events (eventid) values (77) -- CursorUnprepare --insert @trace_events (eventid) values (78) -- CursorClose insert @trace_events (eventid) values (79) -- Missing Column Statistics insert @trace_events (eventid) values (80) -- Missing Join Predicate --insert @trace_events (eventid) values (81) -- Server Memory Change insert @trace_events (eventid) values (82) -- User Configurable 0 insert @trace_events (eventid) values (83) -- User Configurable 1 insert @trace_events (eventid) values (84) -- User Configurable 2 insert @trace_events (eventid) values (85) -- User Configurable 3 insert @trace_events (eventid) values (86) -- User Configurable 4 insert @trace_events (eventid) values (87) -- User Configurable 5 insert @trace_events (eventid) values (88) -- User Configurable 6 insert @trace_events (eventid) values (89) -- User Configurable 7 insert @trace_events (eventid) values (90) -- User Configurable 8 insert @trace_events (eventid) values (91) -- User Configurable 9 insert @trace_events (eventid) values (92) -- Data File Auto Grow insert @trace_events (eventid) values (93) -- Log File Auto Grow insert @trace_events (eventid) values (94) -- Data File Auto Shrink insert @trace_events (eventid) values (95) -- Log File Auto Shrink --insert @trace_events (eventid) values (96) -- Show Plan Text --insert @trace_events (eventid) values (97) -- Show Plan ALL --insert @trace_events (eventid) values (98) -- Show Plan Statistics --insert @trace_events (eventid) values (99) -- Reserved --insert @trace_events (eventid) values (100) -- RPC Output Parameter --insert @trace_events (eventid) values (101) -- Reserved --insert @trace_events (eventid) values (102) -- Audit Statement GDR --insert @trace_events (eventid) values (103) -- Audit Object GDR --insert @trace_events (eventid) values (104) -- Audit Add/Drop Login --insert @trace_events (eventid) values (105) -- Audit Login GDR --insert @trace_events (eventid) values (106) -- Audit Login Change Property --insert @trace_events (eventid) values (107) -- Audit Login Change Password --insert @trace_events (eventid) values (108) -- Audit Add Login to Server Role --insert @trace_events (eventid) values (109) -- Audit Add DB User --insert @trace_events (eventid) values (110) -- Audit Add Member to DB --insert @trace_events (eventid) values (111) -- Audit Add/Drop Role --insert @trace_events (eventid) values (112) -- App Role Pass Change --insert @trace_events (eventid) values (113) -- Audit Statement Permission --insert @trace_events (eventid) values (114) -- Audit Object Permission --insert @trace_events (eventid) values (115) -- Audit Backup/Restore --insert @trace_events (eventid) values (116) -- Audit DBCC --insert @trace_events (eventid) values (117) -- Audit Change Audit --insert @trace_events (eventid) values (118) --Audit Object Derived Permission -- Specify which data columns to include in trace -- (uncomment insert statements for desired columns) insert @trace_columns (columnid) values (1) --TextData insert @trace_columns (columnid) values (2) --BinaryData insert @trace_columns (columnid) values (3) --DatabaseID insert @trace_columns (columnid) values (4) --TransactionID insert @trace_columns (columnid) values (5) --Reserved insert @trace_columns (columnid) values (6) --NTUserName insert @trace_columns (columnid) values (7) --NTDomainName insert @trace_columns (columnid) values (8) --ClientHostName insert @trace_columns (columnid) values (9) --ClientProcessID insert @trace_columns (columnid) values (10) --ApplicationName insert @trace_columns (columnid) values (11) --SQLSecurityLoginName insert @trace_columns (columnid) values (12) --SPID insert @trace_columns (columnid) values (13) --Duration insert @trace_columns (columnid) values (14) --StartTime insert @trace_columns (columnid) values (15) --EndTime insert @trace_columns (columnid) values (16) --Reads insert @trace_columns (columnid) values (17) --Writes insert @trace_columns (columnid) values (18) --CPU insert @trace_columns (columnid) values (19) --Permissions insert @trace_columns (columnid) values (20) --Severity insert @trace_columns (columnid) values (21) --EventSubClass insert @trace_columns (columnid) values (22) --ObjectID insert @trace_columns (columnid) values (23) --Success insert @trace_columns (columnid) values (24) --IndexID insert @trace_columns (columnid) values (25) --IntegerData insert @trace_columns (columnid) values (26) --ServerName insert @trace_columns (columnid) values (27) --EventClass insert @trace_columns (columnid) values (28) --ObjectType insert @trace_columns (columnid) values (29) --NestLevel insert @trace_columns (columnid) values (30) --State insert @trace_columns (columnid) values (31) --Error insert @trace_columns (columnid) values (32) --Mode insert @trace_columns (columnid) values (33) --Handle insert @trace_columns (columnid) values (34) --ObjectName insert @trace_columns (columnid) values (35) --DatabaseName insert @trace_columns (columnid) values (36) --Filename insert @trace_columns (columnid) values (37) --ObjectOwner insert @trace_columns (columnid) values (38) --TargetRoleName insert @trace_columns (columnid) values (39) --TargetUserName insert @trace_columns (columnid) values (40) --DatabaseUserName insert @trace_columns (columnid) values (41) --LoginSID insert @trace_columns (columnid) values (42) --TargetLoginName insert @trace_columns (columnid) values (43) --TargetLoginSID insert @trace_columns (columnid) values (44) --ColumnPermissionsSet -- Build full path of tracefile -- The format of the filename is sqltrace_YYYMMDD_hhmm.trc -- The .trc extension will be appended to the filename automatically IF RIGHT (@strTraceDirectory, 1) <> '\' SELECT @strTraceDirectory = @strTraceDirectory + '\' SELECT @strTraceFile = @strTraceDirectory + @tracefile_basename + '_' + CONVERT (varchar, GETDATE(), 112) + '_' + REPLICATE ('0', 2-LEN (DATEPART (hh, GETDATE()))) + CAST (DATEPART (hh, GETDATE()) AS varchar) + REPLICATE ('0', 2-LEN (DATEPART (mi, GETDATE()))) + CAST (DATEPART (mi, GETDATE()) AS varchar) -- Create the trace definition to get the TraceID -- option values -- 2 - Rollover Trace File when maxfilesize reached -- 4 - Shutdown on error -- 6 - options 2 & 4 -- 8 - Produce black box trace exec @rc = sp_trace_create @traceid = @TraceID output, @options = 2, @tracefile = @strTraceFile, @maxfilesize = @maxfilesize, @stoptime = @stoptime if (@rc != 0) goto error -- Set the events and columns to be included in the trace -- sp_trace_setevent must be called for each column for -- each event, hence the nested cursors declare event_cursor cursor for select eventid from @trace_events declare column_cursor cursor for select columnid from @trace_columns open event_cursor declare @event_id int, @column_id int, @on bit select @on = 1 fetch event_cursor into @event_id while @@fetch_status = 0 -- for each event begin open column_cursor fetch column_cursor into @column_id while @@fetch_status = 0 -- for each data column begin exec sp_trace_setevent @traceid = @TraceID, @eventid = @event_id, @columnid = @column_id, @on = @on fetch column_cursor into @column_id end close column_cursor fetch event_cursor into @event_id end close event_cursor deallocate column_cursor deallocate event_cursor -- specify the Filters on the trace -- Add a call to sp_trace_setfilter for each filter to apply -- Logical operator is either 0 (AND) or 1 (OR) -- Value Comparison operators are as follows: -- 0 = (Equal) -- 1 <> (Not Equal) -- 2 > (Greater Than) -- 3 < (Less Than) -- 4 >= (Greater Than Or Equal) -- 5 <= (Less Than Or Equal) -- 6 LIKE -- 7 NOT LIKE exec @rc = sp_trace_setfilter @traceid = @TraceID, @columnid = 1, @logical_operator = 0, @comparison_operator = 7, @value = N'%xp_trace%' if (@rc != 0) goto error exec @rc = sp_trace_setfilter @traceid = @TraceID, @columnid = 1, @logical_operator = 0, @comparison_operator = 7, @value = N'%sp_trace%' if (@rc != 0) goto error exec @rc = sp_trace_setfilter @traceid = @TraceID, @columnid = 10, @logical_operator = 0, @comparison_operator = 7, @value = N'%Profiler%' if (@rc != 0) goto error exec @rc = sp_trace_setfilter @traceid = @TraceID, @columnid = 10, @logical_operator = 0, @comparison_operator = 7, @value = N'%SQLEM%' if (@rc != 0) goto error -- exec @rc = sp_trace_setfilter @traceid = @TraceID, -- @columnid = 10, -- @logical_operator = 0, -- @comparison_operator = 7, -- @value = N'%Query Analyzer%' -- if (@rc != 0) goto error exec @rc = sp_trace_setfilter @traceid = @TraceID, @columnid = 10, @logical_operator = 0, @comparison_operator = 7, @value = N'%SQLAgent%' if (@rc != 0) goto error -- exec sp_trace_setfilter @traceid = @TraceID, -- @columnid = 3, -- Database ID -- @logical_operator = 0, -- @comparison_operator = 0, -- @value = 8 -- Set the trace status to start the trace exec @rc = sp_trace_setstatus @traceid = @TraceID, @status = 1 if (@rc != 0) goto error -- Display trace id for future references Print 'TraceID for current trace = ' + ltrim(str(@TraceID)) goto finish error: Print 'Trace setup exited with return code = ' + ltrim(str(@rc)) finish: go
Monitoring Running TracesSQL Server 2000 provides some additional built-in user-defined functions to get information about currently running traces. Like the fn_trace_gettable function discussed previously, these functions return the information as a table result. The available functions are as follows:
Listing 7.3 Examples of Using the Built-In User-Defined Functions for Monitoring Tracesset nocount on go SELECT * FROM ::fn_trace_getinfo(default) go traceid property value ----------- ----------- ------------------------------------ 1 1 2 1 2 c:\temp\sampletrace_20020826_0459 1 3 100 1 4 NULL 1 5 1 select * from ::fn_Trace_getfilterinfo(1) go columnid logical_operator comparison_operator value ----------- ---------------- ------------------- ------------- 1 0 7 %xp_trace% 1 0 7 %sp_trace% 10 0 7 %Profiler% 10 0 7 %SQLEM% 10 0 7 %SQLAgent% The property values returned by fn_trace_getinfo are again specified as integer IDs. Table 7.5 describes these property IDs. Table 7.5. Description of Trace Property ID Values
Stopping Server-Side TracesBefore you can access the trace file generated by a server-side trace, you must first stop the trace and then close and delete the trace from SQL Server. If you specified a stop time when you started the trace, it will automatically stop and close when the stop time is reached. For example, in the SQL script in Listing 7.2, if you wanted the trace to run for 15 minutes instead of indefinitely, set the value for the stoptime variable at the beginning of the script using a command similar to the following: set @stoptime = dateadd(minute, 15, getdate()) To otherwise stop a running server-side trace, use the sp_trace_setstatus stored procedure and pass it the trace ID and a status of 0. Stopping a trace only stops gathering trace information and does not delete the trace definition from SQL Server. Essentially, it pauses the trace. You can restart the trace by passing sp_trace_setstatus a status value of 1. Once you've stopped the trace, you can close the trace and delete its definition from SQL Server by passing sp_trace_setstatus the ID of the trace you want to stop and a status of 2. Once you've closed the trace, you must redefine it before you can restart it. If you don't know the ID of the trace you want to stop, use the fn_trace_getinfo function to return a list of all running traces and select the appropriate trace ID. The following shows an example of stopping and closing a trace with a trace ID of 1: -- Set the trace status to stop exec sp_trace_setstatus 1, 0 go -- Close and Delete the trace exec sp_trace_setstatus 1, 2 go If you want to stop and close multiple traces, you must call sp_trace_setstatus twice for each trace. Listing 7.4 provides an example of a system stored procedure that you could create in SQL Server to stop a specific trace or automatically stop all currently running traces. Listing 7.4 Sample System Stored Procedure to Stop Profiler Tracesuse master go if object_id ('sp_OCI_stop_trace') is not null drop proc sp_OCI_stop_trace go create proc sp_OCI_stop_trace @TraceID int = null as if @TraceID is not null begin -- Set the trace status to stop exec sp_trace_setstatus @TraceID, 0 -- close and delete the trace exec sp_trace_setstatus @TraceID, 2 end else -- get a list of all current traces begin declare c1 cursor for SELECT distinct traceid FROM :: fn_trace_getinfo(default) open c1 fetch c1 into @TraceID while @@fetch_status = 0 begin -- Set the trace status to stop exec sp_trace_setstatus @TraceID, 0 -- close and delete the trace exec sp_trace_setstatus @TraceID, 2 fetch c1 into @TraceID end close c1 deallocate c1 end |