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).

4
  • 2
    Advanced Database Recovery is running in Azure SQL DB. Are you also running it on your local instance? It might account for the space since page versions are kept in the database instead of tempdb to enable faster recovery on failovers. Also, if you are going to use billions of rows, you might want to consider columnstores. Apples to apples comparisons with on-premises is best done if you align the features to be the same. It's the same engine under the covers, essentially. Commented Apr 20, 2020 at 14:18
  • Thank you so much @ConorCunninghamMSFT . This exactly explained it, I created a local DB with Accelerated Database Recovery, and the numbers now match exactly. Do you know whether a table can be loaded more compactly if for a particular transaction I do not care about failure (e.g., an initial load from a file). Commented Apr 20, 2020 at 14:33
  • Thanks @LowlyDBA, will do. Commented Apr 20, 2020 at 14:52
  • If you plan to process a file you loaded (iterate over it a few times), then you can use tempdb first if the data fits there since it is in simple recovery mode and does not really need to deal with long recoveries on failover (and thus is not using ADR) Commented Apr 23, 2020 at 21:44

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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