Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

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?

Find more posts tagged with

Accepted answers

All comments

FormerMember

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.

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

tdonley

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

mandevil

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