ADR for tempdb: Fast Rollback, Half the Throughput

The price is elsewhere. The same change took six to seven times as long. And eight sessions that continuously create and drop small temporary tables managed only between half and two fifths of the work with ADR for tempdb. Whether the option is worthwhile therefore depends on how your application uses tempdb.

In short

1. Turn it on: ALTER DATABASE tempdb SET ACCELERATED_DATABASE_RECOVERY = ON; It takes effect only after an instance restart. The default is off; SQL Server 2022 rejects the command.
2. Gain: The rollback of a large change to a #temp table finishes instantly, and the tempdb log can be truncated despite an open transaction.
3. Cost per change: Every changed row gets a version in tempdb's persistent version store (PVS). In the lab, the change itself took six to seven times as long.
4. Cost under load: Many short temp tables lose significant throughput, 48 to 63 percent in the lab. Microsoft names this limitation itself.
5. Two version stores: tempdb then keeps the PVS for its own transactions and, alongside it, the traditional version store for user databases without ADR. Both need space.
6. Not affected: Table variables (they aren't rolled back anyway) and optimized locking (it doesn't apply in tempdb).

What ADR promises in tempdb

In a user database, you know ADR from ADR: fast rollback and what it costs: row versions live in the persistent version store, a rollback only marks the transaction as aborted, and the log no longer depends on the longest open transaction. Up to SQL Server 2022, that applied only to user databases. tempdb continued to roll back the classic way, row by row, and an open transaction on a temporary table held the tempdb log in place.

Microsoft describes exactly two benefits of ADR in tempdb: instant rollback and aggressive log truncation for all transactions in tempdb, that is, temporary tables, table variables and regular tables created there. One note matters, too: plan additional space for the PVS in the tempdb files.

Enabling and checking

-- Turn on ADR for tempdb (SQL Server 2025)ALTER DATABASE tempdb SET ACCELERATED_DATABASE_RECOVERY = ON;-- takes effect only after an instance restart -- check after the restartSELECT name, is_accelerated_database_recovery_onFROM sys.databasesWHERE database_id = 2;

Four things differ from a user database:

• The restart is mandatory. Until then, nothing changes, neither the behavior nor the display. In the lab, the rollback after the ALTER and before the restart still took 2.5 seconds.
• sys.databases shows the effective state, not the requested one. If you have set ON and haven't restarted yet, it still shows 0. We found no place where a pending change would be visible. Turning it off works the same way: OFF, then restart.
• The error log confirms it at startup with the line [DbId:2] ADR enabled for the database. Other indicators don't work: the row for database_id = 2 in sys.dm_tran_persistent_version_store_stats exists even without ADR, with 0 KB.
• No exclusive lock needed. According to Microsoft, the WITH ROLLBACK IMMEDIATE clause should not be specified; SQL Server nonetheless accepted it in the lab without a message.

SQL Server 2022 rejects the same command; in the original: Msg 12122, Level 16, State 1 – Accelerated Database Recovery cannot be enabled/disabled on system databases.

What it gives you: rollback and log

In the lab, we changed a #temp table with 100,000 rows completely three times in an open transaction, then ran a CHECKPOINT and rolled back. Three runs per build:

That is the same order of magnitude as in a user database with ADR (there, 0.1 to 0.3 ms versus about 2.5 seconds). The gain is real, and it affects exactly the cases that hurt in tempdb: a large intermediate step in a #temp table that fails, or a transaction that stays open while the tempdb log grows. What lies behind this without ADR is described in Version Store and Transaction Log in SQL Server in the tempdb hub.

Two caveats belong here:

• The change becomes more expensive. Every changed row writes a version into tempdb's PVS. In the lab, the triple UPDATE took six to seven times as long, even longer than the same change in a user database with ADR (about 1.9 seconds). We did not investigate the exact cause.
• The PVS stays after the rollback. The 92 MB were released only by the cleaner, which runs about once a minute. For a short time, an aborted intermediate step therefore occupies space in tempdb.

Table variables don't benefit. Their rollback was instant in all runs, with and without ADR. The reason is old and has nothing to do with ADR: table variables are not rolled back. In all 12 runs, all 100,000 rows still held the changed value after the ROLLBACK.

Two version stores in tempdb

With ADR for tempdb, there are two separate stores for row versions:

User databases with ADR store their versions in their own PVS as before, not in tempdb.

