SQL Server: Building Templates of a Server Side Trace

Any 3:00 AM call is bad, but one of the worst is when you’re woken up to the words “Timeouts on the Site”. After stumbling down the stairs and logging on you need to find out what’s going on. One of the first investigations tools is to get a trace. Unfortunately the folks at Microsoft didn’t quite take this situation into account when working with a Server Side Trace. I say this because server side traces are all manual code, and unlike their cousin “Profiler” they don’t allow for templates. What this means is at 3:00 AM you’re digging through BOL to write some traces.

One of the keys to being a good DBA is to expect the 3:00AM call and be prepared in advance. Every minute your site is down your loosing money, and Page views. That’s why it’s important to have your traces ready to go. Unfortunately if you’re working with SQL Files on your filesystem you need to manually tweak them, for the appropriate situation. A different approach is to embed different traces within SP’s and change those accordingly using input parameters. Below I’ve created an example stored procedure on filtering for reads. The purpose is to get the mind thinking on what you need in your environment. I’ll explain the sproc within the code itself.


Create Proc Trace_Read_Filter
/*Input Sprocs
Notice I’ve set default values. You Most likely won’t want to change all of them. Set them up on a 95% basis. Meaning what you would
Normally use 90% of the time
*/
(
@TraceRunInMin int = 5, -- How Long in Min do you want trace to Run
@MaxFSize bigint = 20, -- Whats the max file size
@TraceDirectory Nvarchar(256) = 'C:\temp',-- Directory of Trace
@NumberOfFiles int = 5, --Number of Files before Rollover
@ReadWaterMark bigint = 500 --How many Reads do you want to Filter on
)
AS

/*
General Variables
These are either simple variables for turning on items, or ones that
Manipulate the input to provide to the nested procs
*/
Declare @traceid int -- Defined during create, used througout proc
declare @EventOnOff bit --Turn Events On
Declare @TraceOnOff bit --Turn trace on once defined
Declare @TraceStopTime datetime -- Defined adding Runinmin variables
Declare @TraceName Nvarchar(150) --Uses trace Directory to create trace name

/*
Set Internal Variables
1. Set Trace Stop Time
2. Set Trace Name, Date is used to ensure different Names
3/4 Turns on bits
*/

set @TraceStopTime = dateadd(mi, @TraceRunInMin, getdate())
set @TraceName = @TraceDirectory + '\ReadFilter' +
Replace(Replace(convert(Nvarchar(20), getdate(), 120), ':', ''), ' ', '')
Set @EventOnOff = 1
Set @TraceOnOff = 1

--Create Trace
exec sp_trace_create @traceid output, 2, @TraceName, @MaxFSize, @TraceStopTime, @NumberOfFiles

/* SET EVENTS
You need to have a statement for each event and each column. This list
Can get large fast
41 = SQL:StmtCompleted, 1 = TextData, 16 = Reads
45 = SP:StmtCompleted, 1 = TextData, 16 = Reads
12 = SQL:BatchCompleted, 1 = TextData, 16 = Reads
*/
exec sp_trace_setevent @traceid, 41, 1, @EventOnOff
exec sp_trace_setevent @traceid, 45, 1, @EventOnOff
exec sp_trace_setevent @traceid, 12, 1, @EventOnOff
exec sp_trace_setevent @traceid, 41, 16, @EventOnOff
exec sp_trace_setevent @traceid, 45, 16, @EventOnOff
exec sp_trace_setevent @traceid, 12, 16, @EventOnOff


--Filter Use Variable
exec sp_trace_setfilter @traceid, 16, 0, 2, @ReadWaterMark

--Turn on this guy on
exec sp_trace_setstatus @traceid, @status = 1



--Example of Executing for statements over 200 Reads
Exec Trace_Read_Filter @ReadWaterMark = 200