Sample Header Ad - 728x90

sys.dm_tran_version_store_space_usage reports always zero space used

4 votes
1 answer
335 views
I was learning about RCSI when I discovered something strange on Azure SQL Database. The DMV
.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 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? https://learn.microsoft.com/it-it/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage?view=sql-server-ver15 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?
Asked by Davide De Pretto (135 rep)
Jan 24, 2021, 12:04 AM
Last activity: Jan 24, 2021, 05:38 PM