http://www.sqlservercentral.com/blogs/aschenbrenner/2012/07/03/service-broker-adventures-stucked-queue-monitors/

Printed 2014/04/17 12:25PM

Service Broker Adventures: Stucked Queue Monitors

2012/07/03

This week I'm in London helping a client with its large scale Service Broker deployment, mainly troubleshooting some strange problems with Service Broker and helping to improve the overall scalability and messaging throughput. Today I want to share a strange scenario with you, how a badly written Activated Stored Procedure isn't really activated by Service Broker. Let's assume the following basic setup of Service Broker, where we are sending messages from the Initiator Service to the Target Service:

CREATE DATABASE Chapter4_InternalActivation

GO

USE Chapter4_InternalActivation

GO

--*********************************************

--* Create the message type "RequestMessage"

--*********************************************

CREATE MESSAGE TYPE

[http://ssb.csharp.at/SSB_Book/c04/RequestMessage]

VALIDATION = NONE

GO

--*********************************************

--* Create the message type "ResponseMessage"

--*********************************************

CREATE MESSAGE TYPE

[http://ssb.csharp.at/SSB_Book/c04/ResponseMessage]

VALIDATION = NONE

GO

--************************************************

--* Changing the validation of the message types

--************************************************

ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c04/RequestMessage]

VALIDATION = WELL_FORMED_XML

GO

ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c04/ResponseMessage]

VALIDATION = WELL_FORMED_XML

GO

--************************************************

--* Create the contract "HelloWorldContract"

--************************************************

