Keep watch on Service Broker Queue Delay in SQL Server
We have very large setup of Service Broker enabled in our live databases. Out of which, some of the nightly business processes create a huge target queue which we suppose to finish during the night time itself. If we can’t cleanup all these queue in night time, it creates additional overhead during the day time when most of the customer uses our SaaS product which we can’t afford.
This is the reason I am using one very small script which gives me approximate (not exact) idea about how much time current queue will take to complete. If it is taking longer time then I can increase the threads to process it faster to get it done in night time. BTW, one needs to keep watch on locking if threads are blocking each other, you won’t get much benefit of increasing threads.
Here is the script I am using:
EXECUTE CheckQueueDelay ‘ProviceYourQueueName’,100
CREATE PROCEDURE CheckQueueDelay
@MaxMin INT = 10
DECLARE @cnt INT
SELECT @cnt = 1
DECLARE @a INT, @b INT, @tot INT
WHILE @cnt <= @MaxMin BEGIN
SELECT @a = rows FROM GetQueueCount() WHERE name = @QueueName
IF @a = 0 break;
WAITFOR DELAY ’00:01:00′
SELECT @b = rows FROM GetQueueCount() WHERE name = @QueueName
IF @b = 0 break;
SELECT @tot = @a – @b
RAISERROR (‘Processed %d rows in 1 minute’, 10, 1, @tot) WITH NOWAIT
SELECT @tot = @b / (@a – @b)
RAISERROR (‘Need %d minutes to process remaining %d rows’, 10, 1, @tot, @b) WITH NOWAIT
SELECT @cnt = @cnt + 1
Now, you can execute this stored procedure to see how much time would it take to complete the queue. If queue is processing and it is also getting new records at the same time, you might not have exact value.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.