| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.dm_tran_persistent_version_store_stats (Transact-SQL) |
The sys.dm_tran_persistent_version_store_stats dynamic management view returns information for accelerated database recovery (ADR) persistent version store (PVS) metrics. |
rwestMSFT |
randolphwest |
wiassaf |
06/19/2023 |
sql |
system-objects |
reference |
|
|
|
>=sql-server-ver15||>=sql-server-linux-ver15||=azuresqldb-mi-current||=azuresqldb-current |
[!INCLUDE SQL Server 2019, ASDB, ASDBMI]
Returns information for accelerated database recovery (ADR) persistent version store (PVS) metrics.
| Column name | Data type | Description |
|---|---|---|
| database_id | int | The database_id of this row. In [!INCLUDE ssazure-sqldb], 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 cannot remove the aborted transaction, this value will reflect 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. This corresponds to online_index_version_store_size_kb. |
| secondary_low_water_mark | bigint | The low water mark aggregated for queries on readable secondaries. It is 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 does not, 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 does not reflect the PVS hold up from aborted transactions since the version cleaner uses a min threshold for aborted transaction version cleanup. This 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 is not common for PVS usage. |
| pvs_off_row_page_skipped_oldest_aborted_xdesid | bigint | Applies to: [!INCLUDEsssql22-md] and later. The number of pages skipped for reclaim due to oldest aborted transactions. If the version cleaner is slow or invalidated, this will reflect how many pages must be kept for aborted transactions. |
On [!INCLUDEssNoVersion_md] and SQL Managed Instance, requires VIEW SERVER 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_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
Review Best practices for accelerated database recovery. If your ADR PVS is growing, see Troubleshoot accelerated database recovery.