I was learning about RCSI when I discovered something strange on Azure SQL Database.
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) <X64> 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 1 ON pre_workload_space_count 0 post_workload_page_count 0 post_cleaning_space_count 0
SQL Server 2019 appears to behave correctly, reporting 8 pages worth of data in the version store right after the workload and then be cleaned up after a minute or so. On Azure SQL Database, though, the space used is always zero! Is that a correct behavior? What does it means?
Books Online reports this DMV to be compatible both with SQL Server and Azure SQL Database but also says "The following query can be used to determine space consumed in tempdb, by version store of each database in a SQL Server instance.". As far as I know Azure SQL Databases do have a limited scope to their parent instance by design. Can that be the root cause?
I know that Azure SQL Databases run under RCSI by default and also that they have a fixed amount of tempdb space based on the tier you get, so I was concerned about filling that up with long running transaction or other processes keeping lots of records in the version store for too long. But I cannot manage something I can't measure, right?