Simple Performance Tuner Trace

My go-to template for building a performance trace from scratch. It creates a background trace that runs for a set window or until it hits a size cap, and attaches the full column set - end time, host, reads, row counts, text, writes, application, CPU, login, database, SPID, duration, start time - to RPC:Completed, SP:StmtCompleted, SQL:BatchCompleted and SQL:StmtCompleted (with SP:Completed shown commented out, since it does not capture reads/writes/CPU). Every sp_trace_setevent line is annotated with its column name, and the entire filter section is included as ready-to-uncomment examples: text like/not-like, host, app name, login, SPID, duration, reads, writes, CPU, object name, database name and row count thresholds.

------------------------------------------------------------------------------------------------------------------------
-- Create a Queue
------------------------------------------------------------------------------------------------------------------------
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

set @DateTime = DateAdd(HH, 24, GetDate()) --Run for x Hours.
set @maxfilesize = 1024 --Run untill the file reaches x MB.

--The .trc extension will be appended to the filename automatically.
exec @rc = sp_trace_create @TraceID output, 0, N'D:\TraceFileName', @maxfilesize, @DateTime
if (@rc != 0) goto error

------------------------------------------------------------------------------------------------------------------------
-- Set the events
------------------------------------------------------------------------------------------------------------------------
declare @on bit set @on = 1
--RPC:Completed--
exec sp_trace_setevent @TraceID, 10, 15, @on --EndTime
exec sp_trace_setevent @TraceID, 10, 8, @on  --HostName
exec sp_trace_setevent @TraceID, 10, 16, @on --Reads
exec sp_trace_setevent @TraceID, 10, 48, @on --RowCounts
exec sp_trace_setevent @TraceID, 10, 1, @on  --TextData
exec sp_trace_setevent @TraceID, 10, 17, @on --Writes
exec sp_trace_setevent @TraceID, 10, 10, @on --ApplicationName
exec sp_trace_setevent @TraceID, 10, 18, @on --CPU
exec sp_trace_setevent @TraceID, 10, 11, @on --LoginName
exec sp_trace_setevent @TraceID, 10, 35, @on --DatabaseName
exec sp_trace_setevent @TraceID, 10, 12, @on --SPID
exec sp_trace_setevent @TraceID, 10, 13, @on --Duration
exec sp_trace_setevent @TraceID, 10, 14, @on --StartTime
/* SP:Completed does not include Reads, Writes or CPU
--SP:Completed--
exec sp_trace_setevent @TraceID, 43, 15, @on --EndTime
exec sp_trace_setevent @TraceID, 43, 8, @on  --HostName
--exec sp_trace_setevent @TraceID, 43, 16, @on --Reads
exec sp_trace_setevent @TraceID, 43, 48, @on --RowCounts
exec sp_trace_setevent @TraceID, 43, 1, @on  --TextData
--exec sp_trace_setevent @TraceID, 43, 17, @on --Writes
exec sp_trace_setevent @TraceID, 43, 10, @on --ApplicationName
--exec sp_trace_setevent @TraceID, 43, 18, @on --CPU
exec sp_trace_setevent @TraceID, 43, 11, @on --LoginName
exec sp_trace_setevent @TraceID, 43, 35, @on --DatabaseName
exec sp_trace_setevent @TraceID, 43, 12, @on --SPID
exec sp_trace_setevent @TraceID, 43, 13, @on --Duration
exec sp_trace_setevent @TraceID, 43, 14, @on --StartTime
*/
--SP:StmtCompleted--
exec sp_trace_setevent @TraceID, 45, 15, @on --EndTime
exec sp_trace_setevent @TraceID, 45, 8, @on  --HostName
exec sp_trace_setevent @TraceID, 45, 16, @on --Reads
exec sp_trace_setevent @TraceID, 45, 48, @on --RowCounts
exec sp_trace_setevent @TraceID, 45, 1, @on  --TextData
exec sp_trace_setevent @TraceID, 45, 17, @on --Writes
exec sp_trace_setevent @TraceID, 45, 10, @on --ApplicationName
exec sp_trace_setevent @TraceID, 45, 18, @on --CPU
exec sp_trace_setevent @TraceID, 45, 11, @on --LoginName
exec sp_trace_setevent @TraceID, 45, 35, @on --DatabaseName
exec sp_trace_setevent @TraceID, 45, 12, @on --SPID
exec sp_trace_setevent @TraceID, 45, 13, @on --Duration
exec sp_trace_setevent @TraceID, 45, 14, @on --StartTime
--SQL:BatchCompleted--
exec sp_trace_setevent @TraceID, 12, 15, @on --EndTime
exec sp_trace_setevent @TraceID, 12, 8, @on  --HostName
exec sp_trace_setevent @TraceID, 12, 16, @on --Reads
exec sp_trace_setevent @TraceID, 12, 48, @on --RowCounts
exec sp_trace_setevent @TraceID, 12, 1, @on  --TextData
exec sp_trace_setevent @TraceID, 12, 17, @on --Writes
exec sp_trace_setevent @TraceID, 12, 10, @on --ApplicationName
exec sp_trace_setevent @TraceID, 12, 18, @on --CPU
exec sp_trace_setevent @TraceID, 12, 11, @on --LoginName
exec sp_trace_setevent @TraceID, 12, 35, @on --DatabaseName
exec sp_trace_setevent @TraceID, 12, 12, @on --SPID
exec sp_trace_setevent @TraceID, 12, 13, @on --Duration
exec sp_trace_setevent @TraceID, 12, 14, @on --StartTime
--SQL:StmtCompleted--
exec sp_trace_setevent @TraceID, 41, 15, @on --EndTime
exec sp_trace_setevent @TraceID, 41, 8, @on  --HostName
exec sp_trace_setevent @TraceID, 41, 16, @on --Reads
exec sp_trace_setevent @TraceID, 41, 48, @on --RowCounts
exec sp_trace_setevent @TraceID, 41, 1, @on  --TextData
exec sp_trace_setevent @TraceID, 41, 17, @on --Writes
exec sp_trace_setevent @TraceID, 41, 10, @on --ApplicationName
exec sp_trace_setevent @TraceID, 41, 18, @on --CPU
exec sp_trace_setevent @TraceID, 41, 11, @on --LoginName
exec sp_trace_setevent @TraceID, 41, 35, @on --DatabaseName
exec sp_trace_setevent @TraceID, 41, 12, @on --SPID
exec sp_trace_setevent @TraceID, 41, 13, @on --Duration
exec sp_trace_setevent @TraceID, 41, 14, @on --StartTime

