Category Archives: Queue Count

Count active Thread for Service Broker Queue in SQL Server

Count active Thread for Service Broker Queue in SQL Server

Since we have big service broker setup, it is very important to know for us about thread running for target queue. Target queue is the queue which suppose to do the work of actual business logic and we used to give proper naming convention to each queue. Suppose we have one billing queue then we suppose to name it as “BillingInitiatorQueue”, “BillingTargetQueue” etc.

Since target queue has responsibility to implement actual business logic, I am always interested to see how many thread currently running for each target queue. Suppose I have set 15 thread for “BillingTargetQueue” but it may happen that I have only 5 to 10 messages in queue and hence all 15 thread will not be active. If I don’t have any message in queue then obviously no thread will be active though I set 15. Sometime I may have 1000 message to process in target queue but even I have 10 thread active only. Remaining 5 thread may not be getting proper system resources or anything else. I can’t even decide whether to increase thread or decrease without knowing actual number of message, time thread is taking to process. I have already written articles about “Find Service Broker Queue Count in SQL Server (Click Here) ” and “Keep watch on Service Broker Queue Delay in SQL Server (Click Here) ”. Today I am going to show the script which will give the count of thread for target queue running at the moment.


SELECT

OBJECT_NAME(queue_id) AS QueueName,

COUNT(*) AS ThreadCount

FROM

sys.dm_broker_activated_tasks WITH (NOLOCK)

--Comment the WHERE clause to see thread count for all queue

--not only target queue

WHERE (OBJECT_NAME(queue_id) LIKE '%Target%')

GROUP BY

OBJECT_NAME(queue_id)

HAVING

(COUNT(*) > 0)

ORDER BY

ThreadCount DESC

GO

I have written few more article about service broker, have a look if you are interested.

Service Broker Articles:

  • 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)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)
  • Service Broker Queue Activation and Thread in SQL Server (Click Here)
  • Keep watch on Service Broker Queue Delay in SQL Server (Click Here)
  • Deal with message queue conversation with state CD  in Service Broker (Click Here)

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.

Find Service Broker Queue Count in SQL Server

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.

QueueCount

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.