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

set @bigintfilter = 2000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

❑ @TraceID: The @TraceID is an input parameter, which is generated from the sp_trace_create

stored procedure call.

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

column Duration.

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

a logical AND operator is used.

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

symbolize a Greater Than Or Equal operator.

❑ @bigintfilter: This specifies the value to be filtered. The T-SQL variable @bigintfilter was

initialized to 2000000 right before calling this event filter stored procedure.

With the preceding input parameters, the event filter procedure will catch trace events that have a dura-
tion greater than or equal to 2000000 microseconds (2000 milliseconds).

set @intfilter = 50
exec sp_trace_setfilter @TraceID, 18, 0, 4, @intfilter

❑ @TraceID: The @TraceID is an input parameter, which is generated from the sp_trace_create

stored procedure call.

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

column CPU.

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

a logical AND operator is used.

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

symbolize a Greater Than Or Equal operator.

❑ @intfilter: This specifies the value to be filtered. The T-SQL variable @bigintfilter was initial-

ized to 50 before calling this event filter stored procedure.

With the preceding input parameters, the event filter procedure will catch trace events that have CPU
greater than or equal to 50 milliseconds.

Since all three filters used logical AND (0) operators, the three filters will be linked together using the
AND logic. In this setup, SQL Trace will catch all events that must satisfy the three filter conditions at the
same time. The three filtering statements listed above are not easy to comprehend. However, they become
clearer after translating them into T-SQL syntax. The key is to look for how the filters are connected. In
this example, the logic operator AND is used.

-- To translate the above section of code into the T-SQL syntax for a comparison
-- purpose only.
SELECT < related data columns >
FROM < EventQueue >
WHERE TraceID = @TraceID
AND ApplicationName NOT LIKE N'SQL Server Profiler - 0dc223b7-b4ef-