none
Trace filter for SP:completed not working ?

    Question

  • Hello, I work on 2008 R2 server, I want to follow most exhausting stored procedures and SQL queries, when I set trace I have realized, that its storing to file milions of rows per hour , which is completely strange as we dont have such busy DBs. So I have checked that, and realized, that its causing EventClass SP:Completed (nr. 43), which is absolutely ignoring my filters.

    DECLARE @return_code INT;
    DECLARE @TraceID INT;
    DECLARE @maxfilesize BIGINT;
    DECLARE @on bit
    SET @on=1
    SET @maxfilesize = 5;
    EXEC sp_trace_create @traceid OUTPUT,@options = 2,@tracefile = N'C:\sql_trace\QueryAnalysis',@maxfilesize = @maxfilesize,@stoptime =NULL,@filecount = NULL;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 1, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 9, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 10, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 11, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 13, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 14, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 15, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 16, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 17, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 18, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 26, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 27, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 34, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 35, @on = @on;
    EXEC sp_trace_setevent @traceid = @TraceID , @eventid = 43, @columnid = 48, @on = @on;
    DECLARE @bigintFilter BIGINT;
    DECLARE @intFilter int;
    SET @bigintFilter = 10000000; 
    EXEC sp_trace_setfilter @traceid = @TraceID,@columnid = 13, @logical_operator = 1,@comparison_operator = 2,@value = @bigintFilter;
    SET @intFilter = 2500; 
    EXEC sp_trace_setfilter @traceid = @TraceID,@columnid = 18,@logical_operator = 1, @comparison_operator = 2,@value = @intFilter;
    SET @bigintFilter = 20000; 
    EXEC sp_trace_setfilter @traceid = @TraceID,@columnid = 16,@logical_operator = 1, @comparison_operator = 2,@value = @bigintFilter;
    SET @bigintFilter = 10000; 
    EXEC sp_trace_setfilter @traceid = @TraceID,@columnid = 17,@logical_operator = 1, @comparison_operator = 2,@value = @bigintFilter;  
    -----------------------------------------------------------------------------------------
    EXEC sp_trace_setstatus @TraceID,1
              

    Why is it storing all occurance of such event and not filtered ? For Eventclass 10,12,41,45 it works properly.

    See result in trace file

    EventClass ApplicationName Reads Writes CPU Duration
    43 Enterprise Vault NULL NULL NULL 0
    43 Enterprise Vault NULL NULL NULL 0
    43 Enterprise Vault NULL NULL NULL 976
    43 Enterprise Vault NULL NULL NULL 976
    43 Enterprise Vault NULL NULL NULL 0

    Thursday, September 06, 2012 8:41 AM

All replies

  • I think your issue is related to the logical operator you use. Try to set it to 0 instead of 1?

    If you are only interested in most exhausting stored procedures use something like this:

    SET @Value = 100000
    EXEC sp_trace_setfilter @TRACEID, 13 /*Duration*/, 0 /*AND*/, 4 /*>= (Greater Than Or Equal)*/, @Value /*Value*/


    Geert Vanhove



    Thursday, September 06, 2012 9:08 AM
  • But logical operator is connected to previous filter right ? I guess there should be no AND (0) in my filter

    I need to set filter CPU>2500 or Reads > 20000 or Writes>10000 or Duration>10000000

    Thursday, September 06, 2012 9:19 AM
  • sounds logical but I'm not sure how he interprets NULL values in an OR combination. I know you're into troubles in T-SQL that way

    Geert Vanhove

    Thursday, September 06, 2012 9:23 AM