Service Broker in SQL Server: Hand Off Background Work Reliably and Process It in Parallel

An order is saved. Afterward, totals need to be updated, documents generated, or data prepared for another system. The user should not have to wait for any of this, and no task may be lost. When enough of it accumulates, several workers should process it at the same time.

SQL Server has shipped with its own messaging service for exactly this purpose since 2005: Service Broker. Messages are stored as data in the database; sending belongs to the sender's transaction and receiving belongs to the receiver's transaction. This requires neither an external messaging server nor distributed transactions.

In the lab, we measured on SQL Server 2022 and 2025 how much of this holds up in everyday use. With the same four CPUs, a batch of 32 independent compute tasks finished in 4.6 instead of 9.4 seconds once four workers processed it through a queue. With the same queue, a different setup ran strictly one task after another despite four workers. The difference comes not from the workers but from the dialogs (conversations).

The Model: Folders with a Lock

Before we measure, we need a picture that lets us predict every result.

Picture the queue as a tray that holds folders. A folder is a Conversation Group. It contains one or more dialogs with their messages. Each worker reaches into the tray with RECEIVE and takes the next folder whose lock is open. It locks the folder until its transaction ends. Whoever reaches in afterward gets a different folder. If none is unlocked, the worker waits or leaves.

Three predictions follow from the picture:

1. How many workers work at the same time depends on the number of unlocked folders, not on the number of workers.
2. Within a folder, everything proceeds strictly in sequence. The ordering guarantee comes from this, and so does the serialization.
3. The lock holds until COMMIT. A worker that commits earlier releases the folder and in doing so also loses the protection the transaction provides against failures.

The picture has one limit: Without any action on your part, each new dialog on the receiver side gets its own group, and therefore its own folder. Experiment 3 shows how to put dialogs into one folder deliberately.

The building blocks we create for this:

Microsoft: Overview, Conversation Groups, Group locks

The Smallest Working Example

The basic setup from our lab: a database, a message type, a contract, and two services with their queues:

/* PSG Lab · Service Broker · TEST INSTANCE ONLY
   Creates BrokerLab and enables Service Broker.
   ENCRYPTION = OFF and EXECUTE AS OWNER only for this isolated lab. */
CREATE DATABASE BrokerLab;
GO
ALTER DATABASE BrokerLab SET ENABLE_BROKER;   -- requires exclusive access to the database
GO
USE BrokerLab;

