Find Service Broker Queue Count in SQL Server
Service broker is having bit complicated architecture so It may be little difficult to established service broker for beginners but it is little more difficult, even for experienced person, to keep watch on the health of Service Broker.
I have implemented Service Broker for many of my clients so far and have implemented monitoring system as well so that DBAs can keep watch on the health as well as pressure on the service broker. One of the primary watch I used to have is, count the total number of queue in initiator or target queue so that we come to know that which queue (target queue or initiator queue) is having a big burden. I you see high number in any of the queue and id doesn’t decreasing since long time, it is a time to panic and investigate more.
Now, one obvious question would comes in mind that how can we check the total count of each queue? Well, I have created one small user defined function by using “sys.objects” and “sys.partitions” catalog view.
By the way, before we see the user defined function I have created, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.
- 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)
Anyway, let us continue with the user defined function:
CREATE FUNCTION[dbo].[GetQueueCount]() RETURNS TABLE AS RETURN SELECT queues.Name , parti.Rows FROM sys.objects AS SysObj INNER JOIN sys.partitions AS parti ON parti.object_id = SysObj.object_id INNER JOIN sys.objects AS queues ON SysObj.parent_object_id = queues.object_id WHERE parti.index_id = 1 GO
Once you have Table Valued Function ready in the database where you have setup service broker queues, execute the function with simple SELECT query as given below.
SELECT * FROM [GetQueueCount](); GO
Here is the screen capture of the result in my test database. You might get different queue name with different queue count based on the service broker you have setup.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.