Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 134

Next, you will see how event classes and data columns are configured. The stored procedure
sp_trace_setevent facilitates this task. It adds or removes an event class or data column to a trace. You
will look at the first execution statement exec sp_trace_setevent @TraceID, 10, 15, @on .

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on

❑ @TraceID: In the sp_trace_setevent ,the @TraceID is an input parameter, which is generated

from the sp_trace_create stored procedure call.

❑ 10: This corresponds to the second parameter, @eventid . Use an event number to setup the

@eventid . The event number 10 refers to the event class RPC:Completed.

❑ 15: This corresponds to the third parameter, @columnid . Here you will use a column ID to refer to

an event data column. For example, 15 is the EndTime.

❑ @on: This specifies whether to turn the event ON (1) or OFF (0). In this code example, the

variable @on is initialized to 1 before the stored procedure call, meaning that the event class
(specified in the @eventid ) is set to be ON, and the data column (specified in the @columnid )is
set to ON if the column ID is not null.

After the event classes and data columns are configured, the next step is to set the data column filter.
A stored procedure is used to apply a filter to a trace. There are four input parameters for this
stored procedure.

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

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 0dc223b7-b4ef-
48b2-9901-b4091f703729'

❑

@TraceID: The @TraceID is an input parameter, which is generated from the sp_trace_create
stored procedure call.

❑ 10: The second parameter is the column ID parameter. Id value 10 corresponds to the event data

column ApplicationName.

❑ 0: This logical_operator specifies whether the AND (0) or OR (1) operator is applied. In this case,

a logical AND operator is used.

❑ 7: This comparison_operator specifies the type of comparison to be made. The value 7 is used to

symbolize a ''Not Like'' operator.

❑ N'SQL Server Profiler - 0dc223b7-b4ef-48b2-9901-b4091f703729': This specifies the value to

be filtered.

The preceding input parameters instruct the event filter procedure to filter out all events generated
from the SQL Server Profiler application.

Next, you will see the setting of two more similar event filters. The two event filters statements were
generated when you set the Duration filter to greater than 2000 milliseconds and CPU filter to greater
than 50 milliseconds (see Figure 5-26).