Keep watch on Service Broker Queue Delay in SQL Server

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.

Note: I am using one user defined function “GetQueueCount” in the script below so if you don’t have function “GetQueueCount” than kindly create it first.

Here is the script I am using:

[sourcecode language=”sql”]
/*
EXECUTE CheckQueueDelay ‘ProviceYourQueueName’,100
*/
CREATE PROCEDURE CheckQueueDelay
(
@QueueName VARCHAR(100),
@MaxMin INT = 10
)
AS
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
END
GO
[/sourcecode]

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.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.