Server Side Trace : Duration filter Problem

• fais

  Mr or Mrs. 500

  Points: 559

  More actions

  October 7, 2008 at 10:11 am

  #382980

  Hi all,

  I am trying to capture those stored proc which are taking more than 15 sec. So for this I have create a basic trace with filtering on "Duration" column for 15 sec. But when I start the profiler trace, trace is capturing stored proc. which are completing in less than 15 sec.

  I have also tried using Narayana Vyas "Server side tracing" , but still experiencng the same problem even though I gave filter for 15 sec and '>' Operator.

  http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

  Actually I wanted to implement "Vyas" Code. Have anyone tried this one. Please help.

  Can someone please help me out .

  Profiler script :

  -- Create a Queue

  declare @rc int

  declare @TraceID int

  declare @maxfilesize bigint

  set @maxfilesize = 5

  exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

  if (@rc != 0) goto error

  -- Set the events

  declare @on bit

  set @on = 1

  exec sp_trace_setevent @TraceID, 14, 1, @on

  exec sp_trace_setevent @TraceID, 14, 9, @on

  exec sp_trace_setevent @TraceID, 14, 6, @on

  exec sp_trace_setevent @TraceID, 14, 10, @on

  exec sp_trace_setevent @TraceID, 14, 14, @on

  exec sp_trace_setevent @TraceID, 14, 11, @on

  exec sp_trace_setevent @TraceID, 14, 12, @on

  exec sp_trace_setevent @TraceID, 15, 15, @on

  exec sp_trace_setevent @TraceID, 15, 16, @on

  exec sp_trace_setevent @TraceID, 15, 9, @on

  exec sp_trace_setevent @TraceID, 15, 13, @on

  exec sp_trace_setevent @TraceID, 15, 17, @on

  exec sp_trace_setevent @TraceID, 15, 6, @on

  exec sp_trace_setevent @TraceID, 15, 10, @on

  exec sp_trace_setevent @TraceID, 15, 14, @on

  exec sp_trace_setevent @TraceID, 15, 18, @on

  exec sp_trace_setevent @TraceID, 15, 11, @on

  exec sp_trace_setevent @TraceID, 15, 12, @on

  exec sp_trace_setevent @TraceID, 17, 12, @on

  exec sp_trace_setevent @TraceID, 17, 1, @on

  exec sp_trace_setevent @TraceID, 17, 9, @on

  exec sp_trace_setevent @TraceID, 17, 6, @on

  exec sp_trace_setevent @TraceID, 17, 10, @on

  exec sp_trace_setevent @TraceID, 17, 14, @on

  exec sp_trace_setevent @TraceID, 17, 11, @on

  exec sp_trace_setevent @TraceID, 10, 15, @on

  exec sp_trace_setevent @TraceID, 10, 16, @on

  exec sp_trace_setevent @TraceID, 10, 1, @on

  exec sp_trace_setevent @TraceID, 10, 9, @on

  exec sp_trace_setevent @TraceID, 10, 17, @on

  exec sp_trace_setevent @TraceID, 10, 2, @on

  exec sp_trace_setevent @TraceID, 10, 10, @on

  exec sp_trace_setevent @TraceID, 10, 18, @on

  exec sp_trace_setevent @TraceID, 10, 11, @on

  exec sp_trace_setevent @TraceID, 10, 12, @on

  exec sp_trace_setevent @TraceID, 10, 13, @on

  exec sp_trace_setevent @TraceID, 10, 6, @on

  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, 11, @on

  exec sp_trace_setevent @TraceID, 12, 12, @on

  exec sp_trace_setevent @TraceID, 12, 13, @on

  exec sp_trace_setevent @TraceID, 13, 12, @on

  exec sp_trace_setevent @TraceID, 13, 1, @on

  exec sp_trace_setevent @TraceID, 13, 9, @on

  exec sp_trace_setevent @TraceID, 13, 6, @on

  exec sp_trace_setevent @TraceID, 13, 10, @on

  exec sp_trace_setevent @TraceID, 13, 14, @on

  exec sp_trace_setevent @TraceID, 13, 11, @on

  -- Set the Filters

  declare @intfilter int

  declare @bigintfilter bigint

  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - c6dab3eb-c9f2-47e7-9e2d-b016ff6f7734'

  set @bigintfilter = 15000

  exec sp_trace_setfilter @TraceID, 13, 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

  Thanks.

• fais

  Mr or Mrs. 500

  Points: 559

  More actions

  October 7, 2008 at 12:03 pm

  #881869

  fyi..

  The server measures duration in microseconds.

  set @bigintfilter = 15000000