Fixing ACCESS_METHODS_HOBT_VIRTUAL_ROOT and PAGELATCH_SH waits with Service Broker SEND ON CONVERSATION?

96 Views Asked by At

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
0

There are 0 best solutions below