An Outbox for 3 Million Events a Day on Azure SQL: the Engineering Behind It

The Transactional Outbox pattern fits in a thirty-second diagram: write the event in the same transaction as the business data, and let a worker publish it later. Everyone draws that in the first meeting and goes home happy.

What nobody draws is the second half: that table becomes the hottest structure in your database. It takes an insert, an update and a delete on the same row, several times a second, forever. It's read by a scheduler that never sleeps. And it grows until the day the sum of all its rows no longer fits in the server's memory — and from then on, every worker cycle goes to disk.

This article is the record of performance work on a real outbox: ~3 million events per day, ~45 million rows in steady state, running on Azure SQL Database Business Critical. The goal wasn't just "make it fast": it was to do the same work using fewer resources — less IO, less CPU, less log, less memory. On Azure, resources are the invoice.

One warning up front: the most expensive lesson in this work was about method, not about SQL. It's in section 3, and it involves me rewriting the hot path and being 50× wrong.

1. The baseline design

The worker does three things, in an order that matters:

List<OutboxJpaEntity> claimed = transaction.execute(() -> {
    Window<OutboxJpaEntity> window = outboxRepo
        .findFirst100ByEventTypeAndStatusOrderByIdAsc(currentEvent, PENDING, keyset.get());
    // ... mark the batch as processed
    return window.getContent();
});

// publish OUTSIDE the transaction
publisher.publish(claimed);

Claim — grab up to 100 PENDING events of a given type, locking them so no other instance takes them. Flip — mark those events as processed. Publish — send to Service Bus, outside the transaction.

The claim uses the classic SQL Server combo, expressed in JPA:

@QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "-2")) // SKIP_LOCKED
@Lock(LockModeType.PESSIMISTIC_WRITE)
Window<OutboxJpaEntity> findFirst100ByEventTypeAndStatusOrderByIdAsc(
        OutboxEventType eventType, OutboxStatus status, ScrollPosition position);

Hibernate 7's dialect translates that into WITH (UPDLOCK, ROWLOCK, READPAST). UPDLOCK reserves the rows; ROWLOCK keeps granularity at the row level, away from escalation to page or table; READPAST makes a concurrent instance skip whatever is already locked instead of waiting on it. This is what lets you scale horizontally without a table lock, without a blocking SELECT FOR UPDATE, and without inventing a distributed lock in Redis. Two instances running the same worker naturally pick up disjoint batches.

Pagination is keyset, not offset:

final AtomicReference<KeysetScrollPosition> keyset = new AtomicReference<>(ScrollPosition.keyset());
// ...
if (window.hasNext()) {
    keyset.set((KeysetScrollPosition) window.positionAt(window.size() - 1));
}

With OFFSET 500000, the database reads and throws away half a million rows before handing you the page. With keyset, it seeks on id > last_id and reads exactly 100. The difference isn't stylistic: it's the difference between constant cost and cost that grows with backlog depth. In a queue that occasionally piles up millions of rows, offset is a time bomb.

And there's a cap on rounds per cycle:

} while (isRunning() && maybeMore && --rounds > 0);

MAX_ROUNDS = 20 — at most 2,000 events per cycle. Without it, a large backlog would make the scheduler thread drain the entire queue in a single cycle, holding a pool connection and ignoring the shutdown signal for minutes.

Publishing outside the transaction: the honest decision

Publishing inside the transaction is the classic mistake: your database transaction now lasts as long as a network round-trip to the broker, holding locks the whole time. Under load, that's what takes the system down.

Publishing outside means accepting at-least-once: if the application dies between the claim commit and the publish, those events were marked processed and never went out. It's a small, known window, and the decision was documented rather than hidden:

// Publish OUTSIDE the transaction: on failure, mark the batch as FAIL for
// later reprocessing (at-least-once, except in the crash window between
// the TX1 commit and markFailed).

The alternative — two-phase commit between database and broker — costs more than the problem it solves. The consumer has to be idempotent anyway.

2. Before any code: understand the physics of the table

No query optimization saves a design that doesn't fit in memory. So the first thing measured wasn't time — it was size.