------------------------------------------------------------------------------------------------------------------------
-- Set the Filters
------------------------------------------------------------------------------------------------------------------------
declare @intfilter int
declare @bigintfilter bigint

--exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%TEXT LIKE%'
--exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%TEXT NOT LIKE%'
--exec sp_trace_setfilter @TraceID, 8, 0, 6, N'%HOST NAME LIKE%'
--exec sp_trace_setfilter @TraceID, 8, 0, 7, N'%HOST NAME NOT LIKE%'
--exec sp_trace_setfilter @TraceID, 10, 0, 6, N'%APP NAME LIKE%'
--exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%APP NAME NOT LIKE%'
--exec sp_trace_setfilter @TraceID, 11, 0, 6, N'%LOGIN NAME LIKE%'
--exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%LOGIN NAME NOT LIKE%'

--set @intfilter = xxxx --SPID Equal to.
--exec sp_trace_setfilter @TraceID, 12, 0, 0, @intfilter

--set @bigintfilter = xxxx --Duration equal or greater than (microseconds).
--exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

--set @bigintfilter = xxxx --Reads equal or greater than.
--exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter

--set @bigintfilter = xxxx --Writes equal or greater than.
--exec sp_trace_setfilter @TraceID, 17, 0, 4, @bigintfilter

--set @intfilter = xxx --CPU equal or greater than.
--exec sp_trace_setfilter @TraceID, 18, 0, 4, @intfilter

--exec sp_trace_setfilter @TraceID, 34, 0, 6, N'%OBJECT NAME LIKE%'
--exec sp_trace_setfilter @TraceID, 34, 0, 7, N'%OBJECT NAME NOT LIKE%'
--exec sp_trace_setfilter @TraceID, 35, 0, 6, N'%DB NAME LIKE%'
--exec sp_trace_setfilter @TraceID, 35, 0, 7, N'%DB NAME NOT LIKE%'

--set @bigintfilter = xxxx --Row count equal or greater than.
--exec sp_trace_setfilter @TraceID, 48, 0, 4, @bigintfilter

------------------------------------------------------------------------------------------------------------------------
-- Set the trace status to start
------------------------------------------------------------------------------------------------------------------------
exec sp_trace_setstatus @TraceID, 1

------------------------------------------------------------------------------------------------------------------------
-- display trace id for future references
------------------------------------------------------------------------------------------------------------------------
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

SELECT * FROM ::fn_trace_getinfo(NULL)

------------------------------------------------------------------------------------------------------------------------
--Control running traces:
------------------------------------------------------------------------------------------------------------------------
--EXEC sp_trace_setstatus @traceid = 3, @status = 0 --Stop
--EXEC sp_trace_setstatus @traceid = 3, @status = 1 --Start
--EXEC sp_trace_setstatus @traceid = 3, @status = 2 --Delete