Reducing table size during initial load with ADR in SQL Server

Question

When a table is created, and loaded with data from scratch based on a file, the table size changes significantly when Accelerated Database Recovery is on in SQL Server (realized based on my question here, Table size in Azure SQL Managed Instance vs On Premise SQL Server)

Is there any way (perhaps via a hint) to reduce table size in a database with ADR on (as it cannot be turned off in our case) only during an initial load? There is no possibility of conflict during this initial load, and all of the relevant data can be loaded at once.

Thanks!

score 1 · Answer 1 · answered Apr 20 '20 at 16:59

I don't see any significant difference in this test.

create database nonadr
ALTER DATABASE nonadr SET ACCELERATED_DATABASE_RECOVERY = OFF

create database adr
ALTER DATABASE adr SET ACCELERATED_DATABASE_RECOVERY = ON;

go

with q as
(select top (1000*1000*10) *
 from sys.messages m, sys.objects o
)
select *
into nonadr.dbo.test
from q;


with q as
(select top (1000*1000*10) *
 from sys.messages m, sys.objects o
)
select *
into adr.dbo.test
from q;

go

exec nonadr.sys.sp_spaceused 'test' 
exec adr.sys.sp_spaceused 'test'

SELECT DB_NAME(database_id) AS database_name,
       (persistent_version_store_size_kb / 1024.) AS persistent_version_store_size_mb
FROM sys.dm_tran_persistent_version_store_stats
WHERE persistent_version_store_size_kb > 0;

outputs

name                 rows                 reserved           data               index_size         unused
-------------------- -------------------- ------------------ ------------------ ------------------ ------------------
test                 10000000             3352392 KB         3351944 KB         8 KB               440 KB

name                 rows                 reserved           data               index_size         unused
-------------------- -------------------- ------------------ ------------------ ------------------ ------------------
test                 10000000             3494856 KB         3494384 KB         8 KB               464 KB

database_name        persistent_version_store_size_mb
-------------------- ---------------------------------------
adr                  0.070312

Reducing table size during initial load with ADR in SQL Server

1 Answers1