• payload was NVARCHAR(2000), stored in-row.
• With a ~600-character payload, the row landed around 1.3 KB.
• 45 million rows × 1.3 KB ≈ 58 GB.
• Engine memory on an 8-vCore Business Critical (BC_Gen5_8) is 41.5 GB — and the buffer pool is smaller than that: plan cache, lock memory and the 6.28 GB carved out for In-Memory OLTP come out of the same budget.

The table did not fit in memory. That number alone explains why the worker got slower over time regardless of indexing: past a certain point, reading the queue means reading disk.

Second calculation, on transaction log: over its lifetime, each event produces an insert + the flip update + the purge delete. Log volume lands around 4× the payload per event. On Azure that isn't a curiosity — there's log rate governance, a per-service-objective ceiling on log MB/s. Blow past it and you get LOG_RATE_GOVERNOR waits, and the whole application slows down with CPU to spare.

Two conclusions from that arithmetic, and they drove everything afterwards:

1. Reducing bytes per row is a compounding win: less buffer pool, less IO, less log, less backup.
2. Reducing rows touched per cycle beats any amount of plan fine-tuning — and that is, first and foremost, an indexing problem.

3. The most expensive lesson: estimated plans lie

Here's the part that hurts.

The original claim — a SELECT with UPDLOCK/READPAST followed by Hibernate's updates — struck me as naive. "Two statements where one would do." I rewrote it into the pattern every SQL Server outbox article recommends: a single UPDATE ... FROM ... OUTPUT that locks, marks and returns the rows in one shot.

The estimated plan for my version showed a cost of 0.06. It looked obvious.

Then I measured for real, with sys.dm_exec_query_stats:

The original is ~50× cheaper in actual CPU. The optimizer estimated mine at 0.06 and it cost 142 ms — off by roughly 2000×.

Why? Two things stacked up:

• Halloween Protection. The statement reads status = 0 and writes status = 1 in the same index. SQL Server must guarantee an updated row isn't re-read and updated again — and its protection is an Eager Spool: it materializes the entire set into tempdb before applying a single update. A plain SELECT followed by primary-key updates has no such problem; nothing is being read and written at once.
• Concurrent READPAST destroyed the row goal. The optimizer plans TOP 100 assuming it will find 100 rows quickly. With other instances holding rows, READPAST skips large stretches and the operator scans far more than estimated.

The "naive" version already had a clean covered read against the index, batched updates by PK, and — thanks to @DynamicUpdate — an UPDATE touching only the two columns that actually changed.

The rule that stuck: on this path, comparing estimated plans is comparing fiction. Either you have dm_exec_query_stats / STATISTICS IO for both versions, or you don't know which one is faster. I reverted the claim to the original and it has stayed untouched since.

4. The gotchas worth more than any refactor

Before touching architecture, four configuration details paid off more than any rewrite. All of them invisible in the Java code.

4.1 Your driver turns off your filtered index

The hot path index is filtered:

CREATE NONCLUSTERED INDEX IX_outbox_claim
    ON outbox_event (event_type, id)
    INCLUDE (aggregate_id, payload)
    WHERE status = 0;

It's tiny by construction — it holds only the PENDING backlog, which gets published within seconds. It's the perfect index for the claim.

And it simply wasn't being used. The claim turned into a Clustered Index Scan over millions of rows.

The entire fix was one line of YAML:

hikari:
  connection-init-sql: SET ARITHABORT ON

And here precision matters, because the easy explanation — the one I reached for first — is wrong.

The easy explanation goes: "mssql-jdbc connects with ARITHABORT OFF, and with ARITHABORT OFF the optimizer refuses filtered indexes." The CREATE INDEX docs do list ARITHABORT ON among the SET options required for a filtered index, and state that with the wrong options "the query optimizer doesn't consider the index in the execution plan for any Transact-SQL statements."

Except the SET ARITHABORT docs say something that dismantles that: ANSI_WARNINGS ON implicitly sets ARITHABORT ON at database compatibility level 90 or higher — and the JDBC driver connects with ANSI_DEFAULTS ON, which includes ANSI_WARNINGS ON. In the docs' own words: "When ANSI_WARNINGS is ON (the default), the setting of ARITHABORT has no functional effect." On a modern database, then, the filtered index was probably not being refused by that rule.

