4 Replies Latest reply on Apr 22, 2016 5:13 PM by mandevil

    Service Broker data not included

    jason.triplett

      We recently made an architectural change to our product. We moved processing of data from a job to a service broker. We don't see any of the resource utilization of the service broker in Ignite. How can I get this data included?

        • Re: Service Broker data not included
          shaulis

          Hi jason.triplett,

           

          Service Broker is considered a background process, here is the 'quickpoll where clause' that you will need to add in Ignite to get all of the background processes.

           

          AND s.spid like '%'

          UNION ALL

          SELECT s1.loginame, db_name(s1.dbid) name, s1.hostname, s1.program_name, s1.sql_handle, s1.stmt_start, s1.stmt_end,

          s1.spid, CONVERT(smallint, s1.waittype) waittype, s1.lastwaittype, s1.ecid, s1.waittime,

          CONVERT(varchar(64), s1.context_info) context_info , s1.blocked , null

          FROM master..sysprocesses s1 WITH(NOLOCK)

          WHERE s1.spid < 50

          AND s1.status != 'sleeping'

          AND s1.cmd<>'AVAITING COMMAND'

          AND s1.lastwaittype NOT IN ('SLEEP_TASK')

           

          To add:

          In Ignite > Options > Advanced Options > check 'support options' box > choose database (lower right screen) Look for parameter: QuickPoll Where Clause and add the code above. Please let me know if this doesn't help, if the issue persists please send an email to support@confio.com and we'll take a look for you.

          1 of 1 people found this helpful
            • Re: Service Broker data not included
              jason.triplett

              Here's what I'm using since we upgraded to 9:

              AND s.spid LIKE '%'

              union all

              SELECT s1.loginame
              ,DB_NAME(s1.dbid) NAME
              ,s1.hostname
              ,s1.program_name
              ,isnull(r.sql_handle, s1.sql_handle) as sql_handle
              ,isnull(r.statement_start_offset, s1.stmt_start) as stmt_start
              ,isnull(r.statement_end_offset, s1.stmt_end) as stmt_end
              ,s1.spid
              ,CONVERT(SMALLINT, s1.waittype) waittype
              ,s1.lastwaittype
              ,s1.ecid
              ,s1.waittime
              ,CONVERT(VARCHAR(64), s1.context_info) context_info
              ,r.wait_resource
              ,s1.blocked
              ,r.plan_handle
              ,r.statement_start_offset
              ,r.statement_end_offset
              ,r.start_time
              ,r.query_plan_hash
              FROM master..sysprocesses s1
              LEFT OUTER JOIN sys.dm_exec_requests r ON r.session_id = s1.spid
              WHERE s1.spid < 50
              AND s1.STATUS != 'sleeping'
              AND s1.cmd <> 'AWAITING COMMAND'
              AND s1.lastwaittype NOT IN ('SLEEP_TASK')