internal activation of service broker

Hi, there,

I'm a Chinese fan of Microsoft SQL Server 2005. Through studying for MS SQL Server 2005, now I have a question about ‘internal activation of service broker’.

At the moment I have done a test. The description of test is as following:

Firstly I set the status of the activation to be off (ALTER QUEUE [dbo].[TargetQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = OFF , PROCEDURE_NAME = [dbo].[Usp_HelloWorld] , MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo' )). And filled the queue with100000 Messages. Then that running another application caused the usage of the CPU achieve 100%. Secondly reset the status of the activation to be on and monitored the ‘sys.dm_broker_activated_tasks’. The instance of the stored procedure got the maximum number 5 very quickly. The server was much slower at current.

The SQL Server 2005 Books Online says: ’an activated stored procedure typically processes one or more messages and returns a response to the service that originated the message or messages. When messages arrive faster than the stored procedure processes messages, Service Broker starts another instance of the stored procedure, up to the maximum number defined by the queue.” But it doesn’t mention the performance counter of the server, for example: If the usage of the CPU is very high, the queue readers should be as few as possible, even don't process the message of queue until the system is idle. Because a client choice service broker means he don’t care immediately process message, contrarily he care the throughput of the system and don’t bother the natural application.

So my question is whether the strategy of internal activation of service broker doesn’t care the performance counter of the server, just care the speed of process message. And the priority of queue monitor in SQL Server‘s internal processes. Unfortunately I can’t find any information about these from books online and Internet.

When messages are available, an activated procedure will run at normal prioirty, no different than running a T-SQL batch from an user connection. You should not set the max_queue_readers higher than 2 times the number CPUs available to SQL Server, better 1 x number of CPUs.

If you need to reserve more CPU room for user activity, it means you need a bigger machine (more CPUs)

HTH,
~ Remus

|||

I want to know: why SQL Server can't activate more procedures when the the system is idle and activate lower procedures or no procedures when the sysstem is busy. I think it's a good way to improve throughput and very easy for sql server to do it.

|||

You can use the product feedback to make a suggestion for this: https://connect.microsoft.com/SQLServer/Feedback

Ususaly this is achieved in practice by changing the number of maximum activated procedures based on a operation schedule (e.g. add more procedures off hours, reduce at peak hours). An Agent job or event a SSB timer message can be used to run the ALTER statement that changes this number.