Use SQL Parameters to Overcome Ad Hoc Performance Issues

��/****************************************************/

/* Created by: SQL Server Profiler 2005             */

/* Date: 12/30/2006  10:01:02 AM         */

/****************************************************/





-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 5 



-- 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, N'C:\ProcedureCache', @maxfilesize, NULL 

if (@rc != 0) goto error



-- Client side File and Table cannot be scripted



-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 38, 1, @on

exec sp_trace_setevent @TraceID, 38, 21, @on

exec sp_trace_setevent @TraceID, 38, 10, @on

exec sp_trace_setevent @TraceID, 38, 35, @on

exec sp_trace_setevent @TraceID, 38, 12, @on

exec sp_trace_setevent @TraceID, 35, 1, @on

exec sp_trace_setevent @TraceID, 35, 10, @on

exec sp_trace_setevent @TraceID, 35, 35, @on

exec sp_trace_setevent @TraceID, 35, 12, @on





-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint



exec sp_trace_setfilter @TraceID, 35, 1, 6, N'AdventureWorks'

-- 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





exec sp_trace_setstatus 3

          , 0