SQL Server 2019 - sys.dm_db_index_physical_stats - version_record_count

Question

On a server with SQL Server 2019 Standard Edition installed, none of our databases have the Accelerated Database Recovery enabled yet when I run the DMV - sys.dm_db_index_physical_stats, the column version_record_count is showing a non-zero value.

Question is, if this feature is disabled then why is the DMV telling me that row versions are being retained for tables in each database?

The databases in question are using Snapshot Isolation, but I would have thought that the verisons would be stored in tempdb.

Answer 1

Seems as though this is a documentation issue, as the version_record_count, as suspected, is showing any versioned records and not just those from PVS (due to ADR).

Simple repro:

create database versiontest
go

alter database versiontest set allow_snapshot_isolation on
go

use versiontest
go

create table test
(
id  int identity(1,1) not null,
crapdata    char(4000) not null default ('Crap')
)
go

insert into test default values
go 100

select database_id, OBJECT_NAME(object_id), index_id, index_type_desc, version_record_count, inrow_version_record_count, inrow_diff_version_record_count, offrow_regular_version_record_count, offrow_long_term_version_record_count FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('test', 'U'), default, default, 'detailed')
go

begin tran
update test set crapdata = 'More Crap'
go

select database_id, OBJECT_NAME(object_id), index_id, index_type_desc, version_record_count, inrow_version_record_count, inrow_diff_version_record_count, offrow_regular_version_record_count, offrow_long_term_version_record_count FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('test', 'U'), default, default, 'detailed')
go

rollback
go

select database_id, OBJECT_NAME(object_id), index_id, index_type_desc, version_record_count, inrow_version_record_count, inrow_diff_version_record_count, offrow_regular_version_record_count, offrow_long_term_version_record_count FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('test', 'U'), default, default, 'detailed')
go

use master
go

drop database versiontest
go