1

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.

4
  • 2
    What is the transaction isolation of the database?
    – J.D.
    Jan 19 at 19:46
  • Apologies, should have mentioned the databases in question are using Snapshot Isolation, have updated the original question when this info. Jan 21 at 17:16
  • Hmm, well there goes that theory. It does seem so long as ADR is disabled, the row versions are managed in tempdb. Absolutely positive ADR isn't enabled? lol.
    – J.D.
    Jan 21 at 19:34
  • 100% sure :) ADR is disabled. Jan 22 at 10:41

1 Answer 1

4

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
3
  • Ah ok, that makes sense, thanks for answering the question Sean and including an example. Jan 22 at 17:41
  • @user1722621 Don't forget to accept this answer, so others see it verified, if you found it helpful. (Thanks Sean!)
    – J.D.
    Jan 24 at 16:31
  • Done, thanks J.D. Jan 25 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.