What the docs do state unambiguously is this, and it's enough to explain the symptom:

"The default ARITHABORT setting for SQL Server Management Studio (SSMS) is ON, while a client connection in an application defaults to ARITHABORT OFF. Even if there's no functional difference as long as ANSI_WARNINGS is ON, the ARITHABORT setting is still a cache key. Therefore, SSMS and an application both using their respective defaults, have different cache entries, and might get different query plans (...) the same query might execute slower in the application than in SSMS."

Which is exactly the symptom observed: seek in SSMS, scan in the application, same query. Two plan cache entries, two compilations, two fates — and the application's drew the bad one.

The practical lesson survives intact, and it's still the cheapest one here: match your application's ARITHABORT to SSMS's, or you're debugging a plan that isn't the one running in production. Just don't pin it on the wrong mechanism — as I did.

To find out which case is yours, the evidence is in the plan cache:

-- compare the plans of BOTH entries for the same query
SELECT ph.query_plan, cp.plan_handle, cp.usecounts,
       ph.query_plan.value('(//StmtSimple/@StatementOptmLevel)[1]', 'varchar(50)') AS optm
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) ph
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE '%outbox_event%';

Two entries for the same query text means it's the cache key. A single entry that still ignores the filtered index means it really is the SET options rule — and then it's worth checking the database's compatibility level.

4.2 A filtered index won't match a parameter

The second trap on the same index: for the optimizer to prove the WHERE status = 0 filter covers the query, it needs to see the literal 0 in the predicate. When Hibernate parameterizes it (status = @P2), the plan would have to be valid for any value of @P2 — including 1 and 2, which aren't in the index. Result: the filtered index is discarded.

In other words: any query that needs this index has to carry the literal, which in practice means a native query — not a Spring Data derived method. Wherever that shows up, the comment travels with it:

-- status = 0 is a LITERAL on purpose: parameterized, the optimizer won't match
-- the filtered index WHERE status = 0 (same trap as the claim).
IF EXISTS (SELECT 1 FROM dbo.outbox_event WITH (READPAST)
           WHERE event_type = @event_type AND status = 0)

The same goes for the flips: they use literals (1 for PROCESSED, 2 for FAIL) in native queries, with the enum ordinal documented right next to them.

4.3 The NVARCHAR that was never Unicode

The connection string already carried:

sendStringParametersAsUnicode=false

That's well-established good practice on SQL Server (without it, an NVARCHAR parameter compared against a VARCHAR column causes an implicit conversion and kills the seek). But it implies something: the driver was already sending payloads as VARCHAR. The Unicode promised by the NVARCHAR(2000) column never made it through from the application side — we were paying 2 bytes per character for a guarantee that didn't exist.

Migrating to VARCHAR(2000) cuts roughly half the row bytes and half the log bytes. Given log ≈ 4× payload, that's a direct win against log rate governance.

