How to create lightweight SQL Trace?

There are times when you would want to capture database activity and the most popular tool that database engineers use is SQL Server Profiler. Note: SQL Server profiler has been deprecated and it is recommended to use Extended Events for tracing any database related activity. However, in this article, we will look into how we can use SQL Server profiler without the need to run from the SQL Server Profiler tool and how we can minimize the performance impact by creating a server side trace.

SQL Server Profiler Impacts Performance

Anytime SQL Server profiler is started, it adds to the performance of the database server. It also impacts the client machine from where it is being run. Then there’s network data round trip between the client and the server. It is recommended that if you MUST run profiler continuously on lower level environments or frequently on-demand, create a server side trace and start and stop the profiler using T-SQL.

In this example, we will look at creating a stored procedure that will help you:

1. Create and start new trace
2. Stop the trace
3. Read the trace data without the need of SQL Server Profiler tool

Since this is just an example, we will capture only two events:

1. RPC:Completed (Under Stored Procedures)
2.  SQL:BatchCompleted (Under TSQL)

Create Procedure DBA_TraceManager (@Action varchar(10)) as
SET NOCOUNT ON
--Author:  Saleem Hakani
--This is a silent, light weight, server side trace that will capture RPC:Completed and SQL:BatchCompleted
	If (@Action='Stop')
	Begin 
		--If the trace is running, get the trace id 
		Declare @StopTraceID int
		Select @StopTraceID=ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%'
		If (@StopTraceID is null)
		Begin 
		Print 'This trace is not currently running.'
		Return
		End
		If (@StopTraceID is not null)
		Begin 
		exec sp_trace_setstatus @StopTraceID, 0
		exec sp_trace_setstatus @StopTraceID, 2
		Print 'Trace has been stopped.'
		Return
		End
	End
 
	If (@Action='Read')
	Begin
		Declare @TracePath Varchar(255)
		Select @TracePath=[Path] from Sys.Traces where [Path] like '%DBA_TraceManager%'
		Select * from fn_trace_gettable ( @TracePath,default )
	End
 
If (@Action ='Start')
Begin 
--Delete any existing trace
	If Exists (Select ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%')
	Begin 
		Select 'Please either move or delete the existing trace files from ['+Path+']' from sys.traces where status=1 and [Path] like '%DBA_TraceManager%'
		Declare @CheckExistingTraceID int
		Select @CheckExistingTraceID=ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%'
		exec sp_trace_setstatus @CheckExistingTraceID, 0
		exec sp_trace_setstatus @CheckExistingTraceID, 2
	End
	If Not Exists (Select ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%')
	Begin 
	-- Create a Queue
		declare @rc int
		declare @TraceID int
		declare @maxfilesize bigint
		set @maxfilesize = 200 
		exec @rc = sp_trace_create @TraceID output, 2, N'F:\DBA_TraceManager_TraceFiles\DBA_TraceManagerFilesUAT', @maxfilesize, NULL 
		if (@rc != 0) goto error
 
		-- Set the events
		declare @on bit
		set @on = 1
		exec sp_trace_setevent @TraceID, 10, 1, @on
		exec sp_trace_setevent @TraceID, 10, 9, @on
		exec sp_trace_setevent @TraceID, 10, 2, @on
		exec sp_trace_setevent @TraceID, 10, 10, @on
		exec sp_trace_setevent @TraceID, 10, 6, @on
		exec sp_trace_setevent @TraceID, 10, 11, @on
		exec sp_trace_setevent @TraceID, 10, 12, @on
		exec sp_trace_setevent @TraceID, 10, 13, @on
		exec sp_trace_setevent @TraceID, 10, 14, @on
		exec sp_trace_setevent @TraceID, 10, 15, @on
		exec sp_trace_setevent @TraceID, 10, 16, @on
		exec sp_trace_setevent @TraceID, 10, 17, @on
		exec sp_trace_setevent @TraceID, 10, 18, @on
		exec sp_trace_setevent @TraceID, 10, 35, @on
		exec sp_trace_setevent @TraceID, 10, 48, @on
		exec sp_trace_setevent @TraceID, 12, 1, @on
		exec sp_trace_setevent @TraceID, 12, 9, @on
		exec sp_trace_setevent @TraceID, 12, 11, @on
		exec sp_trace_setevent @TraceID, 12, 6, @on
		exec sp_trace_setevent @TraceID, 12, 10, @on
		exec sp_trace_setevent @TraceID, 12, 12, @on
		exec sp_trace_setevent @TraceID, 12, 13, @on
		exec sp_trace_setevent @TraceID, 12, 14, @on
		exec sp_trace_setevent @TraceID, 12, 15, @on
		exec sp_trace_setevent @TraceID, 12, 16, @on
		exec sp_trace_setevent @TraceID, 12, 17, @on
		exec sp_trace_setevent @TraceID, 12, 18, @on
		exec sp_trace_setevent @TraceID, 12, 35, @on
		exec sp_trace_setevent @TraceID, 12, 48, @on
 
		--Start the trace since all the counters have been selected above
		exec sp_trace_setstatus @TraceID, 1
	End
 
	error: 
	select ErrorCode=@rc
	End
go

If you need to start this trace automatically every time SQL Server starts, you can schedule a SQL Agent job and select the schedule type from the job schedule option as “Start automatically when SQL Server Agent starts”. This will make sure that the trace starts as soon SQL Server agent service is started.

Note: Make sure to delete or move any old trace files from F:\DBA_TraceManager_TraceFiles\ folder before starting a new trace.

DBA_TraceManager script will help with:

1. Creating a server side lightweight trace
2. Can be started and stopped on-demand
3. Can be started automatically on server reboot (as long as SQL Agent service is set to auto start)
4. Trace data will be processed at the server side rather than at a client side
5. Auto rollover the trace file (.trc) when it reaches 200 MB
6. Has the ability to read the trace data by simply calling the stored procedure and passing @action parameter value as ‘Read’

DBA_TraceManager accepts a parameter and here are the different values it can accept:

1. @Action=’Start’ – This will allow you to start a new trace. However, make sure the trace file do not exist at the destination folder.
2. @Action=’Stop’ – Stops the trace that was created and started using the above stored procedure (i.e. DBA_TraceManager)
3. @Action=’Read’ – Allows you to read trace data while the trace is in-flight. The helps prevent the need of using SQL Server profiler tool to read profiler data.