How to speed up Service Broker with many jobs on the queue?

Question

I use SQL Service Broker with internal activation to move a list of jobs to the internal activated stored procedure to complete without keeping the main thread/requestor waiting for the actualy individual jobs to finish. Essentially i'm trying to free up the UI thread. The problem is, I passed 2000+ jobs to the Service broker and the messages reached the queue in about 25 mins and free'd the UI however even after an hour, it has only finished working on close to 600+ jobs I use below query to count the number waiting to be completed and it looks like its extremely slow

SELECT COUNT(*)
FROM [HMS_Test].[dbo].[HMSTargetQueueIntAct] 
WITH(NOLOCK)

Below is my activation stored procedure for your ref. Can someone please have a look and let me know whats wrong with this? How can I get the SB to finish these items on the queue quickly? Thanks in advance :)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SB_HMSTargetActivProc]
AS
BEGIN
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(1000);
DECLARE @RecvReqMsgName sysname;

DECLARE @XMLPtr int
DECLARE @ExecuteSQL nvarchar(1000)
DECLARE @CallBackSP nvarchar(100)
DECLARE @CallBackSQL nvarchar(1000)
DECLARE @SBCaller nvarchar(50)
DECLARE @LogMsg nvarchar(1000)

WHILE (1=1)
BEGIN
    BEGIN TRANSACTION;
    WAITFOR
    ( RECEIVE TOP(1)
        @RecvReqDlgHandle = conversation_handle,
        @RecvReqMsg = message_body,
        @RecvReqMsgName = message_type_name
      FROM HMSTargetQueueIntAct
    ), TIMEOUT 5000;

    IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END

    IF @RecvReqMsgName = N'//HMS/InternalAct/RequestMessage'
    BEGIN
       DECLARE @ReplyMsg NVARCHAR(100);
       SELECT @ReplyMsg = N'<ReplyMsg>ACK Message for Initiator service.</ReplyMsg>';
       SEND ON CONVERSATION @RecvReqDlgHandle
              MESSAGE TYPE 
              [//HMS/InternalAct/ReplyMessage]
              (@ReplyMsg);

        EXECUTE sp_xml_preparedocument @XMLPtr OUTPUT, @RecvReqMsg
        SELECT @ExecuteSQL = ExecuteSQL
              ,@CallBackSP = CallBackSP
              ,@SBCaller = SBCaller
        FROM OPENXML(@XMLPtr, 'RequestMsg/CommandParameters', 1)
        WITH    (ExecuteSQL nvarchar(1000) 'ExecuteSQL'
                ,CallBackSP nvarchar(1000) 'CallBackSP'
                ,SBCaller nvarchar(50) 'SBCaller'
                )
        EXEC sp_xml_removedocument @XMLPtr

        IF ((@ExecuteSQL IS NOT NULL) AND (LEN(@ExecuteSQL)>0))
        BEGIN
            SET @LogMsg='ExecuteSQL:' + @ExecuteSQL
            EXECUTE(@ExecuteSQL);
            SET @LogMsg='ExecuteSQLSuccess:' + @ExecuteSQL
            EXECute sp_LogSystemTransaction @SBCaller,@LogMsg,'SBMessage',0,''
        END
        IF ((@CallBackSP IS NOT NULL) AND (LEN(@CallBackSP)>0))
        BEGIN
            SET @CallBackSQL = @CallBackSP + ' @Sender=''sp_SB_HMSTargetActivProc'', @Res=''' + @ExecuteSQL + ''''

            SET @LogMsg='CallBackSQL:' + @CallBackSQL
            EXECute sp_LogSystemTransaction @SBCaller,@LogMsg,'SBMessage',0,''
            EXECUTE(@CallBackSQL);
        END

    END
    ELSE IF @RecvReqMsgName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
        SET @LogMsg='MessageEnd:';
        END CONVERSATION @RecvReqDlgHandle WITH CLEANUP;
    END
    ELSE IF @RecvReqMsgName = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    BEGIN
        DECLARE @message_body VARBINARY(MAX);
        DECLARE @code int;
        DECLARE @description NVARCHAR(3000);
        DECLARE @xmlMessage XML;

        SET @xmlMessage = CAST(@RecvReqMsg AS XML);

        SET @code = (
              SELECT @xmlMessage.value(
                N'declare namespace
                   brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
                       (/brokerns:Error/brokerns:Code)[1]', 
                'int')
                );

        SET @description = (
              SELECT @xmlMessage.value(
                'declare namespace
                   brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
                   (/brokerns:Error/brokerns:Description)[1]', 
                'nvarchar(3000)')
                );


        IF (@code = -8462)
        BEGIN
            SET @LogMsg='MessageEnd:';
            --EXECute sp_LogSystemTransaction @SBCaller,@LogMsg,'SBMessage',0,'';

            END CONVERSATION @RecvReqDlgHandle WITH CLEANUP;
        END
        ELSE
        BEGIN
            SET @LogMsg='ERR:' + @description + ' ' + CAST(@code AS VARCHAR(20));
            EXECute sp_LogSystemTransaction @SBCaller,@LogMsg,'SBError',0,'';

            END CONVERSATION @RecvReqDlgHandle;
        END
    END

    COMMIT TRANSACTION;

END
END

Answer 1

First and foremost I would recommend to threat this as a performance issue and approach it as any other performance issue: measure. See How to analyse SQL Server performance for a brief introduction and explicit advice on how to measure waits, IO, CPU overall, for a session or for a statement, and how to identify bottlenecks. Once you know where the bottleneck is then you can consider means to address it.

Now for something more specific to SSB. I would say that your procedure has three components that are interesting for the question:

• the queue processing, ie. the RECEIVE, END CONVERSATION
• the message parsing (XML shredding)
• the execution (EXECUTE(@ExecuteSQL))

For queue processing I recommend Writing Service Broker Procedures for how to speed up things. RECEIVE TOP(1) is the slowest possible approach. Processing in batch is faster, even much faster, if possible. To dequeue a batch you need correlated messages in the queue, which means SEND-ing many messages on a single conversation handle, see Reusing Conversation. This may complicate the application significantly. Therefore I would strongly urge you to measure and determine the bottleneck before doing such drastic changes.

For the XML shredding I concur with @BenThul, using XML data type methods is better than using MSXML procedures.

And finally there is the EXECUTE(@ExecuteSQL). This for us is a black box, only you know what is actually being executed. Not only how expensive/complex the SQL executed is, but also how likely is to block. Lock contention between this background execution and your front-end code could slow down the queue processing a great deal. Again, measure and you will know. As a side note: from the numbers you posted, I would expect the problem to be here. In my experience an activated procedure that does exactly what you do (RECEIVE TOP(1), XML parsing, SEND a response), w/o the EXECUTE, should go at a rate of about 100 messages per second and drain your queue of 2000 jobs in about 20 seconds. You observe a much slower rate, which would had me suspect the actually executed SQL.

Finally, the easy thing to try: bump up MAX_QUEUE_READERS (again, as @BenThul already pointed out):

 ALTER QUEUE HMSTargetQueueIntAct WITH ACTIVATION (MAX_QUEUE_READERS = 5)

This will allow parallel processing of requests.

You are missing proper error handling in your procedure, you should have a BEGIN TRY/BEGIN CATCH block. See Error Handling in Service Broker procedures, Error Handling and Activation, Handling exceptions that occur during the RECEIVE statement in activated procedures and Exception handling and nested transactions.

Answer 2

One thing I noticed is that this thing doesn't seem to do much. Most of the lines of code seem to be crafting a reply message for the service broker dialog.

That said, the existence of service broker means that you don't have to use sp_xml_preparedocument for your xml needs. Take a look at XQuery. In short, something like this should work:

SELECT @ExecuteSQL = @RcvReqMsg.value('(RequestMsg/CommandParameters/ExecuteSQL)[1]', 'nvarchar(1000)')
   ,@CallBackSP = @RcvReqMsg.value('(RequestMsg/CommandParameters/CallBackSP)[1]', 'nvarchar(1000)')
   ,@SBCaller = @RcvReqMsg.value('(RequestMsg/CommandParameters/SBCaller)[1]', 'nvarchar(1000)')

Secondly, it looks like the messages contain SQL to be executed in the context of the database that contains this queue. What is the performance profile of those? That is, are those your bottleneck? If those are slow, adding service broker to the mix won't magically make things go fast

Thirdly, are you allowing for more than one activation procedure to be active at a time? Check the max_readers column in sys.service_queues to answer this. If it's set to 1 and your process is such that they needn't be run serially, increase that number to run them in parallel.

Fourthly, it looks like you've written your activation procedure to process only one message before completing. Check out the example in this tutorial. Notice the while (1=1) loop. That makes the activation procedure go back to the queue for another message once it's finished with the current message

Lastly, why do you care? Service broker is an inherently asynchronous technology. If something/someone is waiting for a given message to be processed, I'd question that.