Understanding throttling of Service Broker activity

Question

Hi,

I am currently desiging a real-time BI system that has data coming into a database at the same time as other people will be querying it. This worries me a little and hence I am considering using Service Broker to manage the incoming data.

Am I correct in thinking that if (for example) there is increased querying activity SQL Server can essentially "throttle back" the amount of CPU handed over to Service Broker? Is it possible to lower the priority of Service Broker processing?

Any thoughts would be appreciated.

Thanks and regards
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 1

Hi,

Thank you for your question.

I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

Thank you for your understanding and support.

Thanks,
Ai-Hua Qiu

---

Ai-hua Qiu[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

coool, I'll look forward to a reply. Thanks.

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 3

Hi Jamie,

There are two avenues to test out to throttle down Service Broker that I can think of.

You can somehow identity that there is “increased query activity” in a job and then alter the queue to reduce max_queue_readers. The problem with this approach is that if the activation stored procedure is already running it will keep running. You could create a flag in a table to check between loops in the activation stored procedure which is updated when you have “increased query activity” and then changes it and exits.

The second option is to use Resource Governor to throttle CPU for Service Broker activation stored procedures.

Thanks,

Cathy Miller

Microsoft Online Community Support

Answer 4

Cathy,

Thanks for the reply however it doesnt answer the question. I am aware of things that *I* can do to throttle service broker activity, the question was asking what throttling the engine itself will do.

For example, will SQL Server automatically reduce the number of active readers if there is a lot of none-Service Broker activity in the database?

I'm basically interested to know what SQL Server will do to mitigate the risk of blocking/deadlocking when Service Broker is being used. Will it "dial down" the Service Broker activity?

Regards
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 5

Hi Jamie,

There is no automatic throttling of Service Broker activity by the engine for blocking\deadlock. In this respect an internal activation stored procedure is no different than any other stored procedure that you might write. The decision to increase or decrease an activation stored procedure is based on the arrival rate of messages and backlog of messages in the queue and not blocking or deadlocks.

Thanks,

Cathy Miller

Microsoft Online Community Support

Answer 6

OK thanks Cathy, so it sounds as though Resource Governer would be a good way to de-prioritise Service Broker activity. Correct?

 

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 7

Hi Jamie,

Yes. That's right.

Thanks,

Cathy Miller

Microsoft Online Community Support

Answer 8

I realize this post is a little old, but I'm also needing a way of throttling CPU usage of a Service Broker activated stored procedure. 

The problem that I'm having is in identifying a Service Broker activated stored procedure in the Resource Governor classifier function.  I can't find any way of doing this. 

My thought was to identify the session ID by using the @@SPID variable and see if that existed in the sys.dm_broker_activated_tasks system view.  However, it appears that the session id is not assigned until after the login process and therefore it's null until after the classifier function returns.

 

Is there some other way of identifying an activated stored procedure from within the Resource Governor classifier function?

 

Thanks in advance,

Justin Cobbett

Answer 9

Hi,

I need to have use resource governor for my dbo.get_cus_list stored procedure.

My questions is ; is resource governor has got a limit for spical sp ?

and how do make for stored procedure ?