Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Klaus Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.

Killing me softly with Service Broker…

A few days ago, I found a very interesting behavior regarding Service Broker on a customer system. The main thing is that your activation stored procedure is not allowed to block for a very long time, otherwise you will impact the health of your whole SQL Server instance. In this blog posting I want to demonstrate this behavior to you. In the first step I have to create the whole necessary Service Broker infrastructure objects, nothing special with this – I've just stolen that code from Chapter 4 of my Service Broker book:

USE master;

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Chapter4_InternalActivation')

BEGIN

PRINT 'Dropping database ''Chapter4_InternalActivation''';

DROP DATABASE Chapter4_InternalActivation;

END

GO

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

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

--* 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;

WHILE (1=1)

BEGIN

BEGIN TRY

BEGIN TRANSACTION

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

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

END

GO

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

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

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

CREATE QUEUE InitiatorQueue

WITH STATUS = ON

GO

CREATE QUEUE TargetQueue

WITH ACTIVATION

(

STATUS = ON,

PROCEDURE_NAME = [ProcessRequestMessages],

MAX_QUEUE_READERS = 1,

EXECUTE AS SELF

)

GO

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

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

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

CREATE SERVICE InitiatorService

ON QUEUE InitiatorQueue

(

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

)

GO

CREATE SERVICE TargetService

ON QUEUE TargetQueue

(

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

)

GO

As you can see from this listing, the target queue is enabled for activation, means that the ProcessRequestMessages stored procedure automatically started by a Queue Monitor as soon as a new message arrives on that queue. The stored procedure itself just inserts the received message in the table ProcessesMessages. Now imagine that this table has a lock from some other query that is executing inside your database:

BEGIN TRANSACTION

SELECT * FROM ProcessedMessages WITH (TABLOCKX)

GO

I know that TABLOCKX is a little bit too much, but I just want to demonstrate the problem to you. When you afterwards send a new message, the activation stored procedure gets started, and finally blocks because the necessary IX lock for the INSERT statement can't be acquired:

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

--* Sending a message from the InitiatorService to the TargetService

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

BEGIN TRY

BEGIN TRANSACTION;

DECLARE @ch UNIQUEIDENTIFIER

DECLARE @msg NVARCHAR(MAX);

BEGIN DIALOG CONVERSATION @ch

FROM SERVICE [InitiatorService]

TO SERVICE 'TargetService'

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

WITH ENCRYPTION = OFF;

SET @msg =

'<HelloWorldRequest>

Klaus Aschenbrenner

</HelloWorldRequest>';

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

COMMIT;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

GO

You can now check very easily that your activated stored procedure just blocks and is doing nothing inside SQL Server:

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

--* View the currently activated stored procedures

--* The activated stored procedure is now waiting on a LCK_M_IS wait type

--* and is never *ever* finishing...

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

SELECT t.*, s.last_wait_type FROM sys.dm_broker_activated_tasks t

INNER JOIN sys.dm_exec_requests s ON s.session_id = t.spid

GO

The crazy thing about that behavior is, that you can't just stop SQL Server, you have to do a SHUTDOWN WITH NOWAIT, otherwise SQL Server will not stop anymore…:

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

-- * A normal shutdown of SQL Server will not working anymore...

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

SHUTDOWN

GO

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

-- * You have to do a SHUTDOWN WITH NOWAIT

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

SHUTDOWN WITH NO WAIT

GO

Moral of the story: make sure that you release your locks as fast as possible. The other problem with locking in Service Broker is that it yields to high LOCK_HASH spinlocks, which will burn down a lot of CPU cycles without doing anything in SQL Server – it's a very crazy scenario and in the first step very hard to explain. You can also crosscheck this with sys.dm_os_spinlock_stats. In that scenario the CPU is up to 100% but doesn't do any work regarding Service Broker – very contraprodutive! Thanks to Thomas Kejser for that tip regarding the LOCK_HASH spinlock.

Thanks for reading

-Klaus

Comments

Leave a comment on the original post [www.sqlpassion.at, opens in a new window]

Loading comments...