Service Broker Queue Activation and Thread in SQL Server
I have recently written one article “Service Broker implementation between two tables in one database” where I have created two queues “Target Queue” and “Initiator Queue”. Following is the code of one of the queue.
CREATE QUEUE OrderTargetQueue WITH STATUS = ON, ACTIVATION( STATUS=ON, PROCEDURE_NAME = OrderTargetQueueReader, --You can change queue reader number based on your queue load MAX_QUEUE_READERS = 1, EXECUTE AS OWNER ); GO
After reading and successfully implementing that example, one of the readers had difficulty to understand the script I have given for the queue so thought to give some insight about important part of this script.
Second line of this script is “WITH STATUS = ON” right below the “CREATE QUEUE” line indicates that the queue we are creating is enabled. Use “OFF” status either in “CREATE QUEUE” or in “ALTER QUEUE” to disable the queue. If you disable the queue, queue will stop accepting messages.
If queue is enable and active, it supposes to have some property setup to define its behavior. Third line of the code “ACTIVATION (” defines those property.
Fourth line “STATUS=ON,” right below “ACTIVATION (” indicates that Queue is active and hence it processes the message it receives.
Fifth line “PROCEDURE_NAME=OrderTargetQueueReader,” defines the stored procedure which reads the message of target queue (in our case) and process it.
Sixth and most important is “MAX_QUEUE_READERS=1,” indicates that only one execution of “OrderTargetQueueReader” stored procedure keeps running, no matter whether there is one message or more than one. Sometime it happens that we have hundreds or thousands of messages to process and we need multiple execution of stored procedure as parallel execution to process message faster and clear the queue. You have to ALTER the queue and increase the thread of QUEUE based on your requirement, later on you can decrease it back.
We can understand this by one example. Suppose we go to super market to buy monthly grocery stuff. If there are very few customer, mall manager keeps one payment window open. Whenever customer increases, mall manager decides to open multiple payment windows to process payment faster so that customer doesn’t need to wait for long time in queue.
Though we had only one queue in mall so all customer stands in one row only, as soon as multiple windows get opens, customer moves to another queue to finish their payment early and this reshuffle of queue has some overhead. This same concept applies the Service Broker too and Microsoft has handled this issue very efficiently, I will come up with detail knowledge of this concept very soon.
BTW, let me show you how to ALTER the QUEUE to increase the thread.
ALTER QUEUE [OrderTargetQueue] WITH STATUS = ON , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[OrderTargetQueueReader] , MAX_QUEUE_READERS = 5 , EXECUTE AS OWNER ) GO
Till then stay tuned!!!!!!!!!!
If you wish to refer my other Service Broker related article, have a look at following list:
- Importance of Service Broker in SQL Server (Click Here)
- Introduction of Service Broker in SQL Server (Click Here)
- Enable Service Broker in SQL Server database (Click Here)
- Small demonstration of Service broker for beginners (Click Here)
- Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
- Find Service Broker Queue Count in SQL Server (Click Here)
- Get Transmission queue row count in Service Broker (Click Here)
- Purge target Transmission queue of Service Broker in SQL Server (Click Here)
- Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
- Service Broker implementation between two tables in one database (Click Here)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.