In the lab, an open SNAPSHOT transaction in a user database without ADR held on to its versions for 60 seconds while a second session changed temporary tables. Both stores grew side by side: the traditional store to 11.9 MB, tempdb's PVS to 13.6 MB. Small changes ended up in the row itself as a delta (21 bytes per row), large ones as a separate entry in the PVS.

Two things from this matter for operations:

• A snapshot transaction in any database also holds on to tempdb's PVS. After it ended, the PVS dropped to 0.35 MB within three seconds in the lab. So if tempdb is growing, you don't look for the holder only in tempdb.
• The performance counter "Version Store Size (KB)" counts only the traditional store. Monitoring that relies on it misses tempdb's PVS entirely.

What it costs: many short temp tables

Microsoft names a known limitation for ADR in tempdb: if temporary tables are created and dropped very frequently, latch waits can occur on a tempdb system table; the page speaks of an ongoing investigation and a planned fix. We found no fix in the release notes for CU9 and CU10.

We re-created this: eight sessions that, for 60 seconds without pause, create a #temp table, insert 1,000 rows, change all of them and drop the table again. Once as a loop in a batch, once in a stored procedure, where SQL Server can cache the temporary table. Three runs per cell, four CPUs, four tempdb data files:

In both variants, the sessions spent about three quarters of their time waiting on page latches on tempdb system tables. Without ADR, sysschobjs was the hot spot, with ADR sysrscols; in addition came latch waits of the class PVS_ADD_RECORD, which don't occur at all without ADR. The table named by Microsoft, sys.sysseobjvalues, did not show up in any of our samples; whether both have the same cause, we cannot say. Allocation pages (PFS, GAM, SGAM) were never the bottleneck. tempdb's PVS grew to 110 to 541 MB under this load even though every transaction committed immediately.

The difference between CU9 and CU10 is not a reliable build difference with three runs per cell. The direction is: on both builds, the option cost about half of the throughput. Whether memory-optimized tempdb metadata mitigates this, we did not measure.

Optimized locking does not apply in tempdb

Optimized locking can't be turned on for tempdb (Msg 12122 … Optimized Locking cannot be enabled or disabled on system databases.), and Microsoft says so, too. In the lab, in a session whose database uses optimized locking, the locks looked like this:

Only your own session sees local temporary tables anyway; there is nothing there that would need optimized locking. For global temporary tables, everything stays as it was.

Overview: Is ADR on in tempdb, and who is holding the versions?

In three result sets, the query shows the effective state along with the space and log of tempdb, the two version stores side by side, and the sessions that hold on to versions or to the tempdb log.

Query: ADR for tempdb overview

