Version Store and ADR

Overview

SQL Server keeps old versions of rows in two places.

• The tempdb version store holds them for databases with read committed snapshot (RCSI) or snapshot isolation (SI) turned on. It is shared by the whole instance, and nothing can be cleaned out of it while an older transaction that uses row versions is still open.
• Accelerated Database Recovery (ADR), in SQL Server 2019 and later, keeps a persistent version store (PVS) inside the user database itself. A background cleaner removes versions no one needs any more, but it has to stop at the oldest active transaction, at aborted transactions not yet cleaned, at a snapshot reader, and at an availability group secondary that has not caught up.

When either store grows, the question is always the same: what is holding it? The Version Store and ADR report answers that per database and names the session.

Two pages share the report:

• Version Store and ADR (database level, under Real Time) – a card for the database and a grid of its open transactions, oldest first, with the one holding the cleaner marked.
• Version Store and ADR by Database (instance level) – a card for each database that keeps row versions, worst first, and a grid of every database.

Where to find it

Reading the page

Each card shows ADR on or off, the PVS size and its share of the database’s used data space, the online index version store, the aborted transactions still to clean, the database’s share of the tempdb version store, and the oldest open transaction. The line under them is the cleanup blocker, chosen in this order:

1. An open transaction in the database (a long running transaction when the cleaner has mostly skipped pages for the minimum useful timestamp), with its session, login and host.
2. A snapshot reader in the database.
3. An availability group secondary behind the secondary low water mark.
4. Aborted transactions waiting for the aborted version cleaner.
5. Otherwise, the reason the cleaner skipped the most pages for since startup (pvs_off_row_page_skipped_*).

The heading gives the tempdb version store for the whole instance from sys.dm_db_file_space_usage, and on the instance page also the total of sys.dm_tran_version_store_space_usage by database. The summary line names the longest running snapshot transaction on the instance.

Right-click a session to go to What is Active, Sessions and the other session pages. On the database page, PVS Cleanup Script shows sys.sp_persistent_version_cleanup for the database with a warning first: it runs the cleaner in the foreground, which is heavy on a busy database, and it cannot clean versions that an open transaction still needs. Nothing is run from the report. On the instance page, double click a database to open its database page. Both grids support the usual CSV and Excel export, and Copy the findings as text copies the cards.

Permissions and versions

• The PVS, version store and transaction views need VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later). Each part that is refused is named on the page; the rest still shows.
• Used data space is read with FILEPROPERTY in each ADR database. When that is not possible the percent is taken of the allocated data file size.
• Before SQL Server 2019 there is no persistent version store, so only the tempdb version store is shown. The per database tempdb figure needs sys.dm_tran_version_store_space_usage (SQL Server 2016 SP2 and 2017 or later); the instance total works on 2008 and later.