1

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!

  • 1
    How are you measuring the table size? – David Browne - Microsoft Apr 20 at 15:53
  • Hi @DavidBrowne-Microsoft, I use EXEC sp_spaceused 'tableName'; – eatplayrove Apr 20 at 16:41
1

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
| improve this answer | |
  • CREATE DATABASE test_adr; ALTER DATABASE test_adr SET ACCELERATED_DATABASE_RECOVERY = ON; CREATE TABLE test_adr.dbo.sample ( col1 bigint NOT NULL, col2 smallint NOT NULL, col3 tinyint NOT NULL); INSERT INTO test_adr.dbo.sample WITH (TABLOCK) select t1.k , t1.k%32000, t1.k%100 from ( SELECT ROW_NUMBER() OVER(ORDER BY a.object_id) as k from sys.all_columns, sys.all_columns a ) t1 where t1.k < 1000000; I create a 3 column table with 1M entries as above, with ADR it's ~34 MB, without it's ~20 MB. Ratio is pretty consistent as entries grow. – eatplayrove Apr 20 at 17:53
  • That's just the 14 bytes/row for the row version pointer you get with either ADR or READ_COMMITTED_SNAPSHOT. – David Browne - Microsoft Apr 20 at 18:09
  • Thank you @David, great information that I read up on. Not much to do then. – eatplayrove Apr 20 at 18:44
  • Any table with billions of rows should probably use a clustered columnstore index if you want to optimize for storage space and query speed. – David Browne - Microsoft Apr 20 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.