With one non-negotiable caveat: NVARCHAR → VARCHAR can corrupt data silently (characters outside the collation's code page become ?). So the script carries a mandatory pre-check before the ALTER:

SELECT MAX(LEN(payload)) AS max_len,
       SUM(CASE WHEN payload <> CONVERT(NVARCHAR(2000), CONVERT(VARCHAR(2000), payload))
                THEN 1 ELSE 0 END) AS lossy_rows
FROM dbo.outbox_event;

It must return lossy_rows = 0. In the test environment (16.5M rows): max_len = 28, lossy_rows = 0. Only then the ALTER — which is size-of-data, rewrites every row, and requires dropping and recreating any index containing the column.

4.4 Time zone in the Instant bind

An Instant mapped to datetime2 is bound in the JVM's time zone, not UTC. Which means the same code writes different values depending on which container it runs in — and a purge comparing processed_at < @cutoff starts deleting the wrong window.

"[hibernate.jdbc.time_zone]": UTC

One line that trades an environmental dependency for a guarantee.

5. Indexes and page geometry

With the driver fixed, the index started being used. At which point the subject becomes how pages behave under writes.

FILLFACTOR: 85 was waste

The original maintenance script rebuilt indexes with FILLFACTOR = 85 — leaving 15% of every page empty to absorb row growth and avoid page splits.

Except here, the row doesn't grow. RCSI and ADR are always on in Azure SQL Database — RCSI is the default on every new database, and ADR can't even be turned off. Row versioning adds a 14-byte version tag per row (a 6-byte transaction sequence number + an 8-byte row identifier), and the documentation is specific about when: on a database that already had RCSI enabled, those 14 bytes go in at insert time, not on first modification.

Which means: on Azure, the row is born with its version tag. The flip doesn't grow it by a single byte. That 15% of free space was reserved for growth that never happens.

15% of empty space across 45 million rows ≈ 9 GB of nothing occupying buffer pool, being read in every scan, copied in every checkpoint and every backup. FILLFACTOR = 98 is plenty:

ALTER INDEX ... REBUILD WITH (FILLFACTOR = 98, OPTIMIZE_FOR_SEQUENTIAL_KEY = ON, ONLINE = ON);

OPTIMIZE_FOR_SEQUENTIAL_KEY

The key comes from an ascending sequence, so every insert lands on the same page — the last one. Under burst, dozens of threads contend for that page's latch and form a PAGELATCH_EX convoy: the bottleneck isn't IO or CPU, it's a queue waiting on an in-memory structure. OPTIMIZE_FOR_SEQUENTIAL_KEY = ON exists precisely for this — it orders entry into that latch instead of letting the convoy form.

Why the wide covering index was expensive on the flip

There was a covering index with status as the leading column:

CREATE NONCLUSTERED INDEX IX_Outbox_Status_Event_Type_Covering
    ON dbo.outbox_event (status, event_type, id)
    INCLUDE (aggregate_id, aggregate_type, payload, created_at, processed_at, retry_count)

Great for reading. But when status is a key column and the flip changes status from 0 to 1, the index entry changes position — and SQL Server implements that as a delete + insert of the whole entry. The whole entry here includes the payload. So: every published event rewrote ~1.3 KB of index, on top of the base table row.

The filtered index serves the same access without that cost: because status = 0 lives in the WHERE and not in the key, the flip removes the entry instead of relocating it — and the index stays small, holding only the backlog.

Sequence IDs, not IDENTITY

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "outbox_event_seq")
@SequenceGenerator(name = "outbox_event_seq", sequenceName = "outbox_event_seq", allocationSize = 50)

CREATE SEQUENCE outbox_event_seq AS BIGINT START WITH 10000001 INCREMENT BY 50 CACHE 100;

With IDENTITY, Hibernate cannot batch inserts — it needs the generated id back for every row, which forces a round-trip per insert. With a sequence, allocationSize = 50 and the pooled-lo optimizer, the application reserves 50 ids at once and inserts 50 rows in a single JDBC batch.

The detail that breaks in production if you get it wrong: INCREMENT BY in the database must match allocationSize in Java. Diverge, and they collide.

6. The Java side: concurrency and clean shutdown

The work here is almost entirely IO — the JVM spends its time waiting on the database. Measurement confirmed it: at 35–350 events/s, allocation sits in the hundreds of KB/s. GC tuning had no win to offer. So the Java effort went elsewhere: don't hold threads, and don't lose batches on shutdown.

Virtual threads in the scheduler. The worker blocks on IO the entire time — the canonical use case:

@Bean("fastScheduler")
public ThreadPoolTaskScheduler fastScheduler() {
    ThreadPoolTaskScheduler scheduler = new ThreadPoolTaskScheduler();
    scheduler.setVirtualThreads(true);
    // virtual threads are daemon: without this wait the JVM exits mid-batch
    scheduler.setAwaitTerminationSeconds(30);
    scheduler.setWaitForTasksToCompleteOnShutdown(true);
    // ...
}

The comment points at the real trap: virtual threads are daemon threads. Without awaitTermination, the JVM exits without waiting for them and the in-flight batch dies with the process — at the worst possible moment, between claim and publish.

SmartLifecycle to actually shut down:

