1

In doing a test, I have observed that the size of the same table with the same exact DDL (created from scratch) with the same exact data (10M rows) varies drastically in our Azure SQL Managed Instance vs a local installation of SQL Server: 336 MB in the managed instance vs 198 MB in the local.

The steps are the same in either scenario: - Run the table creation script (exact same) without any primary key/index in both environments - Load a file with 10M rows (3 columns) to the table with bcp in both environments

Does anyone have any insight as to why the managed instance takes significantly more space? (I am not worried about the actual MBs, but the ratio since this is a table that will contain billions of rows).

eatplayrove
  • 121
  • 1

1 Answers1

3

The Accelerated Database Recovery (ADR) feature introduced as part of SQL Server 2019 is enabled automatically on SQL Server Managed Instances.

Part of the way this feature is implemented is to include a 14 byte row versioning tag with each row.

14 bytes per row * 10,000,000 rows = 140,000,000 bytes = 133.5 MB of extra data to support ADR.

This explains the entirety of the difference in the approximate numbers provided (336 MB - 198 MB = 138 MB).

You should see the size of the SQL Server 2019 database match the Managed Instance size if you enable ADR prior to loading the 10,000,000 rows:

ALTER DATABASE [YourDatabaseName] 
SET ACCELERATED_DATABASE_RECOVERY = ON;
Josh Darnell
  • 30,133
  • 5
  • 70
  • 124