CREATE CONTRACT [http://ssb.csharp.at/SSB_Book/c04/HelloWorldContract]

(

[http://ssb.csharp.at/SSB_Book/c04/RequestMessage] SENT BY INITIATOR,

[http://ssb.csharp.at/SSB_Book/c04/ResponseMessage] SENT BY TARGET

)

GO

--**************************************************

--* Create a table to store the processed messages

--**************************************************

CREATE TABLE ProcessedMessages

(

ID UNIQUEIDENTIFIER NOT NULL,

MessageBody XML NOT NULL,

ServiceName NVARCHAR(MAX) NOT NULL

)

GO

--********************************************************

--* Create the queues "InitiatorQueue" and "TargetQueue"

--********************************************************

CREATE QUEUE InitiatorQueue

WITH STATUS = ON

GO

--************************************************************

--* Create the queues "InitiatorService" and "TargetService"

--************************************************************

CREATE SERVICE InitiatorService

ON QUEUE InitiatorQueue

(

[http://ssb.csharp.at/SSB_Book/c04/HelloWorldContract]

)

GO

For the Target Service an Activated Stored Procedure was written, and this Stored Procedure was driven by a configuration table. The configuration table specified if the Stored Procedure was retrieving messages from the underlying queue, or not. Have a look at the following code:

--************************************************

--* Create a simple config table

--************************************************

CREATE TABLE Config

(

QueueName SYSNAME NOT NULL PRIMARY KEY,

QueueEnabled BIT NOT NULL

)

GO

--************************************************

--* Insert a config record

--************************************************

INSERT INTO Config VALUES ('TargetQueue', 0)

GO

--************************************************************************

--* A stored procedure used for internal activation on the target queue

--************************************************************************

CREATE PROCEDURE ProcessRequestMessages

AS

DECLARE @ch UNIQUEIDENTIFIER

DECLARE @messagetypename NVARCHAR(256)

DECLARE @messagebody XML

DECLARE @responsemessage XML

DECLARE @enabled BIT;

WHILE (1=1)

BEGIN

BEGIN TRY

BEGIN TRANSACTION

-- Check if the queue is enabled

SELECT @enabled = QueueEnabled FROM Config

WHERE QueueName = 'TargetQueue'

IF (@enabled = 1)

BEGIN

WAITFOR (

RECEIVE TOP(1)

@ch = conversation_handle,

@messagetypename = message_type_name,

@messagebody = CAST(message_body AS XML)

FROM TargetQueue

), TIMEOUT 60000

IF (@@ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION

BREAK

END

IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c04/RequestMessage')

BEGIN

-- Store the received request message in a table

INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'TargetService')

-- Construct the response message

SET @responsemessage = '<HelloWorldResponse>' + @messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') + '</HelloWorldResponse>';

-- Send the response message back to the initiating service

SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c04/ResponseMessage] (@responsemessage);

-- End the conversation on the target's side

END CONVERSATION @ch;

END

IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

BEGIN

-- End the conversation

END CONVERSATION @ch;

END

END

ELSE

BEGIN

-- When the queue is not "enabled" in the config table, we just return

COMMIT TRANSACTION

BREAK

END

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

END

GO

This approach is a little bit strange, because you just have to disable activation, which leads to the same scenario, so that the Activated Stored Procedure isn't doing anything anymore (as soon as the queue is empty). This scenario was invented, because sometimes it was impossible to disable Service Broker queues, because of a Locking/Blocking scenario. The Locking/Blocking scenario occurred, because there were uncommitted transactions from the Activated Stored Procedure, which means that some locks were held forever in SQL Server, which finally blocked the disabling of the queue. See my last blog posting on this phenomenon: http://www.sqlpassion.at/blog/PermaLink,guid,fc4f98af-f42a-4b3d-872e-c31815e6fc02.aspx. So when you have changed the entry in the config table, the Activated Stored Procedure was just exiting – so far so good in the theory…

The real truth about that "solution" was the fact, that the Activated Stored Procedure wasn't ever processing messages. You might now ask why. The answer on that question was finally very easy: As soon as your Stored Procedure gets activated by Service Broker, you MUST process messages from your queue through the RECEIVE statement, otherwise Service Broker assumes that your Stored Procedure has encountered a problem, and considers the Stored Procedure to be failed. See the following remarks from Books Online (http://msdn.microsoft.com/en-us/library/ms171585(v=sql.105).aspx):

An activated stored procedure must receive messages from the queue that activated the procedure. If the stored procedure exits without receiving messages or the queue monitor detects that the stored procedure is not receiving messages after a short time-out, the queue monitor considers the stored procedure to have failed. In this case, the queue monitor stops activating the stored procedure.

But how can you now troubleshoot that specific problem to find out if Service Broker had considered your Stored Procedure to be failed? There's the DMV sys.dm_broker_queue_monitors, which shows the so-called Queue Monitors. Those components are responsible for activating your Stored Procedure. A Queue Monitor can be in 3 different states:

When a Stored Procedure is not activated for your queue, then the corresponding Queue Monitor is in the INACTIVE state. As soon as the Queue Monitor has started your Stored Procedure, the Queue Monitor goes into the NOTIFIED state. And finally the Queue Monitor goes into the RECEIVE_OCCURING state, when the Activated Stored Procedure receives messages. This means that the Queue Monitor remains in the NOTIFIED state, when your Stored Procedure isn't receiving any messages. As long as your Queue Monitor is stucked in the NOTIFIED state, you are not processing any messages from your queue!

In this specific scenario the Queue Monitor is also not moving into the RECEIVE_OCCURING state, when you change the entry in the user-defined configuration table – it just remains in the NOTIFIED state. The only solution is to disable and re-enable the queue to restart the corresponding Queue Monitor. But when your queues are blocked through locks from an uncommitted transaction… you see one problem leads to another problem… You can find here the download to the script with which you can reproduce the stucked Queue Monitor within Service Broker.

What's the moral of this story: read the f… manual and code your Activated Stored Procedures in a very robust way J. I've talked a lot to different people over the last years about Service Broker. Everyone is just scared about Service Broker, because it's such an overcomplicated technology. In my opinion Service Broker isn't really complicated, but you have to know the various design patterns behind Service Broker, and it's up to your Activated Stored Procedures what you're doing with Service Broker, and how healthy your Service Broker solution will be. Service Broker itself is very robust (he's just sending messages from A to B, nothing more), and will not cause any problems to you, almost of the time you are the trigger of the problems…

Thanks for reading

-Klaus


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.