sys.dm_tran_persistent_version_store_stats (Transact-SQL)
Applies to: 
SQL Server 2019 (15.x)
Azure SQL Database
Azure SQL Managed Instance
Returns information for accelerated database recovery (ADR) persistent version store (PVS) metrics.
Table returned
| Column name | Data type | Description |
|---|---|---|
database_id |
int | The database_id of this row.In Azure SQL Database, the values are unique within a single database or an elastic pool, but not within a logical server. |
pvs_filegroup_id |
smallint | The filegroup that hosts PVS version store. |
persistent_version_store_size_kb |
bigint | The PVS size in kilobytes. This value is used to determine current space used by PVS. |
online_index_version_store_size_kb |
bigint | The size of a special version store size used during online index rebuild, in kilobytes. |
current_aborted_transaction_count |
bigint | The number of aborted transactions in the database. For more details, see sys.dm_tran_aborted_transactions. |
oldest_active_transaction_id |
bigint | The transaction ID of the oldest active transaction. |
oldest_aborted_transaction_id |
bigint | The transaction ID of the oldest aborted transaction. If the PVS cleaner can't remove the aborted transaction, this value reflects the oldest value. |
min_transaction_timestamp |
bigint | The minimum useful timestamp in the system from snapshot scans. |
online_index_min_transaction_timestamp |
bigint | The minimum useful timestamp in the system to hold up the PVS cleanup during online index builds. |
secondary_low_water_mark |
bigint | The low water mark aggregated for queries on readable secondaries. The value is a transaction ID and can be matched with oldest_active_transaction_id and oldest_aborted_transaction_id. |
offrow_version_cleaner_start_time |
datetime2(7) | The last start time of the off-row PVS cleanup process. |
offrow_version_cleaner_end_time |
datetime2(7) | The last end time of the off-row PVS cleanup process. If start time has value but the end time doesn't, it means PVS cleanup is ongoing on this database. |
aborted_version_cleaner_start_time |
datetime2(7) | The start timestamp of the last aborted transaction sweep. |
aborted_version_cleaner_end_time |
datetime2(7) | The end timestamp of last aborted transaction sweep. If start time has value but the end time doesn't, it means PVS cleanup is ongoing on this database. |
pvs_off_row_page_skipped_low_water_mark |
bigint | The number of pages skipped during cleanup due to hold up from secondary read queries. |
pvs_off_row_page_skipped_transaction_not_cleaned |
bigint | The number of pages skipped during cleanup due to aborted transactions. Note this value doesn't reflect the PVS hold up from aborted transactions since the version cleaner uses a min threshold for aborted transaction version cleanup. Can be ignored when troubleshooting large PVS issues. |
pvs_off_row_page_skipped_oldest_active_xdesid |
bigint | The number of pages skipped during cleanup due to the oldest active transaction. |
pvs_off_row_page_skipped_min_useful_xts |
bigint | The number of pages skipped during cleanup due to a long snapshot scan. |
pvs_off_row_page_skipped_oldest_snapshot |
bigint | The number of pages skipped during cleanup due to online index rebuild activities. |
pvs_off_row_page_skipped_oldest_aborted_xdesid |
bigint | The number of pages skipped during cleanup due to oldest aborted transactions. Reflects how many pages were skipped during cleanup because they contained row versions for aborted transactions. Applies to: SQL Server 2022 (16.x) and later versions. |
Permissions
On SQL Server and SQL Managed Instance, requires VIEW SERVER PERFORMANCE STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerPerformanceStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE PERFORMANCE STATE permission on the database, or membership in the ##MS_ServerPerformanceStateReader## server role is required.