We've had a few problems lately with the size of the target queue growing into the multi-millions, and knock-on effects of trying to process all of these messages.
The reason for this isn't currently obvious (I have some ideas, but that's a different issue), but once the queue is huge (10-20 million messages waiting) I need to take action to speed it up. The activation sproc runs a variety of other sprocs and some of them are doing lookups on large tables - in some cases, I can clear some of the data to improve speed (and yes, regular pruning of these tables is also on my to-do list!) but the fact that the queue is processing is locking and blocking my attempts to do this.
The activation procedure, in common with every example and tutorial I have ever seen, uses an infinite loop with a break when the queue is empty - of course, with 20 million records to process, these procedures will not stop unless there's a poisoned message that stops the queue from within.
WHILE (1 = 1)
-- Receive all available messages into the table.
-- Wait 5 seconds for messages.
,CAST([message_body] AS NVARCHAR(MAX))
), TIMEOUT 5000;
IF @@ROWCOUNT = 0
My thought is to change the loop from
WHILE (1 = 1)
to something like
WHILE (DATEDIFF(minute, @StartDate, GETDATE()) < 2)
to cause the activation procedure to terminate on a regular basis, allowing me to stop the queue gracefully in a reasonable time so that I can carry out whatever remedial action is necessary before restarting it.
My question is whether this is a) a good idea and b) will work as I imagine?
I'm also curious as to the operation of the RECEIVE... when there are, say, 10 million messages in the queue, what will my activation procedure actually RECEIVE? Not all 10 million presumably - there are 32 threads processing this queue, I reuse each conversation handle for 1000 messages before retiring it.
Do I need to add a TOP 10000 or some such to the RECEIVE to allow the loop to go around again and check my 2 minute time limit?
Thanks in advance,