13 Replies Latest reply on Sep 5, 2018 4:21 PM by darichar

    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')

                • Re: Service Broker data not included
                  tdonley

                  Is there an update for version 10, or does the Version 9 code still work?

                    • Re: Service Broker data not included
                      mandevil

                      Should still work. Our quick poll methodology did not change.

                        • Re: Service Broker data not included
                          athornbury

                          mandevil-

                           

                          What about in version 11.1.457? Is there an updated version of this? Or is there a different way to possibly collect Service Broker data? Thanks!

                            • Re: Service Broker data not included
                              mandevil

                              No update to the quick poll for specific collection. You'll have to still use the workaround.

                                • Re: Service Broker data not included
                                  athornbury

                                  Thank you! I applied the above to three monitored instances involved with Service Broker. Hopefully this might shed some unforeseen light on problem areas when we experience Service Broker issues.

                                    • Re: Service Broker data not included
                                      dabright

                                      Is there a good way to monitor Service Broker Queue count with DPA 12.0.3074?

                                       

                                      Do you have any more information on how to use the QuickPoll clause? I addeded:

                                      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')

                                       

                                      to it for one instance and restarted the monitor, but I don't see where I can see the results anywhere

                                        • Re: Service Broker data not included
                                          darichar

                                          By adding this quickpoll where clause, you have enabled monitoring of the Service Broker sessions within SQL Server. You can see the waits for these processes but it sounds like you want to monitor the number of rows within the queue tables themselves. You may already have a query that retrieves the number of rows within the queue tables, but if not, here is one that I have used before:

                                           

                                          SELECT q.Name, p.Rows

                                          FROM sys.objects o

                                          INNER JOIN sys.partitions p ON p.object_id = o.object_id

                                          INNER JOIN sys.objects q ON q.object_id = o.parent_object_id

                                          WHERE p.index_id = 1

                                           

                                          You can plug this into a Custom SQL Alert - Multiple Numeric Return type of alert. To create this, click on Alerts and then the Manage Alerts tab. Create the alert similar to this screen shot:

                                          Fill out the alert information similar to the following, choose the instances you want to execute it on and some thresholds. I chose to receive a medium alert if the number of rows in any of the queue tables is between 1 and 20, and a critical alert if the number is above 20. You may want to adjust these in your environment if you have a busy Service Broker and this gets too noisy. Use the test alert button to make sure it works for you and then save it. Once every 10 minutes (the execution interval) DPA will run the query against your selected instances and let you know if any queue tables have too many rows queued up in them. Let me know your thoughts.