/* PSG Stack · SQL Server Locking · Query 2   Overview: is ADR on in tempdb, how large are the two version stores in tempdb, and who is holding them?    !!! WARNING — please read before running !!!   - Read-only. The query changes no data and no setting and does not kill any session.   - Snapshot: version stores grow and shrink constantly. A small value only means "small at this     moment". Do not run it in a loop or every second.   - Permissions: VIEW SERVER STATE (SQL Server 2022 and later: VIEW SERVER PERFORMANCE STATE is enough).   - MAXDOP 1: every statement runs serially and uses no additional cores. This keeps the     diagnosis small even while tempdb is under pressure.   - Runs on SQL Server 2019 and later; ADR in tempdb exists only from SQL Server 2025.     Tested in the PSG lab on 2025 CU9 and CU10 and on 2022 CU27 (2026-11-08).   - ADR in tempdb is turned on with ALTER DATABASE tempdb SET ACCELERATED_DATABASE_RECOVERY = ON;     it takes effect only after the instance restarts. This query does not change anything.    Result sets:     1  State: switches, instance start, space and log of tempdb     2  The two version stores in tempdb: PVS (versions of tempdb's own transactions, only with        tempdb ADR) and the traditional version store (versions from user databases without ADR)     3  Who is holding versions or the tempdb log: oldest snapshot/RCSI transactions and oldest        open transactions in tempdb (at most 5 each)*/SET NOCOUNT ON; /* 1 · State   tempdb_adr shows the state in effect since the last start. ALTER DATABASE tempdb … without a   restart does not change the column yet (checked in the PSG lab, in both directions); at startup the   error log reports "[DbId:2] ADR enabled for the database." */SELECT CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(20)) AS version,       CASE d.is_accelerated_database_recovery_on WHEN 1 THEN N'on' ELSE N'off' END AS tempdb_adr,       si.sqlserver_start_time AS instance_start,       CASE CAST(SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS int) WHEN 1 THEN N'on' ELSE N'off' END AS memory_optimized_metadata,       f.data_mb, f.free_mb,       CAST(l.total_log_size_mb AS decimal(12,1)) AS log_mb,       CAST(l.active_log_size_mb AS decimal(12,1)) AS log_active_mb,       l.log_truncation_holdup_reason AS log_waiting_for,       CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS int) < 17                THEN N'ADR for tempdb exists only from SQL Server 2025.'            WHEN d.is_accelerated_database_recovery_on = 1                THEN N'tempdb ADR on: instant rollback in tempdb, plan space for PVS in tempdb (result set 2).'            ELSE N'tempdb ADR off (default).'       END AS noteFROM sys.databases AS dCROSS JOIN sys.dm_os_sys_info AS siCROSS APPLY sys.dm_db_log_stats(2) AS lCROSS APPLY (SELECT CAST(SUM(total_page_count) / 128.0 AS decimal(12,1)) AS data_mb,                    CAST(SUM(unallocated_extent_page_count) / 128.0 AS decimal(12,1)) AS free_mb             FROM tempdb.sys.dm_db_file_space_usage) AS fWHERE d.database_id = 2OPTION (MAXDOP 1); /* 2 · The two version stores in tempdb   PVS: persistent_version_store_size_kb counts only versions stored off-row; in-row versions        live in the pages of the temporary tables themselves.   traditional: version store pages (sys.dm_db_file_space_usage), split by source database        (sys.dm_tran_version_store_space_usage). User databases with ADR keep their PVS in the        database itself and do not appear here. */SELECT N'PVS (tempdb ADR)' AS version_store,       CAST(p.persistent_version_store_size_kb / 1024.0 AS decimal(12,1)) AS used_mb,       N'transactions in tempdb: #temp, table variables, tables in tempdb' AS applies_to,       p.current_aborted_transaction_count AS aborted_tx,       p.offrow_version_cleaner_end_time AS last_cleaner_run,       p.pvs_off_row_page_skipped_min_useful_xts AS pages_skipped_snapshot,       CAST(NULL AS nvarchar(400)) AS largest_sources,       CASE WHEN (SELECT is_accelerated_database_recovery_on FROM sys.databases WHERE database_id = 2) = 0                THEN N'tempdb ADR off: transactions in tempdb create no versions'            WHEN p.offrow_version_cleaner_start_time IS NOT NULL AND p.offrow_version_cleaner_end_time IS NULL                THEN N'cleaner is running'            ELSE N'' END AS noteFROM (SELECT 1 AS x) AS oneLEFT JOIN sys.dm_tran_persistent_version_store_stats AS p ON p.database_id = 2UNION ALLSELECT N'traditional (version store)',       CAST(v.pages / 128.0 AS decimal(12,1)),       N'user databases without ADR: RCSI, snapshot isolation, triggers, online index, MARS',       NULL, NULL, NULL,       (SELECT STRING_AGG(CAST(x.entry AS nvarchar(400)), N', ')        FROM (SELECT TOP (3) DB_NAME(database_id) + N' ' + CAST(CAST(reserved_space_kb / 1024.0 AS decimal(12,1)) AS nvarchar(20)) + N' MB' AS entry              FROM sys.dm_tran_version_store_space_usage              WHERE reserved_space_kb > 0              ORDER BY reserved_space_kb DESC) AS x),       N''FROM (SELECT SUM(version_store_reserved_page_count) AS pages FROM tempdb.sys.dm_db_file_space_usage) AS vOPTION (MAXDOP 1); /* 3 · Who is holding versions or the tempdb log (at most 5 each, oldest first)   Snapshot/RCSI transactions delay the cleanup of both version stores, across database   boundaries. Without tempdb ADR, open transactions in tempdb hold the tempdb log. */SELECT h.kind, h.session_id, h.seconds_open, h.database_name, h.login_name, h.program_name, h.tempdb_log_mbFROM (SELECT * FROM (SELECT TOP (5) N'snapshot/RCSI transaction' AS kind, a.session_id, a.elapsed_time_seconds AS seconds_open,             DB_NAME(s.database_id) AS database_name, s.login_name, s.program_name,             CAST(NULL AS decimal(12,1)) AS tempdb_log_mb      FROM sys.dm_tran_active_snapshot_database_transactions AS a      JOIN sys.dm_exec_sessions AS s ON s.session_id = a.session_id      WHERE a.session_id <> @@SPID      ORDER BY a.elapsed_time_seconds DESC) AS snap      UNION ALL      SELECT * FROM (SELECT TOP (5) N'open transaction in tempdb' AS kind, st.session_id,             DATEDIFF(SECOND, dt.database_transaction_begin_time, SYSDATETIME()) AS seconds_open,             DB_NAME(s.database_id) AS database_name, s.login_name, s.program_name,             CAST(dt.database_transaction_log_bytes_used / 1048576.0 AS decimal(12,1)) AS tempdb_log_mb      FROM sys.dm_tran_database_transactions AS dt      JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = dt.transaction_id      JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id      WHERE dt.database_id = 2 AND dt.database_transaction_begin_time IS NOT NULL AND st.session_id <> @@SPID      ORDER BY dt.database_transaction_begin_time) AS open_tx) AS hORDER BY h.seconds_open DESCOPTION (MAXDOP 1);

