Professional Web Applications Themes

sp_trace_setfilter - Microsoft SQL / MS SQL Server

I don't have time to test this, but what if you set BOTH reads and cpu to OR? I think the filters work something like: WHERE andcondition1 AND andcondition2 AND andcondition3... AND (orcondition1 OR orcondition2 OR...) Which means that if you only have 1 orcondition it will work the same as an andcondition. -- Jacco Schalkwijk MCDBA, MCSD, MCSE Database Administrator Eurostop Ltd. "Joris Laperre" <joris.laperrebumrungrad.com> wrote in message news:u4B5Y#sRDHA.2480tk2msftngp13.phx.gbl... > Hi, > > Did anyone get the sp_trace_setfilter to work with a logical operator OR ? > Below an extract from my stored procedure to start a trace: > ...

  1. #1

    Default Re: sp_trace_setfilter

    I don't have time to test this, but what if you set BOTH reads and cpu to
    OR?

    I think the filters work something like:
    WHERE andcondition1
    AND andcondition2
    AND andcondition3...
    AND (orcondition1 OR orcondition2 OR...)

    Which means that if you only have 1 orcondition it will work the same as an
    andcondition.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Joris Laperre" <joris.laperrebumrungrad.com> wrote in message
    news:u4B5Y#sRDHA.2480tk2msftngp13.phx.gbl...
    > Hi,
    >
    > Did anyone get the sp_trace_setfilter to work with a logical operator OR ?
    > Below an extract from my stored procedure to start a trace:
    >
    > -- Set the Filters
    > EXECUTE master..sp_trace_setfilter TraceID, 10, 0, 7, N'SQL
    Profiler' --
    > ApplicationName
    > EXECUTE master..sp_trace_setfilter TraceID, 3, 0, 0, dbid --
    Database
    > EXECUTE master..sp_trace_setfilter TraceID, 16, 0, 4, reads -- Reads
    > EXECUTE master..sp_trace_setfilter TraceID, 18, 1, 4, cpu -- CPU
    >
    > I want to see events for a given database that aren't generated by the
    > profiler and where either the reads OR the cpu exceed a threshold. BOL
    > doesn't say anything about operator precedence, so I also tried repeating
    > the application name and database filters in case AND takes precedence
    over
    > OR. But in either case, it ANDs all filters regardless of the logical
    > operator (third parameter: 0 = AND, 1 = OR), so I get every event with a
    > minimum number of reads AND a minimum number of cpu usage.
    >
    > Note that SQL Profiler doesn't give you the option of OR-ing filters.
    >
    > Has anyone managed to OR filters for a trace ? Any sample code ? Any other
    > information that may be useful ?
    >
    > Thanks in advance.
    >
    > Kind regards,
    > Joris Laperre
    >
    >

    Jacco Schalkwijk Guest

  2. #2

    Default Re: sp_trace_setfilter

    Hi Jacco,

    Thanks for your suggestion. I tried it but it doesn't work either; it still
    behaves as an AND.

    Kind regards,
    Joris Laperre

    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:OcJOKMuRDHA.560TK2MSFTNGP10.phx.gbl...
    > I don't have time to test this, but what if you set BOTH reads and cpu to
    > OR?
    >
    > I think the filters work something like:
    > WHERE andcondition1
    > AND andcondition2
    > AND andcondition3...
    > AND (orcondition1 OR orcondition2 OR...)
    >
    > Which means that if you only have 1 orcondition it will work the same as
    an
    > andcondition.
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Joris Laperre" <joris.laperrebumrungrad.com> wrote in message
    > news:u4B5Y#sRDHA.2480tk2msftngp13.phx.gbl...
    > > Hi,
    > >
    > > Did anyone get the sp_trace_setfilter to work with a logical operator OR
    ?
    > > Below an extract from my stored procedure to start a trace:
    > >
    > > -- Set the Filters
    > > EXECUTE master..sp_trace_setfilter TraceID, 10, 0, 7, N'SQL
    > Profiler' --
    > > ApplicationName
    > > EXECUTE master..sp_trace_setfilter TraceID, 3, 0, 0, dbid --
    > Database
    > > EXECUTE master..sp_trace_setfilter TraceID, 16, 0, 4, reads --
    Reads
    > > EXECUTE master..sp_trace_setfilter TraceID, 18, 1, 4, cpu -- CPU
    > >
    > > I want to see events for a given database that aren't generated by the
    > > profiler and where either the reads OR the cpu exceed a threshold. BOL
    > > doesn't say anything about operator precedence, so I also tried
    repeating
    > > the application name and database filters in case AND takes precedence
    > over
    > > OR. But in either case, it ANDs all filters regardless of the logical
    > > operator (third parameter: 0 = AND, 1 = OR), so I get every event with a
    > > minimum number of reads AND a minimum number of cpu usage.
    > >
    > > Note that SQL Profiler doesn't give you the option of OR-ing filters.
    > >
    > > Has anyone managed to OR filters for a trace ? Any sample code ? Any
    other
    > > information that may be useful ?
    > >
    > > Thanks in advance.
    > >
    > > Kind regards,
    > > Joris Laperre
    > >
    > >
    >
    >

    Joris Laperre Guest

  3. #3

    Default Re: sp_trace_setfilter

    OR isn't possible with the current version of trace...

    --

    Brian Moran
    Principal Mentor
    Solid Quality Learning
    SQL Server MVP
    [url]http://www.solidqualitylearning.com[/url]


    "Joris Laperre" <joris.laperrebumrungrad.com> wrote in message
    news:O7JqS4uRDHA.1324TK2MSFTNGP11.phx.gbl...
    > Hi Jacco,
    >
    > Thanks for your suggestion. I tried it but it doesn't work either; it
    still
    > behaves as an AND.
    >
    > Kind regards,
    > Joris Laperre
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:OcJOKMuRDHA.560TK2MSFTNGP10.phx.gbl...
    > > I don't have time to test this, but what if you set BOTH reads and cpu
    to
    > > OR?
    > >
    > > I think the filters work something like:
    > > WHERE andcondition1
    > > AND andcondition2
    > > AND andcondition3...
    > > AND (orcondition1 OR orcondition2 OR...)
    > >
    > > Which means that if you only have 1 orcondition it will work the same as
    > an
    > > andcondition.
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Joris Laperre" <joris.laperrebumrungrad.com> wrote in message
    > > news:u4B5Y#sRDHA.2480tk2msftngp13.phx.gbl...
    > > > Hi,
    > > >
    > > > Did anyone get the sp_trace_setfilter to work with a logical operator
    OR
    > ?
    > > > Below an extract from my stored procedure to start a trace:
    > > >
    > > > -- Set the Filters
    > > > EXECUTE master..sp_trace_setfilter TraceID, 10, 0, 7, N'SQL
    > > Profiler' --
    > > > ApplicationName
    > > > EXECUTE master..sp_trace_setfilter TraceID, 3, 0, 0, dbid --
    > > Database
    > > > EXECUTE master..sp_trace_setfilter TraceID, 16, 0, 4, reads --
    > Reads
    > > > EXECUTE master..sp_trace_setfilter TraceID, 18, 1, 4, cpu -- CPU
    > > >
    > > > I want to see events for a given database that aren't generated by the
    > > > profiler and where either the reads OR the cpu exceed a threshold. BOL
    > > > doesn't say anything about operator precedence, so I also tried
    > repeating
    > > > the application name and database filters in case AND takes precedence
    > > over
    > > > OR. But in either case, it ANDs all filters regardless of the logical
    > > > operator (third parameter: 0 = AND, 1 = OR), so I get every event with
    a
    > > > minimum number of reads AND a minimum number of cpu usage.
    > > >
    > > > Note that SQL Profiler doesn't give you the option of OR-ing filters.
    > > >
    > > > Has anyone managed to OR filters for a trace ? Any sample code ? Any
    > other
    > > > information that may be useful ?
    > > >
    > > > Thanks in advance.
    > > >
    > > > Kind regards,
    > > > Joris Laperre
    > > >
    > > >
    > >
    > >
    >
    >

    Brian Moran Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139