In partnership with

Prefer another language? Prefere receber em português? ¿Prefieres recibirlo en español? Choose here / Escolha aqui / Elige aquí

The request came with four pages of metrics to collect. Extended events, wait statistics, CPU profiles, memory dumps, OS counters. It came with no test scenario at all.

For the people who wrote it, that made sense. The plan was written for one real environment, where serial redo had been seen running faster than parallel redo. When you collect data on the real thing, you don't need scenarios. But I had no access to that environment.

So I asked myself: if I can't measure the real environment, what can I do? I can build my own, run many different workloads, and see if any of them makes serial redo win. That is what I did this week.

In short

  • In 80 tests on two four-node SQL Server labs in Azure, parallel redo was faster or equal almost everywhere. On random-key inserts it was 4.5 to 6.2 times faster.

  • Serial redo (trace flag 3459) won one workload: hot-tail inserts on Ultra Disk, 42–52 s against 61–72 s.

  • The disk changed the answer.

This post does three things. First, it explains serial and parallel redo in plain words, and how I tested them. Second, it shows the numbers from both rounds, and why one workload favored serial redo. Third, it lists what affects redo performance outside and inside SQL Server, with a runbook if you want to test serial redo yourself.

What is redo, and what is the difference between serial and parallel redo?

In an availability group, the primary replica sends its transaction log to each secondary replica. The secondary writes (hardens) that log to its own log file. Then it applies each log record to the data pages. That apply step is redo.

If log arrives faster than redo can apply it, a redo queue builds. A big redo queue means the secondary is behind. Readers on a readable secondary see old data. A failover also takes longer, because the new primary must finish redo first.

Think of a restaurant kitchen. Orders arrive on paper slips. Each slip is a log record.

  • Serial redo is one cook. He reads each slip and cooks it himself, one after the other.

  • Parallel redo is a head cook with helpers. He reads each slip and hands it to a helper. The helpers cook at the same time.

With many different dishes, the team wins easily. But the head cook still reads every slip. If each dish takes seconds and the slips keep coming, the helpers stand and wait. The head cook becomes the limit. Keep this picture in mind. It explains the main result of this lab.

Where this works. Parallel redo is the default in SQL Server 2016 and later. Each database uses up to half of the CPU cores for redo, and at most 16 threads. SQL Server 2022 added a parallel redo thread pool, shared by all databases on the instance. Trace flag 3459 disables parallel redo. It is documented, applies to SQL Server 2016 and later, and has global scope only. Azure SQL Managed Instance supports only a short list of global trace flags, and 3459 is not on it. Azure SQL Database doesn't support trace flags at all. On both services, Microsoft manages redo for you. My lab ran on a pre-release build, SQL Server vNext CTP 1.2 on Linux.

How I tested serial vs parallel redo: a technical specification first

Before I wrote any script, I wrote a technical specification. If you never wrote one: it is a document that says what to build, how to run it, and how to know it worked. Mine had:

  • the environment: nodes, availability groups, the test database;

  • each scenario, with exact parameters: number of sessions, duration, target log rate in MB/s;

  • what to collect in each run, and from which node;

  • the order of the runs and the quiet time between them;

  • the checks that must pass before each run (we called them gates);

  • when to stop.

The parameters are the important part. "Run a heavy insert workload" is not a specification. "40 sessions, each inserting 100,000 rows with INSERT…SELECT, serial redo, then 16 minutes of quiet time" is.

I wrote it in one day, with seven scenarios. Then I gave it to Claude Code, the AI coding tool I use. It built everything from the spec: the workloads, the collectors, the extended event sessions, and an orchestrator that runs the whole campaign with nobody watching. You can find the lab, with more details, in Runbook: Serial vs Parallel Redo in SQL Server on lab.dbatopro.com.

The lab had two environments. Both had four SQL Server on Linux VMs in Azure: two availability groups joined by a distributed availability group. I measured redo on the last secondary of the chain.

Lab 1

Lab 2

Could it explain the serial win on hot-tail inserts?

Data disks

Standard HDD

Ultra Disk

No. Same disk for both modes in Lab 2. It made the win visible

Disk limits

500 IOPS, 60 MB/s

