Robson Kades describes an Azure SQL Database outbox handling about 3 million events a day, with roughly 45 million rows at steady state. The implementation’s main lesson is not that a particular SQL rewrite or database tier guarantees that scale: it is that a growing outbox needs deliberate control of row width, backlog access, transaction boundaries, and measured query performance.
What the outbox protects—and what it does not
An outbox addresses the dual-write problem. If an application commits a business change to a database and then separately publishes an event, the database write can succeed while the send fails. The business state and event can instead be written in the same database transaction; a separate worker later reads the committed event, publishes it, and records its progress.
As an Amazon Associate I earn from qualifying purchases.
That arrangement makes the business change and the intent to publish atomic within the database. It does not make publication to an external broker part of that same transaction. Microsoft’s architecture guidance describes this database-plus-worker pattern and notes that event ordering must be handled when sequence matters—for example, when consumers need to process a Created event before an Updated event.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →This is a relational Azure SQL implementation. Microsoft’s separate Cosmos DB example uses transactional batches, a change feed, and Service Bus; it is a different implementation, not the mechanism Kades describes.
#1 Best Overall
What scale did this implementation report?
Kades’s article date line says “Sep 16” but does not state a year. Its figures are author-reported observations for one Azure SQL Database Business Critical workload, not an independently reproduced benchmark, a capacity guarantee, or general Azure SQL limits.
| Reported measure | What it describes |
|---|---|
| About 3 million events per day | The workload reported by Kades; the article’s date line does not give a year. |
| About 45 million rows at steady state | The retained outbox table in the reported workload; the article’s date line does not give a year. |
| About 1.3 KB per row; roughly 58 GB for 45 million rows | Kades’s approximate row-size and storage arithmetic for the described schema. |
| 41.5 GB of stated engine memory | The figure Kades cites for the described 8-vCore Business Critical configuration; it is not a universal or current service limit. |
| About four times payload size in lifecycle logging | Kades’s estimate for the insert, status change, and purge-delete lifecycle in this design. |
The table-size arithmetic matters because the full retained dataset need not fit in engine memory. Kades says buffer-pool capacity is lower than the configuration’s stated engine-memory figure, so repeated work against a large table can become a storage and access-path problem, not just a question of making one query syntactically shorter. The log estimate also makes row width consequential: payload bytes can affect both retained storage and the volume of writes the lifecycle generates.
How the worker claims and drains pending events
Claim small batches with concurrent workers in mind
The reported worker selects up to 100 pending events of one event type, claims them by changing their status, and publishes them after the claim transaction. The application’s Hibernate pessimistic-locking behavior is translated into SQL Server hints including UPDLOCK, ROWLOCK, and READPAST. In this design, the intent is to let concurrent worker instances take different available rows rather than wait behind rows another worker has locked. These hints do not guarantee freedom from contention or lock escalation in every workload; the actual plan and behavior under concurrency still matter.
Use keyset pagination and bound each work cycle
The worker uses keyset pagination rather than OFFSET. With offset-based paging, a query generally has to read and discard rows before the requested offset; as the backlog grows, that work can grow too. A keyset query can continue from the last seen ID and seek forward instead. Kades reports a cap of 20 rounds per cycle, with no more than 100 events per round. That bounds one scheduler run to at most 2,000 claimed events, so a large backlog cannot keep a scheduler thread and its connection occupied indefinitely.
Why publishing outside the transaction requires recovery
Publishing after the claim transaction commits keeps a slow broker or network call out of the database transaction, avoiding a transaction held open during external I/O. But it creates a failure window: the worker can commit the claim or status change and then fail before publication succeeds or before failure is recorded. The article calls out this boundary; it does not establish exactly-once delivery.
Design recovery around that boundary. The system needs a defined way to identify and retry work that did not complete, and consumers need to tolerate duplicate delivery through idempotent handling. Kades puts it plainly: “The consumer has to be idempotent anyway.” If event order matters, retries and concurrent workers also need to preserve or enforce the required sequence; the outbox table alone does not establish that ordering.
Why the apparently simpler UPDATE…OUTPUT rewrite lost
Kades replaced a SELECT-plus-updates claim operation with a single UPDATE ... FROM ... OUTPUT statement, expecting fewer steps to be faster. The estimated plan showed a cost of 0.06, but measurements from sys.dm_exec_query_stats in this workload showed a different result:
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11| Claim approach | Reported CPU per 100-row claim | Qualification |
|---|---|---|
UPDATE ... OUTPUT |
142.84 ms | Author-reported measurement from the described environment. |
| Original SELECT plus updates | About 2.9 ms | Author-reported measurement from the same case study environment. |
Kades attributes the rewrite’s behavior to Halloween protection materializing rows in an eager spool, and to concurrent READPAST behavior undermining the optimizer’s TOP row goal. Those are explanations for this observed plan and workload, not a universal verdict on UPDATE...OUTPUT. An estimated-plan cost is not a substitute for measuring the work the query actually does. Kades’s practical advice is to compare actual CPU and I/O—using tools such as sys.dm_exec_query_stats and SET STATISTICS IO—before adopting a hot-path rewrite.
How the filtered index became usable
The described hot-path index is a filtered nonclustered index on event type and ID, with aggregate ID and payload included, filtered to pending status. Kades reports that the application initially used a clustered scan rather than this index. Two details in the case study help explain why the intended access path was not selected:
- Connection SET options: Kades found plan-cache differences associated with connection settings. In this environment, setting
ARITHABORT ONaligned application behavior with the behavior observed in SQL Server Management Studio. The article notes that on modern compatibility levelsANSI_WARNINGS ONaffects the functional interpretation, whileARITHABORTstill participates in the plan-cache key. Check the application’s actual connection options and execution plan rather than treating this setting as a universal fix. - Parameterized status predicates: The optimizer may have difficulty proving that a parameterized predicate matches the filtered index’s pending-status condition. Kades recommends a literal status predicate in the relevant native queries for this particular index and query design.
These observations are specific to the reported query, index, and connection setup. A filtered index only helps when the optimizer can use it for the application’s actual query and execution context.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Row width, logging, and payload conversion
The article reports that the application sent payloads as VARCHAR even though the column was NVARCHAR, and considers changing the column to VARCHAR to reduce row and log bytes. That is a data-integrity decision as well as a storage optimization: converting Unicode text to a narrower code page can silently replace characters the target encoding cannot represent.
Before such a conversion, check the real stored values for lossy conversions and confirm that the application’s language and data requirements permit the narrower encoding. Kades reports zero lossy rows in the test environment, but that result does not establish safety for another dataset. Do not infer that payload savings alone justify the change.
Best Value
Azure SQL Database documentation describes data-I/O and transaction-log-rate governance, including LOG_RATE_GOVERNOR as a wait type. The applicable limits depend on service level and hardware series, so a log-rate figure for one configuration should not be treated as a universal Azure SQL cap.
Operational choices in the reported stack
Kades lists Java 25, Spring Boot 4.1, Hibernate 7, mssql-jdbc, and Azure SQL Database Business Critical. The implementation also uses virtual threads for its I/O-bound scheduler, explicit graceful-shutdown behavior, small connection-pool settings, JDBC batching, and keyset-based work. These are choices in this system, not prerequisites for the outbox pattern; another workload should validate its own concurrency, connection use, and shutdown behavior.
The worker also respects configured Service Bus message and batch-size ceilings. Those limits vary by tier and protocol and can change. Operators should verify the current limits for their actual Service Bus configuration rather than transplant a tier-specific value from an article with an unstated year.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Quick Recap
A practical way to evaluate an outbox hot path
- Define delivery semantics first. Decide how retries, duplicate events, and ordering are handled at the publisher and consumer boundaries.
- Measure backlog behavior. Observe query CPU and I/O as pending rows and retained rows grow; check whether the query seeks from a stable key or revisits an expanding prefix.
- Inspect the application’s real plan context. Check the selected index, parameterization, and connection SET options under the application connection, not only an interactive session.
- Measure candidate rewrites under representative concurrency. Compare actual CPU, I/O, and relevant waits; do not select a plan because its estimated cost looks smaller or its SQL is shorter.
- Include storage and logging in the design. Estimate retained row width and lifecycle writes for the schema and workload, and assess any encoding change for data loss before deploying it.
- Bound each worker run and test recovery. Set a finite batch and cycle limit, then exercise failures between claiming, publishing, and recording completion.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
