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 special version store size, in kilobytes, used during online index rebuild. |
current_aborted_transaction_count |
bigint | The number of abort transactions in the database. Detail of the abort transactions can be viewed in 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 abort 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. Corresponds to online_index_version_store_size_kb. |
secondary_low_water_mark |
bigint | The low water mark aggregated for queries on readable secondaries. It's a transaction ID and can be used to compare with oldest_active_transaction_id and oldest_aborted_transaction_id. |
offrow_version_cleaner_start_time |
datetime2(7) | The 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. |
aborted_version_cleaner_start_time |
datetime2(7) | The start timestamp of a full sweep. |
aborted_version_cleaner_end_time |
datetime2(7) | The end timestamp of last full 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 for reclaim due to hold up from secondary read queries. |
pvs_off_row_page_skipped_transaction_not_cleaned |
bigint | The number of pages skipped for reclaim 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 for large PVS issue. |
pvs_off_row_page_skipped_oldest_active_xdesid |
bigint | The number of pages skipped for reclaim due to the oldest active transaction. |
pvs_off_row_page_skipped_min_useful_xts |
bigint | The number of pages skipped for reclaim due to a long snapshot scan. |
pvs_off_row_page_skipped_oldest_snapshot |
bigint | The number of pages skipped for reclaim due to online index rebuild activities. This scenario isn't common for PVS usage. |
pvs_off_row_page_skipped_oldest_aborted_xdesid |
bigint | The number of pages skipped for reclaim 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.