CREATE MESSAGE TYPE [//lab/work] VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT [//lab/contract] ([//lab/work] SENT BY INITIATOR);

CREATE QUEUE dbo.SourceQ;   -- sender side: EndDialog replies land here
CREATE QUEUE dbo.TargetQ;   -- receiver side: the work sits here

CREATE SERVICE [//lab/source] ON QUEUE dbo.SourceQ;
CREATE SERVICE [//lab/target] ON QUEUE dbo.TargetQ ([//lab/contract]);

-- The worker logs every completed task here
CREATE TABLE dbo.WorkLog (
    run      varchar(80)      NOT NULL,
    item     int              NOT NULL,
    spid     int              NOT NULL,
    grp      uniqueidentifier NOT NULL,
    seq      bigint           NOT NULL,
    started  datetime2(7)     NOT NULL,
    finished datetime2(7)     NOT NULL,
    PRIMARY KEY (run, item)
);

The sender opens a dialog and sends twelve tasks over it. BEGIN DIALOG and SEND are in one transaction: If it rolls back, not a single task exists.

USE BrokerLab;
SET QUOTED_IDENTIFIER ON;   -- required for the XML methods

DECLARE @h uniqueidentifier, @body xml, @i int = 1;

BEGIN TRANSACTION;

BEGIN DIALOG CONVERSATION @h
    FROM SERVICE [//lab/source]
    TO SERVICE N'//lab/target'
    ON CONTRACT [//lab/contract]
    WITH ENCRYPTION = OFF;

WHILE @i <= 12
BEGIN
    SET @body = (SELECT 'one-dialog' AS [@run], @i AS [@id],
                        CONVERT(bit, CASE WHEN @i = 12 THEN 1 ELSE 0 END) AS [@close]
                 FOR XML PATH('w'), TYPE);
    SEND ON CONVERSATION @h MESSAGE TYPE [//lab/work] (@body);
    SET @i += 1;
END;

COMMIT;

The close attribute is an agreement for this example only: The receiver ends the dialog after the last task, and the sender cleans up its side as soon as the EndDialog message arrives there.

The worker is an ordinary procedure. All that matters is where the transaction begins and ends:

CREATE PROCEDURE dbo.Work
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @h uniqueidentifier, @g uniqueidentifier, @b varbinary(max),
            @t nvarchar(256), @s bigint, @x xml, @run varchar(80), @id int,
            @close bit, @started datetime2(7), @delay varchar(12);

    WHILE 1 = 1
    BEGIN
        BEGIN TRANSACTION;                    -- (1) receive and work: one transaction

        SET @h = NULL;
        WAITFOR (
            RECEIVE TOP (1)
                @h = conversation_handle,
                @g = conversation_group_id,
                @b = message_body,
                @t = message_type_name,
                @s = message_sequence_number
            FROM dbo.TargetQ
        ), TIMEOUT 500;                       -- (2) no unlocked folder for 500 ms: worker exits

        IF @h IS NULL BEGIN COMMIT; BREAK; END;

        IF @t = N'//lab/work'
        BEGIN
            SET @x     = CONVERT(xml, @b);
            SET @run   = @x.value('(/w/@run)[1]',   'varchar(80)');
            SET @id    = @x.value('(/w/@id)[1]',    'int');
            SET @close = @x.value('(/w/@close)[1]', 'bit');

            -- Artificial work for the lab only: task 1 takes 600 ms, all others 200 ms
            SET @delay   = CASE WHEN @id = 1 THEN '00:00:00.600' ELSE '00:00:00.200' END;
            SET @started = SYSUTCDATETIME();
            WAITFOR DELAY @delay;

            INSERT dbo.WorkLog                -- (3) the business effect
            VALUES (@run, @id, @@SPID, @g, @s, @started, SYSUTCDATETIME());

            IF @close = 1 END CONVERSATION @h;
        END
        ELSE IF @t = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            END CONVERSATION @h;
        ELSE IF @t = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
        BEGIN
            ROLLBACK;                         -- do not silently swallow the error message
            THROW 51000, 'Unexpected Broker error: inspect queue', 1;
        END;

        COMMIT;                               -- (4) message gone and effect present: together
    END;
END;

WAITFOR (RECEIVE …), TIMEOUT waits for a message from an unlocked folder. Without a WHERE clause, RECEIVE selects only groups that no other RECEIVE has locked and returns only messages from that one group. A locked folder therefore does not hold up any other worker (Microsoft: RECEIVE). The artificial WAITFOR DELAY stands in for work; it makes visible in the experiment who is busy at the same time, and it does not measure compute performance. The timestamps come from the instance itself, not from the client.

Experiment 1: Twelve Tasks, One Dialog, Four Workers

Expectation according to the model: One dialog is one folder. Four workers cannot hold it at the same time. So only one task should run at any moment, in the order 1 to 12.

Setup: The twelve tasks from above, then EXEC dbo.Work; in four sessions at the same time.

Observation: The log shows it directly:

SELECT item, spid, seq, started, finished
FROM dbo.WorkLog
WHERE run = 'one-dialog'
ORDER BY started;

Excerpt, SQL Server 2025 CU9, first of three repetitions; times relative to the first start. Complete in the raw data.

No task overlaps with another. All twelve ran one after another and, in this repetition, were even completed by the same session. The other three workers did not get a turn because there was only one folder. The same picture appeared in all three repetitions and on both versions.

Conclusion: More workers do not help a single dialog. That is not a defect but the price of the ordering guarantee. Anyone who sends all work through one long-lived dialog has built serial processing, no matter how many workers are running.

Experiment 2: Twelve Tasks, Twelve Dialogs

Expectation: Twelve dialogs result in twelve folders on the receiver side. Four workers should therefore process four tasks at the same time.

Change: BEGIN DIALOG moves into the loop, and each task carries close = 1 so that the receiver ends each dialog after its single task. Please do not close the dialog on the sender side right after the SEND. After END CONVERSATION, that side can no longer receive anything on the dialog, not even an error message from the receiver.

WHILE @i <= 12
BEGIN
    BEGIN DIALOG CONVERSATION @h
        FROM SERVICE [//lab/source]
        TO SERVICE N'//lab/target'
        ON CONTRACT [//lab/contract]
        WITH ENCRYPTION = OFF;

    SET @body = (SELECT 'many-dialogs' AS [@run], @i AS [@id],
                        CONVERT(bit, 1) AS [@close]
                 FOR XML PATH('w'), TYPE);
    SEND ON CONVERSATION @h MESSAGE TYPE [//lab/work] (@body);
    SET @i += 1;
END;

Observation:

Excerpt, SQL Server 2025 CU9, first repetition.

Four sessions work at the same time. Instead of after 2.9 seconds, all twelve tasks were done after 0.83 seconds. The deliberately slow task 1 finished seventh. The completion order was 2, 3, 4, 5, 6, 7, 1, 8, 9, 10, 11, 12.

The same queue with four producers, each sending three tasks at the same time on dialogs of their own, likewise resulted in four tasks running at the same time. Many senders and one receiver, so-called fan-in, therefore does not mean serial processing. All that matters is how many folders the work is spread across.

Conclusion: Independent tasks belong on independent dialogs. Service Broker then distributes them to free workers by itself, even when the tasks differ in length. Across dialogs, however, there is no ordering, neither at start nor at completion.

Experiment 3: Merging Folders Deliberately

Sometimes serialization is wanted, for example for all changes to the same order. Then their dialogs should go into one folder.

Expectation: If twelve dialogs sit in one group, they behave like Experiment 1: one task after another.

Change: Twelve dialogs as in Experiment 2, but before starting the workers we put them into an existing group on the receiver side with MOVE CONVERSATION:

DECLARE @group uniqueidentifier, @handle uniqueidentifier;

SELECT TOP (1) @group = conversation_group_id FROM dbo.TargetQ;

DECLARE dialogs CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT conversation_handle
    FROM dbo.TargetQ
    WHERE conversation_group_id <> @group;

OPEN dialogs;
FETCH NEXT FROM dialogs INTO @handle;
WHILE @@FETCH_STATUS = 0
BEGIN
    MOVE CONVERSATION @handle TO @group;
    FETCH NEXT FROM dialogs INTO @handle;
END;
CLOSE dialogs;
DEALLOCATE dialogs;

The target group must exist and belong to the same service; MOVE CONVERSATION does not accept a freshly generated GUID. Incidentally, a SELECT on a queue only reads and removes nothing. This is how you can check beforehand how many folders are waiting:

SELECT conversation_group_id, COUNT(*) AS messages
FROM dbo.TargetQ
GROUP BY conversation_group_id;

Observation: Twelve groups before, one afterward. Four workers started; one session completed all twelve tasks one after another. No overlap, in all repetitions on both versions.

Cross-check: Many readers of the documentation come across RELATED_CONVERSATION_GROUP and use it to put the dialogs into one group on the sender side:

DECLARE @g uniqueidentifier = NEWID();
-- in the loop:
BEGIN DIALOG CONVERSATION @h
    FROM SERVICE [//lab/source]
    TO SERVICE N'//lab/target'
    ON CONTRACT [//lab/contract]
    WITH RELATED_CONVERSATION_GROUP = @g, ENCRYPTION = OFF;

Result: At the receiver, twelve groups were waiting nonetheless, and four workers worked at the same time. Conversation Groups apply only on their own side of a dialog. The sender group helps, for example, to bring replies together at the sender. It transfers nothing to the receiver side.

Conclusion: The folder is formed where the reading happens. An application defines the assignment by business rules before competing workers access it, for example one dialog per order. MOVE CONVERSATION before the workers start is a lab shortcut, not an application pattern. Even a shared group of different dialogs ensures only mutual exclusion, not an overall business ordering.

All variants at a glance, each with four workers started except in the first row:

SQL Server 2022 CU27 and 2025 CU9, three repetitions each: 36 runs, 432 confirmed tasks. In every run exactly twelve results, no duplicates, queue empty afterward; the same value in every repetition.

In Practice: Making Better Use of the Same CPUs

The experiments so far show coordination with artificial waiting time. The practical question is a different one: A batch of many similar, mutually independent tasks runs serially and in doing so occupies roughly one core while the others are idle. Does it finish sooner with several workers?

For this we used real CPU work. Each task computes double SHA2-256 hashes over 8,192 rows, derived from its own task ID. That is roughly 250 ms of CPU, with MAXDOP 1 so that only the parallelism between tasks takes effect:

CREATE PROCEDURE dbo.TaskBody @task int, @passes int, @result bigint OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @i int = 0, @v bigint;
    SET @result = 0;
    WHILE @i < @passes
    BEGIN
        SELECT @v = SUM(CONVERT(bigint, CHECKSUM(HASHBYTES('SHA2_256', HASHBYTES('SHA2_256',
                    CONCAT(payload, CONVERT(varchar(12), @task), ':', CONVERT(varchar(12), @i)))))))
        FROM dbo.Seed
        OPTION (MAXDOP 1);
        SET @result += @v;
        SET @i += 1;
    END;
END;

Each of the 32 tasks sits on a dialog of its own. The core of the worker is the same as above: receive, compute, write the result, end the dialog, all in one transaction.

-- Excerpt from dbo.BatchWorker (queue branch of the loop);
-- start barrier, CPU measurement and direct comparison branch omitted
BEGIN TRAN;
    SET @h = NULL;
    WAITFOR (RECEIVE TOP (1) @h = conversation_handle, @type = message_type_name,
                             @body = message_body
             FROM dbo.TargetQ), TIMEOUT 100;
    IF @h IS NULL BEGIN COMMIT; BREAK; END;
    IF @type <> N'//lab/work'
        BEGIN ROLLBACK; THROW 51100, 'Unexpected message type in task queue', 1; END;

    SET @x    = CONVERT(xml, @body);
    SET @task = @x.value('(/w/@id)[1]', 'int');

    SET @start = SYSUTCDATETIME();
    EXEC dbo.TaskBody @task, @passes, @result OUTPUT;
    SET @finished = SYSUTCDATETIME();

    INSERT dbo.BatchResult VALUES (@run, @task, @result, @start, @finished, @@SPID);
    END CONVERSATION @h;
COMMIT;

Observation, median of three runs, the same four assigned CPUs in all variants:

SQL Server 2022 CU27, 32 tasks per batch, results identical to the direct reference in every run. Ranges in the method section.

Four workers halved the batch duration with the same four CPUs. The free capacity was used. Three things belong directly with this:

• The individual task does not get faster. Its processing time rose from 273 ms direct to 821 ms with eight workers. The batch finishes sooner because tasks overlap.
• The total computing work rises. Four workers needed 35% more CPU seconds, eight 48% more. Shorter does not mean cheaper here.
• The queue itself accelerates nothing. One worker with a queue was slightly slower than the direct call. The gain comes solely from the parallelism.

The two-worker case stands out: hardly any gain. In two of three runs, both sat on the same SQL scheduler, one computing while the other waited for CPU, while other schedulers were idle. The third run was distributed better and finished after 6.0 s. Free CPU on average does not guarantee that every worker happens to land on a free scheduler.

Cross-check with a shared bottleneck: The same tasks, but each increments a shared counter before computing, in the same transaction:

UPDATE dbo.HotCounter SET n = n + 1 WHERE id = 1;   -- X lock until COMMIT
EXEC dbo.TaskBody @task, @passes, @result OUTPUT;

Now the batch took about 9.5 seconds with one, four, or eight workers alike. The additional workers sat in LCK_M_X waiting for the counter row. Logically independent tasks need an implementation that actually allows this independence.

Conclusion for practice: Several workers pay off when enough independent tasks are waiting, CPU is actually free, and no shared bottleneck lines the work up again. The best number of workers is a matter of measurement. Compare batch duration, complete results, CPU per completed task, and wait states. Under mixed workloads, include the response times of the other applications. Free CPU can also be the headroom for peak load. And if an individual query already runs in parallel, query parallelism and task parallelism share the same cores.

Activation: SQL Server Starts the Workers Itself

Instead of starting workers from outside, the queue can activate a procedure itself:

ALTER QUEUE dbo.TargetQ
WITH ACTIVATION (
    STATUS            = ON,
    PROCEDURE_NAME    = dbo.Work,
    MAX_QUEUE_READERS = 4,
    EXECUTE AS OWNER          -- lab only; for applications, choose the identity deliberately
);

With 24 independent dialogs, both versions reached two concurrently working workers although four were allowed. MAX_QUEUE_READERS is an upper limit for activated instances. The queue monitor checks every few seconds, and on events such as new messages, whether the running readers are falling behind, and only then starts another instance. That fits the model: If all work sits in a single dialog, it does not start another reader once a second one is already waiting. Readers that an application starts itself are independent of this limit.

Two rules for the procedure: It must exit when nothing is pending. In dbo.Work, WAITFOR (RECEIVE …), TIMEOUT 500 takes care of that. And STATUS = OFF only prevents new activations; instances that are already running keep working until they end on their own. In the lab, we therefore waited after switching off until sys.dm_broker_activated_tasks was empty.

Microsoft: When activation occurs, ALTER QUEUE

The "Asynchronous Trigger": Hand Off Synchronously, Process Later

A trigger always runs in the transaction of the triggering statement. Service Broker changes nothing about that. The pattern is that the trigger does not perform the expensive work itself but only hands it off:

CREATE TRIGGER dbo.OrdersAsync ON dbo.Orders AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- The entire inserted set, not just one row
    DECLARE @h uniqueidentifier,
            @body xml = (SELECT id, note FROM inserted
                         FOR XML PATH('order'), ROOT('orders'));

    BEGIN DIALOG CONVERSATION @h
        FROM SERVICE [//lab/source]
        TO SERVICE N'//lab/trigger'
        ON CONTRACT [//lab/contract]
        WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @h MESSAGE TYPE [//lab/work] (@body);
END;

A trigger fires once per statement, not per row. One INSERT can bring three or thirty thousand rows. The trigger therefore packs the entire inserted set into one message. Anyone who writes SELECT @id = id FROM inserted loses all rows but one, even with perfect delivery. For large sets, keys or a batch reference belong in the message rather than the rows.

The worker on the other side:

CREATE PROCEDURE dbo.TriggerWork
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @h uniqueidentifier, @b varbinary(max), @t nvarchar(256), @x xml;

    BEGIN TRAN;
    WAITFOR (RECEIVE TOP (1) @h = conversation_handle, @b = message_body,
                             @t = message_type_name
             FROM dbo.TriggerQ), TIMEOUT 500;
    IF @h IS NULL BEGIN COMMIT; RETURN; END;

    IF @t = N'//lab/work'
    BEGIN
        SET @x = CONVERT(xml, @b);
        WAITFOR DELAY '00:00:01';             -- artificial expensive follow-up work (lab)
        INSERT dbo.OrderEffects
        SELECT n.value('(id/text())[1]', 'int'), SYSUTCDATETIME()
        FROM @x.nodes('/orders/order') AS a(n);
        END CONVERSATION @h;
    END
    ELSE IF @t = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
        END CONVERSATION @h;
    ELSE
    BEGIN
        ROLLBACK;
        THROW 51002, 'Unexpected TriggerQ message', 1;
    END;
    COMMIT;
END;

What we tested with it:

The error message of the failed SEND, verbatim from the lab (only the container name is shortened):

Msg 8431, Level 16, State 1, Server …, Procedure OrdersAsync, Line 1
The message type '//lab/invalid' is not part of the service contract.
The statement has been terminated.

Conclusion: The originating transaction still pays for the trigger, the message construction, and the SEND. An error in them makes the original statement fail. In return, it gets a valuable contract: If the order is committed, the task is retained until a worker has processed it successfully or it is explicitly handled as an error. The work itself does not become cheaper. It runs later and then competes for the same resources. For the user interface this means: "task accepted" and "document ready" are two different states. Anyone who reports "done" after the SEND has built an application error.

Microsoft: Typical uses, Triggers with multiple rows

When Something Goes Wrong

The real test is not the happy path but the interruption between receive and commit.

Rollback: The Message Comes Back

BEGIN TRANSACTION;
    DECLARE @handle uniqueidentifier;
    RECEIVE TOP (1) @handle = conversation_handle FROM dbo.TargetQ;
    INSERT dbo.ProbeEffects (id) VALUES (1);
ROLLBACK;

SELECT COUNT(*) AS messages FROM dbo.TargetQ;        -- 1: back again
SELECT COUNT(*) AS effects  FROM dbo.ProbeEffects;   -- 0: nothing left

The message was back in the queue, and the half-finished effect was gone. The next call of dbo.Work completed the task exactly once. A worker may therefore run through its code repeatedly. What counts is what is committed together.

Hard Instance Crash

We received a message, wrote the effect in the open transaction, and then killed our own test instance hard (SIGKILL on the container). After the restart and recovery: message receivable again, effect rolled back, reprocessing exactly once. This is ordinary transaction recovery applied to the queue.

The Shortcut That Loses Work

-- ANTI-PATTERN, demonstration only
DECLARE @h uniqueidentifier;
BEGIN TRAN;
    RECEIVE TOP (1) @h = conversation_handle FROM dbo.TargetQ;
    END CONVERSATION @h;
COMMIT;                -- message is gone ...
-- ... and the actual work never happens

Result: queue empty, work missing. The same happens when a worker commits immediately after RECEIVE and only then does its work. Service Broker cannot repair an application error that has already been committed. An empty queue does not mean the work is done.

The Message Everyone Fails On

A task is syntactically valid but cannot be processed in business terms. The worker fails, rolls back, and receives the same message again. By default, Service Broker protects against this repeating endlessly:

-- TEST INSTANCE ONLY: deliberately disables the queue
BEGIN TRAN;
    DECLARE @h uniqueidentifier;
    RECEIVE TOP (1) @h = conversation_handle FROM dbo.TargetQ;
ROLLBACK;
-- five times in a row, after each attempt:
SELECT is_receive_enabled FROM sys.service_queues WHERE name = 'TargetQ';

Identical on SQL Server 2022 and 2025.

After the fifth consecutive rollback, the queue is disabled for RECEIVE. That also leaves every healthy task behind it stuck. A reflexive ALTER QUEUE dbo.TargetQ WITH STATUS = ON does not remove the cause. A robust worker catches business errors itself, limits the retries, stores the task with error data in a table of its own, and commits the receive. Or it ends the dialog with an explicit error response to the sender. We did not build such an error store in the lab.

The documentation names two tools for this, neither tested in the lab: With POISON_MESSAGE_HANDLING (STATUS = OFF) on CREATE QUEUE or ALTER QUEUE, the queue stays open after five rollbacks; your own error handling must then reliably prevent endless loops. And an event notification FOR BROKER_QUEUE_DISABLED can report the shutdown instead of letting you notice it only through the backlog.

Microsoft: Poison Messages, CREATE QUEUE: POISON_MESSAGE_HANDLING, CREATE EVENT NOTIFICATION, Transactional messaging

Ending Dialogs Properly

EndDialog and Error messages belong in every piece of receiving code, on both sides; our sender cleans up its queue with a small procedure dbo.DrainSource. An END CONVERSATION directly after each SEND is no substitute for a closing protocol. Service Broker never ends a dialog by itself; both sides must execute END CONVERSATION. According to the documentation, END CONVERSATION … WITH CLEANUP is a tool for administrators when a dialog can no longer end normally, and expressly does not belong in application code. After our runs, sys.conversation_endpoints contained no open dialogs, only endpoints in the state CLOSED. Many old open dialogs, on the other hand, are a finding.

Microsoft: END CONVERSATION, Dialog lifetime

Distribute Work or Notify Several Recipients?

"Fan-out" means two different things.

Distributing work: Twelve tasks, four workers, and every task is to be completed exactly once. These are competing readers on one queue, as in Experiment 2.

Notifying several recipients: An order interests the warehouse and accounting, and both should receive a message of their own. Four readers on the same queue do not accomplish this; they share the messages. Each recipient needs its own service with its own queue and its own dialog:

-- Two explicitly addressed deliveries
BEGIN DIALOG CONVERSATION @h1 FROM SERVICE [//lab/source]
    TO SERVICE N'//lab/target'     ON CONTRACT [//lab/contract] WITH ENCRYPTION = OFF;
BEGIN DIALOG CONVERSATION @h2 FROM SERVICE [//lab/source]
    TO SERVICE N'//lab/subscriber' ON CONTRACT [//lab/contract] WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @h1 MESSAGE TYPE [//lab/work] (@body);
SEND ON CONVERSATION @h2 MESSAGE TYPE [//lab/work] (@body);

In the lab, one message then sat in each of TargetQ and SubscriberQ. Since SQL Server 2012, SEND can also serve several handles in one statement, SEND ON CONVERSATION (@h1, @h2) …; according to the documentation, it is then atomic. We did not test this form. Microsoft: SEND

In Operations: Where Is It Stuck?

Four layers cleanly separate most questions:

1. Hand-off: Is Broker enabled in the database? Is anything being sent and committed?
2. Delivery: Are messages stuck in sys.transmission_queue, and with what status?
3. Processing: Is the queue open for RECEIVE? Is activation running? How many folders are waiting?
4. Business completion: How old is the oldest open task? What has failed? Only a task status of your own in the application answers that.

-- 1. Broker enabled in this database?
SELECT name, is_broker_enabled, service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID();

-- 2. Stuck deliveries
SELECT to_service_name, transmission_status, COUNT(*) AS messages
FROM sys.transmission_queue
GROUP BY to_service_name, transmission_status;

-- 3a. Queue state and activation
SELECT name, is_receive_enabled, is_enqueue_enabled,
       is_activation_enabled, max_readers, activation_procedure
FROM sys.service_queues
WHERE is_ms_shipped = 0;

-- 3b. How many folders are waiting? (reads only, removes nothing)
SELECT COUNT(DISTINCT conversation_group_id) AS groups, COUNT(*) AS messages
FROM dbo.TargetQ;

-- 3c. Running activated workers
SELECT * FROM sys.dm_broker_activated_tasks WHERE database_id = DB_ID();

-- Dialogs by state: many old open dialogs are a finding
SELECT state_desc, COUNT(*) AS endpoints
FROM sys.conversation_endpoints
GROUP BY state_desc;

These queries do not replace a task status. They do, however, separate "nothing was handed off," "delivery is stuck," and "the worker is not working" from one another. Suitable permissions are required for the DMVs. Microsoft: Troubleshooting delivery problems

And in the Cloud?

Service Broker is a capability of the SQL Server engine that not every managed service offers. This belongs on the checklist before a migration. Documentation as of October 27, 2026:

Feature comparison SQL Database/Managed Instance, Broker differences in Managed Instance, Migration note, SQL Server on Azure VMs, AWS, Google

Where Broker is missing, the same commitment has to be built differently. Three approaches fit the scenario of independent tasks:

The most important difference: Azure Service Bus does not take part in the SQL transaction. A committed update and a send call that fails afterward leave a change without a task; the reverse is equally possible. The outbox pattern therefore writes the data change and the task together into the database. A separate process forwards the task after the commit. In doing so it may send twice, so the receiver must recognize repetitions by a task ID. And if external workers call the same procedures, the database CPU still does the computing. These are architectural suggestions, not implemented in the lab.

Service Bus: Transaction scope, Outbox principle, Receive modes, Duplicates and idempotency, Sessions, Storage Queues and Service Bus compared, Retry and idempotency

What Remains

Service Broker is worthwhile when the reliability of a SQL transaction should also apply to handing off background work. How the work behaves is decided by the design:

• One dialog per independent task distributes the work across free workers.
• One folder per business transaction serializes what belongs together and lets unrelated work run in parallel.
• RECEIVE, work, and commit in one transaction turn the delivery guarantee into a processing guarantee, as long as the effects stay in the database. For emails or HTTP calls, the target additionally needs a task ID by which it recognizes repetitions.
• More workers help exactly as far as free CPU and independent resources allow. A measurement shows how far that is.

Method and Limits

Setup: Dedicated containers on the PSG lab host, one after another; SQL Server 2022 CU27 (16.0.4295.3, Developer, Ubuntu 22.04) and SQL Server 2025 CU9 (17.0.5005.3, Enterprise Developer, Ubuntu 24.04). Four assigned vCPUs, 5 GiB container RAM, 3,072 MB max server memory, no outbound network. Shared host with other services, no exclusive-hardware measurement and no version comparison.

Coordination experiments (1 to 3, activation, trigger, failure cases): Per version, six variants with twelve tasks and three repetitions in varying order after a warm-up of their own, 36 runs in total and 432 confirmed tasks; activation additionally with 24 tasks per version. The work is an artificial WAITFOR of 200 ms, 600 ms for task 1. Concurrency is determined from the start and end timestamps of the instance, not from the number of open sessions. The trigger timing comparison is a single test per version and shows the shift, not a speedup factor. 158 run checks and 494 independent evaluation checks passed.

CPU batch: SQL Server 2022 CU27 only. 32 tasks per batch, about 250 ms CPU per task, serial plan (MaxDOPSetToOne), warm cache. Direct call and 1, 2, 4, 8 workers, each with and without a shared counter, three repetitions: 30 runs, 960 confirmed tasks. Batch duration from the opening of a shared start barrier to the end of all workers including commit. Container CPU from the cgroup, including background and measurement work; the workers' CPU time separately from the request counters. Ranges of the batch duration: direct 9.17–9.42 s, one worker 9.42–9.91 s, two 6.04–9.67 s, four 4.48–6.16 s, eight 3.86–4.95 s. The median values of different columns do not necessarily come from the same repetition. 153 run checks and 289 evaluation checks passed.

Not tested: Remote dialogs, routes, certificates and encryption, availability groups, Windows-specific behavior, production payload sizes and long queues, mixed load with other applications, custom retry and error store, external effects such as mail or HTTP, the multicast form of SEND, and all cloud alternatives. The lab worker is a small, verifiable consumer, not an operations framework.

The scripts shown correspond to the lab setup, are formatted for readability and, where noted in a comment, shortened. The results come from the automated run with the same objects. Raw intervals, session IDs, sequence numbers, and check logs are available for every run.

Sources: Microsoft: What Service Broker does · Conversation Groups · Group locks · RECEIVE · When activation occurs · Transactional messaging · Poison Messages · END CONVERSATION. Further evidence appears directly next to the statements; vendor documentation checked on October 27, 2026.

Test Your Model

As of October 27, 2026. Tested with SQL Server 2022 CU27 and SQL Server 2025 CU9.

Further reading