cancel
Showing results for 
Search instead for 
Did you mean: 

Service Broker data not included

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?

0 Kudos
13 Replies
shaulis
Level 9

Re: Service Broker data not included

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.

Re: Service Broker data not included

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

0 Kudos
tdonley
Level 7

Re: Service Broker data not included

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

mandevil
Level 14

Re: Service Broker data not included

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

athornbury
Level 8

Re: Service Broker data not included

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!

mandevil
Level 14

Re: Service Broker data not included

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

athornbury
Level 8

Re: Service Broker data not included

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.

dabright
Level 7

Re: Service Broker data not included

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

0 Kudos
darichar
Level 11

Re: Service Broker data not included

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:

pastedImage_0.png

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.

pastedImage_1.png