We are having issues with our service broker implementation as volume has scaled up. The code predates me, I have some experience with service broker (specifically around Event Notifications), but not a ton.
Currently, we have a SQL Server 2016 instance with 30 threads picking up messages one-at-a-time (I know, I know) from a queue and doing work upon it, but the Stored Procedure that inserts into the queue is slow with a ton of waits, causing backups in the systems that feed it. Specifically, we see a bunch of PAGELATCH_EX/PAGELATCH_SH in the filegroup that holds the service broker queues that tends to be SEND ON CONVERSATION, but also a ton of ACCESS_METHODS_HOBT_VIRTUAL_ROOT, s well as BROKER_TRANSMISSION_TABLE waits on END CONVERSATION.
First Stored Procedure, the one our app calls to deliver messages for processing.:
CREATE PROCEDURE [etl].[catch_or_release_queue_bundle] ( @xml_bundle XML )
AS
SET NOCOUNT ON
BEGIN
--Initiator Service, Target Service and the Contract
BEGIN DIALOG @InitDlgHandle
FROM SERVICE [//Bundle/Raw/SBInitiatorService]
TO SERVICE '//Bundle/Raw/SBTargetService'
ON CONTRACT [//Bundle/Raw/SBContract]
WITH ENCRYPTION=OFF, LIFETIME = 7200;
--Send the Message
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [//Bundle/Raw/RequestMessage] (@xml_bundle);
END;
The activated stored procedure that acts upon it:
CREATE PROCEDURE [dbo].[TargetActivProc]
AS
set nocount on
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg xml;
DECLARE @RecvReqMsgName sysname;
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
--changed from a WAITFOR (RECEIVE top(1) )TIMEOUT 5000 to use a waitfor instead
RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM sbTargetQueue
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
WAITFOR DELAY '00:00:01' --mdb 20221004 22:30 adding this so that it waits 1 second.
BREAK;
END
-- Disabling this portion. This should be the production procedure call
EXEC message_evaluator @RecvReqMsg
IF @RecvReqMsgName =
N'//Bundle/Raw/RequestMessage'
BEGIN
DECLARE @ReplyMsg NVARCHAR(100);
SELECT @ReplyMsg =
N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';
SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE
[//Bundle/Raw/ReplyMessage]
(@ReplyMsg);
END
ELSE IF @RecvReqMsgName =
N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @RecvReqDlgHandle;
END
ELSE IF @RecvReqMsgName =
N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
END CONVERSATION @RecvReqDlgHandle;
END
COMMIT TRANSACTION;
END
And the activated stored procedure for the second queue. I honestly have no idea what the purpose of this one is. My only idea is that there's a THIRD queue that was used at one point but no longer receives messages, and this is some sort of rollup SP to close all conversations? There's enough with Service Broker that they added it for a reason, I just don't know why.
CREATE PROCEDURE [dbo].[InititatorActivProc]
AS
SET NOCOUNT on
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @message_type SYSNAME;
DECLARE @message_body NVARCHAR(4000);
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
WAITFOR (
RECEIVE top(1) @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [sbInitiatorQueue]), TIMEOUT 5000;
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
IF @message_type = '//Bundle/Raw/ReplyMessage' --
BEGIN
END CONVERSATION @dh
END
ELSE
IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR ('Received error %s from service [Target]', 10, 1, @message_body) WITH LOG;
END CONVERSATION @dh
END
COMMIT TRANSACTION;
END