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
Last activity: Jan 24, 2021, 05:38 PM