Easiest way to do this is to start SQL Profiler and define the events,columns and filters you want to profile. Now you can export the trace definition. (File,Export, Script Trace Definition, For SQL 2005-2008)
Now I will show you a script which I uses a lot with some additional comments and an automatic stop of the profile trace of 1 minute.
-- start script to run a profiler trace
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Extra from default export
DECLARE @EndTime DATETIME
DECLARE @OutputFileName NVARCHAR(256)
SET @OutputFileName = 'C:\Traces\tracefile'
-- Set the time the profile trace should run in minutes
SET @EndTime = DATEADD(mi,1,getdate()) -- trace will stop after 1 minute
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
EXEC @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
-- 15: Endtime
exec sp_trace_setevent @TraceID, 10, 15, @on
-- 16: Reads
exec sp_trace_setevent @TraceID, 10, 16, @on
-- 9:ClientProcessID
exec sp_trace_setevent @TraceID, 10, 9, @on
-- 17:Writes
exec sp_trace_setevent @TraceID, 10, 17, @on
-- 2:BinaryData
exec sp_trace_setevent @TraceID, 10, 2, @on
-- 10:ApplicationName
exec sp_trace_setevent @TraceID, 10, 10, @on
-- 18: CPU
exec sp_trace_setevent @TraceID, 10, 18, @on
-- 3:DatabaseID
exec sp_trace_setevent @TraceID, 10, 3, @on
-- 11:LoginName
exec sp_trace_setevent @TraceID, 10, 11, @on
-- 12:Spid
exec sp_trace_setevent @TraceID, 10, 12, @on
-- 13:Duration
exec sp_trace_setevent @TraceID, 10, 13, @on
-- 6:NTUserName
exec sp_trace_setevent @TraceID, 10, 6, @on
-- 14:Starttime
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- TraceID 3 = DatabaseID
-- 0 = equal
set @intfilter = 8
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 53952710-77cd-4820-9710-533bb536e38b'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - c8d5b4e9-a748-4c71-8cce-4c105fa2141f'
-- TraceID 12 = SPID
-- 1: SPID <> 51
set @intfilter = 51
exec sp_trace_setfilter @TraceID, 12, 0, 1, @intfilter
-- TraceID 13 = Duration
-- 4: Duration >= 2000000
set @bigintfilter = 2000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- TraceID 16 = Reads
-- 3: < 5000 Reads
set @bigintfilter = 5000
exec sp_trace_setfilter @TraceID, 16, 0, 3, @bigintfilter
-- TraceID 18 = CPU
-- 2: CPU > 100
set @intfilter = 100
exec sp_trace_setfilter @TraceID, 18, 0, 2, @intfilter
-- 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
-- End Script to run a profiler trace
Additional information about all events and columns can be found in the books online. Search for: sp_trace_setstatus, sp_trace_setfilter
After starting the trace you get a TraceID. This TraceID is needed if you want to stop the trace before the endtime has occured. For instance, you want to stop a trace after 5 minutes but the @Endtime is configured for 30 minutes.
With next statement you can stop the trace for traceID 6:
-- Stop the trace: sp_trace_setstatus 'TraceID',0
sp_trace_setstatus 6,0
-- Close the trace sp_trace_setstatus 'Traceid',2
sp_trace_setstatus 6,2
After the trace is completed you can import the trace file in a SQL table for futher analyze:
CREATE TABLE TraceResults (
TextData VARCHAR(4000),
Duration INT,
Reads INT,
Writes INT,
CPU INT,
StartTime DATETIME,
ProcedureName VARCHAR(100)
)
GO
-- store traced files in a table
INSERT INTO TraceResults
(TextData, Duration, Reads, Writes, CPU, StartTime)
SELECT TextData, Duration/1000, Reads, Writes, CPU, StartTime
FROM fn_trace_gettable('c:\traces\tracefile.trc',1)