// volatile: start() runs on the main thread, stop() on the
// SpringApplicationShutdownHook, and the executeJob() loop reads the flag
// on the fastScheduler threads.
private volatile boolean isRunning = false;

Three different threads touch that flag, and it's volatile that guarantees the loop actually sees the stop signal. The loop checks it on every round (while (isRunning() && ...)), so a shutdown interrupts between batches — never in the middle of one.

Small pool, short timeout:

maximum-pool-size: 20
connection-timeout: 250
minimum-idle: 0

A large pool is a popular trap: more connections mean more context for the database to manage and more internal contention, not more throughput. And connection-timeout: 250 is a deliberate fail-fast choice — if there's no connection within 250 ms the system is saturated, and queuing threads to wait just moves the queue inside the application.

Well-aligned batching:

"[hibernate.jdbc.batch_size]": 50
"[hibernate.jdbc.fetch_size]": 50
"[hibernate.id.optimizer.pooled.preferred]": pooled-lo
"[hibernate.connection.provider_disables_autocommit]": true
"[hibernate.order_updates]": true
open-in-view: false

provider_disables_autocommit: true stops Hibernate from opening the database transaction too early — the pooled connection already arrives with autocommit off, and without this flag Hibernate spends a redundant round-trip per transaction. open-in-view: false because keeping a persistence session open while the response renders is holding a connection for free.

The publisher respects the broker's limit. A small detail that prevents a production error: on the Service Bus Standard tier the ceiling is 256 KB, both per message and per batch — and it isn't configurable. (On Premium the batch goes to 1 MB, and a single message can reach 100 MB over AMQP; if you change tier, this constant changes.) The publisher packs up to that ceiling and fails fast, with a clear message, on any single event that can't fit:

if (size > MAX_BATCH_BYTES) {
    throw new IllegalArgumentException(
            "Message " + event.getId() + " exceeds the batch limit of " + MAX_BATCH_BYTES + " bytes");
}

7. How to measure (because without this, none of the above is true)

The tooling that backed every decision in this article:

Actual CPU per query — the source of truth, the one that proved I was 50× wrong:

SELECT TOP 100
    (qs.total_worker_time * 1.0 / qs.execution_count) / 1000.0 AS Avg_CPU_ms,
    qs.execution_count, qp.query_plan, st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE st.text LIKE '%outbox%'
ORDER BY qs.execution_count DESC;

Indexes nobody uses — every unused index is pure write cost on a table that does nothing but write:

SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) DESC;

Fragmentation and real size via sys.dm_db_index_physical_stats — that's where the 9 GB wasted by FILLFACTOR 85 came from.

Wait evidence — sys.dm_db_wait_stats, sys.dm_db_resource_stats and Query Store, all database-scoped on Azure SQL. That's where LOG_RATE_GOVERNOR and PAGELATCH_EX show up by name.

8. What stuck

In order of real impact — and note how little of it is "better code":

1. SET ARITHABORT ON in the pool init. One line of YAML: it aligns the application with SSMS and kills the parallel plan cache entry where the bad plan lived.
2. NVARCHAR → VARCHAR. Half the row and log bytes, for a Unicode guarantee the driver was no longer delivering. With a mandatory data-loss pre-check.
3. FILLFACTOR 98 + OPTIMIZE_FOR_SEQUENTIAL_KEY. ~9 GB of emptiness reclaimed and the last-page latch convoy tamed.
4. A filtered index, with the literal in the query. An index holding only the PENDING backlog, and queries written so the optimizer can actually use it.
5. Keyset instead of offset in the claim's pagination: constant cost instead of cost that grows with backlog depth.

And the lesson that outranks all of them: I rewrote the hot path before measuring, and I was 50× wrong. The estimated plan said 0.06; reality charged 142 ms. The "obvious" pattern the articles recommend — a single-statement UPDATE ... OUTPUT — was the worse of the two in this specific context, because of a Halloween Protection the diagram never shows.

Comparing estimated plans is comparing fiction. dm_exec_query_stats, STATISTICS IO, or we know nothing.

Stack: Java 25, Spring Boot 4.1, Hibernate 7, mssql-jdbc, Azure SQL Database Business Critical.