20,000 IOPS, 500 MB/s

No. Hot-tail pages were in memory: serial waited under 0.5 s on page reads

Location

Two regions (West US 2, West Central US)

One zone (East US 2)

No. Both modes got the log at the same speed

Primary VM

8 vCPUs

16 vCPUs

No. It only created the log: about 6.4 GB per test, in both modes

Measured secondary

8 vCPUs (4 cores), 4 redo workers

Same

No. Same in both labs

SQL Server

vNext CTP 1.2 on Linux (build 18.0.110.3)

Same build

No. Same in both labs

A fair question from any reader: Lab 2 had faster disks, a closer network and a bigger primary. Did that give serial redo an advantage? No. Here is why.

The comparison that matters is serial against parallel inside Lab 2. Both modes ran on the same VMs, the same disks, the same network and the same build. Each test had the same backlog of about 6.4 GB. Better resources help both modes the same way.

What Lab 2 changed is what the test could see. In Lab 1 the hot-tail backlog was only 1 to 2 GB, and redo kept up with the log as it arrived. So the two modes tied. A tie is not a parallel win. It only means the test could not tell them apart.

The network gives the clearest proof. In Lab 2, the log took about the same time to arrive in both modes: 19 to 29 seconds. After the last byte arrived, the network had no more work to do. Even then, serial redo applied 155 to 182 MB/s, and parallel redo only 116 to 131 MB/s.

The order of the tests can't explain it either. I ran them so that each mode had tests right after a restart and tests later on. Serial won in both positions.

The difference that matters is not between my two labs. It is between my lab and a large production server: my secondary had 8 vCPUs and 4 redo workers. I come back to that in "What this lab can't tell you".

Round 1 on Standard HDD: why serial redo never won

The seven scenarios from the spec ran first: bulk inserts, single-row inserts, one high-rate run, and repeats. Twelve runs in total.

In every run, in every mode, redo finished within 0 to 4 seconds after the workload ended. The log never arrived faster than about 27 MB/s. At that rate, both modes keep up. Redo was never the bottleneck, so the test could not tell the two modes apart. A tie tells you nothing about which one is faster.

I also ran a third mode in both rounds: parallel redo without the thread pool, using two trace flags that are not publicly documented. It behaved like the default parallel mode, so I don't show its numbers here.

So I asked the AI to suggest more scenarios. It came back with six, and with a better method. This is the part I like most about working with AI: it doesn't only do the work faster, it also makes me think of things I hadn't considered.

The method is called a backlog drain:

  1. Pause data movement on the last secondary.

  2. Run the workload on the primary. The log piles up.

  3. Resume data movement and time how long each mode takes to apply all of it.

Now redo is under pressure on purpose. The six new scenarios were:

Scenario

What it does

H, hot-tail inserts

Single-row inserts on an identity key, so every insert goes to the last page

I, random-key inserts

Single-row inserts on a GUID key, spread across the index

J, update churn

Many updates on a small table

K, allocation-heavy

Many new pages and extents

L, wide inserts

Large rows into 16 independent tables

M, bulk insert

The bulk workload again, under the new method

Drain time in seconds, two tests per mode:

Scenario

Serial (s)

Parallel (s)

Reading

H, hot-tail

167–188

143–195

Tie

I, random-key

273–279

45–60

Parallel, 4.5–6.2×

J, update churn

117–144

134–168

Tie

K, allocation

75–82

77–100

Tie

L, wide inserts

72–74

72–91

Tie

M, bulk

54.5–54.7

54.1–54.4

Tie

Again, no serial win. Random-key inserts were a clear parallel win: each insert touches a different page, and many of those pages must be read from disk first. That work spreads well across helpers. In the other five scenarios, the log arrived more slowly than either mode could apply it, so they tied.

Round 2 on Ultra Disk: the hot-tail inserts where serial redo won

Here I found the real problem with Round 1: the disks. The spec asked for 60 to 65 MB/s of log. Lab 1 never got close.

I measured how fast the primary could generate log on each disk type:

Data disk

Bulk insert

Single-row inserts

Log flush per commit

Standard HDD

25–27 MB/s

7 MB/s

not measured

Premium SSD P30

84–92 MB/s

13–15 MB/s

not measured

Premium SSD, P50 tier

95 MB/s

13–22 MB/s

2.4–3.0 ms

Ultra Disk

114–130 MB/s

46–66 MB/s*

0.9 ms

* Across 16 tables. Into one table, single-row inserts stopped at 22–26 MB/s on page latch contention.

Single-row inserts wait for the log flush on every commit. Ultra Disk cut that wait to about a third. So I built Lab 2 on Ultra Disk and ran everything again.

The first twelve runs tied again, now at 65 MB/s. The drain tests were different:

Scenario

Serial (s)

Parallel (s)

Reading

H, hot-tail

41.7–51.8 (6 tests)

60.8–71.5 (6 tests)

Serial wins

I, random-key

269–275

198

Parallel, about 1.4×

J, update churn

141–177

135–174

Tie

L, wide inserts

8.1–8.2

6.7–7.1

Parallel, about 15%

M, bulk

6.7–8.7

5.6–6.2

Parallel, slightly

(Scenario K finished within 4 seconds in every test, so it can't separate the modes.)

When serial won the first two hot-tail tests, I didn't call it a result. I called it a lead. I ran eight more, in an order that rules out the effect of a fresh restart. Serial won all six of its tests. Each test cleared about 6.4 GB of backlog.

Why was parallel redo slower on hot-tail inserts?

My first guess was wrong. I expected the helpers to block each other, because all the inserts land on the same page. The data said no: waits between workers were 8 seconds or less per test.

The real limit was the head cook. In parallel mode, one main redo thread reads the log and hands each record to a worker. On these tests:

Measure (6 tests each)

Serial

Parallel

Drain time

41.7–51.8 s

60.8–71.5 s

Main redo thread CPU

39.5–45.7 s

52.4–58.6 s

CPU per MB redone

6.1–7.0 ms

8.0–9.1 ms

Main thread busy

88–95% of a core

82–88% of a core

Busiest worker CPU

none

under 13 s

Workers waiting for work

none

240–285 s

RECOVERY_MANAGER latch waits

none

479,000–546,000

Each hot-tail insert is a small log record, and applying it is cheap: the page is already in memory. So most of the cost is reading the record and handing it over. In serial mode, one thread reads and applies, at 6.1 to 7.0 ms of CPU per MB. In parallel mode, the main thread spends 8.0 to 9.1 ms per MB only to read and hand over. That is about 30% more, and it matches the difference in drain time. The workers had little to do.

Random-key inserts are the opposite. There, applying a record often means reading a page from disk. The workers used 189 to 202 CPU seconds per test and waited 145 to 147 seconds on page reads. That is real work, and spreading it across workers wins.

What I can't say yet: which code path inside the main thread costs the extra CPU. The half a million RECOVERY_MANAGER latch waits, absent in serial mode, point at the hand-off. They don't prove it.

100+ Claude Code hacks to ship code 10X faster

Top engineers at Anthropic and OpenAI say AI now writes 100% of their code.

If you're not using AI, you're spending 40 hours doing what they do in 4.

These 100+ Claude Code hacks fix that and help you ship 10x faster.

Sign up for The Code and get:

  • 100+ Claude Code hacks used by top engineers — free

  • The Code newsletter — learn the latest AI tools, tips, and skills to code faster with AI in 5 minutes a day

What affects redo performance outside SQL Server?

  • Log disk latency on the primary. It sets how fast log is created. On Standard HDD the log never went past 27 MB/s, and redo had nothing to prove. The same workload on Ultra Disk created up to 66 MB/s.

  • Disk reads on the secondary. Random-key redo had to read pages before applying them. On Ultra Disk the parallel lead there was smaller: about 1.4×, against 4.5–6.2× on Standard HDD.

  • Network and distance. Lab 1 crossed two regions. Lab 2 sat in one zone, and after a pause the log arrived at 300 to 440 MB/s.

  • CPU cores on the secondary. They decide how many redo workers you get: up to half the cores, at most 16 per database. But if one main thread is the limit, more cores may not help. That is a guess I haven't tested, not a result.

  • A fresh restart. In Lab 1, parallel redo stalled in 4 of 13 tests, all among the first tests after a restart. Serial didn't stall. It is a pattern worth testing, not proof.

  • Things you don't control. A policy in the subscription I use for labs shuts the VMs down every night and turns Premium SSD disks back to Standard HDD. The first night it cost me more than five hours of tests. I also found that disks I had deployed as Premium were running as Standard HDD. That is the real reason Round 1 couldn't reach the target rate. Check that your disks are what you think they are.

What affects redo performance inside SQL Server?

  • Workload shape. Hot-tail inserts favored serial redo on fast disks. Random-key inserts favored parallel redo on every disk. The same server, the same flag, opposite answers.

  • Record size. For wide inserts (scenario L), the main thread spent only 3 to 4 ms of CPU per MB. Big records carry more work per hand-off, so parallel redo wins.

  • Page reads. If redo must read pages from disk, the work is heavy and parallel helps. If the pages are in memory, the hand-off cost dominates.

  • The redo mode. Parallel by default, serial with trace flag 3459. Neither is always faster.

  • Readers on the secondary. My lab had no readers. Microsoft Learn describes two more causes: read queries that block redo during DDL, and reports that compete with redo for disk.

How to test serial redo on a secondary: a runbook

⚠️ Before you start. Trace flag 3459 is global: it changes redo for every availability group database on that instance. Serial redo was 4.5 to 6 times slower on random-key inserts in this lab. A slower redo means a bigger redo queue and a longer failover. Test on one secondary, never on all of them, and first on a copy of production.

1. Capture the baseline. On the secondary, during your busiest hour, sample this every minute for at least one hour:

-- Run on the secondary replica
SELECT DB_NAME(drs.database_id) AS database_name,
       drs.redo_queue_size,      -- KB received but not yet redone
       drs.redo_rate,            -- average KB/s redone
       drs.log_send_queue_size,  -- KB not yet received
       drs.last_redone_time
FROM sys.dm_hadr_database_replica_states AS drs
WHERE drs.is_local = 1;

Also check which threads do redo for your database. One row with the command DB STARTUP means single-threaded redo:

SELECT r.session_id, r.command, r.wait_type, r.wait_time, r.cpu_time
FROM sys.dm_exec_requests AS r
WHERE r.command IN ('DB STARTUP', 'PARALLEL REDO TASK', 'PARALLEL REDO HELP TASK')
  AND r.database_id = DB_ID(N'YourDatabase');

Also check the log write latency on the primary, since the last restart. Around 1 ms, with mostly single-row inserts on an identity key, puts you in the hot-tail zone, where a serial test is worth your time:

-- Run on the primary replica
SELECT DB_NAME(vfs.database_id) AS database_name,
       vfs.num_of_writes,
       CAST(vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0)
            AS decimal(10, 2)) AS avg_log_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id
 AND mf.file_id = vfs.file_id
WHERE mf.type_desc = N'LOG';

Cost. These queries are light. Sampling once a minute is fine on a busy server. Don't loop them every second for hours, and save the results to a table on another server, not on the secondary you are measuring.

2. Apply it safely. In a low-traffic window, on one secondary:

  1. Turn it on: DBCC TRACEON (3459, -1);

  2. Verify it: DBCC TRACESTATUS (3459); must show Status 1 and Global 1. Then run the redo-threads query from step 1 while there is redo work. With serial redo, you should see only the DB STARTUP row for your database.

  3. If redo is still parallel, check the current trace flag documentation, and restart SQL Server on that secondary as needed. In my lab, every switch between modes was a restart with the flag set at startup.

  4. A trace flag turned on with DBCC TRACEON is lost at the next restart. To keep it, also set it at startup:

    • Linux: sudo /opt/mssql/bin/mssql-conf traceflag 3459 on (used at the next restart).

    • Windows: add -T3459 to the startup parameters in SQL Server Configuration Manager.

If you restart, that secondary is unavailable for a short time. The other replicas keep running. Then compare redo_queue_size and redo_rate with your baseline, at the same hour, for several days.

3. Roll it back if the redo queue grows past your RTO, or if failover tests take longer:

  1. Turn it off: DBCC TRACEOFF (3459, -1);

  2. Verify it: DBCC TRACESTATUS (3459); must show Status 0. The redo-threads query should show the parallel redo tasks again when there is redo work. If it doesn't, check the current documentation and restart SQL Server on that secondary as needed.

  3. If you set it at startup, remove it there too:

    • Linux: sudo /opt/mssql/bin/mssql-conf traceflag 3459 off

    • Windows: remove -T3459 from the startup parameters.

How long did it take, with AI doing most of the work?

People ask me how to deliver something this complex, fast, with quality. Here are the real numbers:

Measure

Value

From first message to last delivery

About 50 hours (Nov 9 to Nov 11)

Tests running with nobody watching

27.5 hours

Measured runs

80, in 13 scenarios

Azure VMs

8, in two labs

Claude's tool calls

693

My messages

37, about 900 words

My hands-on time

About 3 hours

My review of the results

About 2 hours

Most of the time I was not even looking. I defined what to do, I checked, and I stepped in when I had to. It didn't take an absurd amount of my time.

Not everything went well. The SQL port was blocked from my machine, so all the work ran on the nodes. Ultra Disk had no capacity in one region, and three deployments failed. The nightly shutdown cost five hours the first night. After the orchestrator learned to resume on its own, the second night cost 15 minutes. And the AI's first explanation for the serial win was wrong. The data corrected it.

That is why the last point matters most: I know everything that was done, because I reviewed everything. I read every report. I saw the evidence files, all of them. I read the data collectors it built. And now I can reuse those collectors in my next lab.

What this lab can't tell you

  • The measured secondary had 8 vCPUs (4 cores) and 4 redo workers, in both labs. I don't know how big the hot-tail effect is on a server with many more cores.

  • Lab 2 sat in one zone. Log transport across regions is slower.

  • Scenarios I to M had two tests per mode. Only H had six.

  • The Ultra Disk settings were my choice, not a tuned production setup.

  • The lab ran on a pre-release build, SQL Server vNext CTP 1.2 on Linux. A released version may behave differently.

Your next step: send me your lab idea

Is there a lab or a proof of concept you want to run, but you don't have the time or the environment? Leave a comment with the details: what you want to test, on which version, and what you want to measure.

I will tell you if it is possible. If it is, your idea may become the next post. And you can use everything from this lab, with the details in Runbook: Serial vs Parallel Redo in SQL Server on lab.dbatopro.com, to save your time or to unblock something you are stuck on.

References

Glossary

Term

What it means

Availability group

A group of databases that SQL Server copies from a primary replica to one or more secondary replicas.

Backlog drain

My test method: pause the secondary, build up log, resume, and time how long redo takes to apply it all.

Distributed availability group

Two availability groups joined together, usually in different places. The second one gets the log from the first.

Extended events

A light SQL Server tracing system that records chosen events to a file.

Hot-tail insert

An insert on an ever-growing key, such as an identity column. Every new row goes to the last page of the index.

Log flush

Writing the log records of a transaction to disk. A commit waits for it.

Main redo thread

In parallel redo, the thread that reads the log and hands records to the workers. In serial redo, the only thread.

Parallel redo

The default redo mode: one thread reads the log and several workers apply it.

Random-key insert

An insert on a key with random values, such as a GUID. New rows go to pages all over the index.

RECOVERY_MANAGER latch

An internal SQL Server lock used by redo. In this lab it appeared only in parallel mode.

Redo

Applying the received log records to the data pages on a secondary replica.

Redo queue

Log that the secondary has received but not yet applied.

Serial redo

Redo with one thread that reads and applies every record. Turned on with trace flag 3459.

Technical specification

A document that says what to build, with exact parameters, how to run it, and how to know it worked.

Trace flag

A switch that changes a specific SQL Server behavior. Use only documented ones, and test first.

Ultra Disk

Azure's fastest managed disk type, with very low latency and IOPS and throughput you set yourself.

How I write: every post starts from my own notes and experience. I use AI tools to help draft and edit, and I review every word before it goes out.

The technical details in this post are based on publicly available Microsoft documentation as of November 2026.

Disclaimer: The views expressed in this article are those of the author and do not necessarily reflect the official policy or position of Microsoft. The author is a Microsoft employee.