sys.dm_tran_version_store_space_usage reports always zero space used

I was learning about RCSI when I discovered something strange on Azure SQL Database.

The DMV sys.dm_tran_version_store_space_usage always reports 0 as the space used by the version store, even if I run a CRUD workload just before.

To demonstrate this behavior, I created a little test.

-- Server info
select @@version as sql_version;

-- Database info
select
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
from sys.databases
where database_id = db_id();

-- Just to be sure the current database has its version store empty
select reserved_page_count as pre_workload_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

-- Test workload
drop table if exists RCSI_TEST;

create table RCSI_TEST (
    id uniqueidentifier default newid()
);
go

insert into RCSI_TEST default values;
go 100

update RCSI_TEST
set id = newid();

delete from RCSI_TEST;

-- Metrics
select reserved_page_count as post_workload_page_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

waitfor delay '00:01:30'; -- Just to be sure! ;)

select reserved_page_count as post_cleaning_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

I ran this script both on SQL Server 2019 Developer Edition (on Docker) and on an Azure SQL Database (tier S0, 10DTU) and here are the results.

SQL Server 2019

sql_version
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)Sep 23 2020 16:03:08    Copyright (C) 2019 Microsoft Corporation    Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) 

is_read_committed_snapshot_on   snapshot_isolation_state_desc
1                               ON

pre_workload_space_count
0

post_workload_page_count
8

post_cleaning_space_count
0

Azure SQL Database

```
sql_version
Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 1 2020 18:48:35 Copyright (C) 2019 Microsoft Corporation

is_read_committed_snapshot_on snapshot_isolation_state_desc

Azure SQL Database uses Accelerated Database Recovery, and so RCSI doesn't use TempDb for the version store. Instead the version store is inside the database to enable "Instantaneous transaction rollback", which is especially important during a failover:

The persisted version store is a database engine mechanism for persisting the row versions generated in the database itself instead of the traditional tempdb version store. PVS enables resource isolation and improves availability of readable secondaries.

Accelerated Database Recovery

So you should look in sys.dm_tran_persistent_version_store_stats, or see generally Manage accelerated database recovery

StackExchange Database Administrators Q#283852, answer score: 4