Sample output

Sample output from a re-created lab run (SQL Server 2025 CU10, synthetic data, names made up). ADR for tempdb is on. In the database Warehouse (RCSI and snapshot isolation, without ADR), session 52 has kept a SNAPSHOT transaction open for 31 seconds; another session has changed two temporary tables:

1 · State

version     tempdb_adr instance_start    memory_optimized_metadata data_mb free_mb log_mb log_active_mb log_waiting_for note
----------- ---------- ----------------- ------------------------- ------- ------- ------ ------------- --------------- -----------------------------------------------------------------------------------------
17.0.5035.5 on         2026-11-08 14:37  off                       2048.0  2025.4  1024.0 .1            CHECKPOINT      tempdb ADR on: instant rollback in tempdb, plan space for PVS in tempdb (result set 2).


2 · The two version stores in tempdb

version_store               used_mb applies_to                                                       aborted_tx last_cleaner_run pages_skipped_snapshot largest_sources    note
--------------------------- ------- ---------------------------------------------------------------- ---------- ---------------- ---------------------- ------------------ ----
PVS (tempdb ADR)            13.3    transactions in tempdb: #temp, table variables, tables in tempdb 0          2026-11-08 14:42 0                      NULL
traditional (version store) 11.6    user databases without ADR: RCSI, snapshot isolation, ...         NULL       NULL             NULL                   Warehouse 11.6 MB


3 · Who is holding versions or the tempdb log

kind                      session_id seconds_open database_name login_name    program_name  tempdb_log_mb
------------------------- ---------- ------------ ------------- ------------- ------------- -------------
snapshot/RCSI transaction 52         31           Warehouse     app_warehouse WarehouseApp  NULL

Both stores are in use at the same time: the PVS holds the versions of the temporary tables, the traditional version store those of the Warehouse database. The SNAPSHOT transaction holds on to both; in the lab, tempdb's PVS dropped from 13.3 to 0.35 MB only after it ended.

Permissions: VIEW SERVER STATE; from SQL Server 2022 on, VIEW SERVER PERFORMANCE STATE is enough. Without the server-level permission, the query fails with error 300. Tested on SQL Server 2025 CU9 and CU10 with ADR for tempdb on and off, and on 2022, where it reports that the option exists only from 2025.

When to turn it on, and when not to

In favor: an application that processes large intermediate results in temporary tables and whose failures or long transactions have already filled the tempdb log or cost minutes of rollback. Against: an application that creates and drops very many short temporary tables at high frequency, for example in frequently called procedures. There, the throughput loss in the lab was much larger than any gain in rollback.

Because the option needs a restart, you can't just try it out on the side. Measure beforehand on a test instance with your workload, plan space for the PVS in the tempdb files, and check the error log and the overview query after turning it on. Monitoring that observes only the "Version Store Size" performance counter needs a second metric.

Frequently asked questions

Do I need ADR in the user databases to turn it on for tempdb? No. The two options are independent. User databases with ADR store their versions in their own PVS; without ADR, they use tempdb's traditional version store.

How can I tell whether a restart is still pending? You can't, directly. sys.databases shows the effective state since the last start. Record the change in your change log and check the error log and sys.databases after the restart.

Does ADR for tempdb help with table variables? No. Their rollback is always instant because table variables are not rolled back.

Does optimized locking apply to temporary tables? No. tempdb does not support optimized locking.

When tempdb is your bottleneck

Whether ADR for tempdb saves rollback time or costs throughput in your case shows up in a measurement under your load. As part of a performance analysis, we measure this on a test instance and recommend whether and when the restart is worthwhile. If you want to have tempdb examined as a whole, the SQL Server Health Check is the right starting point; the fundamentals are in the tempdb hub.

More locking topics