November 1, 2010 at 2:08 pm
I have a queue set up as follows:
CREATE QUEUE [dbo].[SBWorkQueue]
WITH STATUS = ON
, RETENTION = OFF
, ACTIVATION (
STATUS = ON
, PROCEDURE_NAME = [dbo].[ActivationProcedure]
, MAX_QUEUE_READERS = 5
, EXECUTE AS N'login'
) ON [PRIMARY]
GO
The ActivationProcedure uses a ( WHILE 1>0 ) loop to ensure that it's continually running. I've verified via Profiler that this procedure is constantly executing. I also verified that there are 5 instances of the [ActivationProcedure] running using the following:
select * from sys.dm_broker_activated_tasks
What I've read seems to imply that if an activated proc is already running, an additional one will not be started.
I don't understand how this works with MAX_QUEUE_READERS. I've also read that the activated proc will continue to receive messages until the queue is empty. If this is the case (since this proc is running continuously) would that ever interfere with having MAX_QUEUE_READERS procs active?
Any clarity is greatly appreciated. Thank you!
November 1, 2010 at 4:35 pm
Each message in the queue is supposed to be either unique, or your procs are supposed to deal with them in conversations. So, each of the queue's only deals with a single conversation at a time.
You're correct on the loops. What's supposed to happen is one queue starts up. If two messages come in the first is handed off the active queue. Since that's now busy it looks for a second reader. Since the reader's not there, it checks max_queues, finds out it can add another, boots it up and hands it the message. Now there's two hanging around.
So, the system is supposed to start knocking off the extra readers as it finds them unused, but I haven't experimented with that beyond BOL telling me that. XD I usually max it off at one reader because I don't want my asynchronous updating eating system resources, since usually its tasks are quick enough that when the system's bored it cleans itself out.
I'm not sure about your last question, though. Can you clarify?
If this is the case (since this proc is running continuously) would that ever interfere with having MAX_QUEUE_READERS procs active?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 4:44 pm
Thanks for the reply Craig!
If this is the case (since this proc is running continuously) would that ever interfere with having MAX_QUEUE_READERS procs active?
I meant that if one of the activation procedures was receiving and processing messages until the queue was empty, would the others be able to receive and process messages as well. It sounds like I'm confusing messages and conversations.
Each queue reader will continue to receive and process messages within the conversation it is already working on, is that correct then? And each queue reader works on a separate conversation?
Sorry for the confusion, thanks again!
November 1, 2010 at 4:51 pm
SwedishOrr (11/1/2010)
I meant that if one of the activation procedures was receiving and processing messages until the queue was empty, would the others be able to receive and process messages as well.
Yes. Each message (let's skip dialogs and conversations for the moments) will get processed by the next available queue_reader. Easiest way to see it is if #1 grabs a message, and it's done before the next message comes in, #1 should grab it. If #1's busy when that second message comes in, queue#2 grabs it. (Not exactly at the machine level from what I've witnessed but close enough. Kinda first come first serve for the queue, #1 doesn't get priority).
It sounds like I'm confusing messages and conversations.
Not exactly a hard thing to do. I still flip them, and I like Service Broker.
Each queue reader will continue to receive and process messages within the conversation it is already working on, is that correct then? And each queue reader works on a separate conversation?
Sorry for the confusion, thanks again!
Well... no. You've really got to dig into the mechanics of service broker to see how conversations are supposed to be handled. These articles by Adam Mechanic are some of the best I've ever seen walking you through service broker inch by inch, from both the engine and from the usability standpoints:
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 9:08 am
Thanks for the information Craig, I really appreciate it. I'll read those articles you've included.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply