Modifier

Estimate compressed data size in your Business Central online tenant

In the online version of Business Central, data is compressed using the SQL Server data compression feature. As a consequence, the data size in your on-premises database might not match the data size when migrated to the Business Central service.

Currently, all tables in the online version of Business Central are compressed with CompressionType set to Page.

To evaluate whether a table is a good candidate to compress, you can use the stored procedure sp_estimate_data_compression_savings in SQL Server. Learn more in sp_estimate_data_compression_savings (Transact-SQL).

Note

The stored procedure sp_estimate_data_compression_savings fails if the table has columns with & in the name.

If you want to estimate the compressed size of all or some tables in your database, you can create and, if needed, modify the following stored procedure:

CREATE PROCEDURE dbo.estimate_page_compressed_table_sizes
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @table_name sysname;
 
    CREATE TABLE #compressed_table_report (
        table_name sysname,
        schema_name nvarchar(max),
        index_id int,
        partition_number int,
        size_with_current_compression_setting bigint,
        size_with_requested_compression_setting bigint,
        sample_size_with_current_compression_setting bigint,
        sample_size_with_requested_compression_setting bigint
    );
 
    DECLARE tables_cur CURSOR FOR 
    SELECT name
    FROM sys.tables
    WHERE schema_id = SCHEMA_ID('dbo');
 
    OPEN tables_cur;
 
    FETCH NEXT FROM tables_cur INTO @table_name;

    WHILE @@FETCH_STATUS = 0 
    BEGIN
        INSERT INTO #compressed_table_report
        EXEC sp_estimate_data_compression_savings
            @schema_name = 'dbo',
            @object_name = @table_name,
            @index_id = NULL,
            @partition_number = NULL,
            @data_compression = 'PAGE';
 
        FETCH NEXT FROM tables_cur INTO @table_name;
    END;
 
    CLOSE tables_cur;
    DEALLOCATE tables_cur;
 
    SELECT
        table_name,
        SUM(size_with_current_compression_setting)
            AS size_with_current_compression_setting_KB,
        SUM(size_with_requested_compression_setting)
            AS size_with_requested_compression_setting_KB,
        SUM(
            size_with_current_compression_setting -
            size_with_requested_compression_setting
        ) AS size_saving_KB
    FROM #compressed_table_report
    GROUP BY table_name
    ORDER BY size_saving_KB DESC;
 
    DROP TABLE #compressed_table_report;
END;
GO

To run the stored procedure, execute the following commands:

USE [TenantDatabase] -- Replace TenantDatabase with your tenant database name.
GO

EXEC dbo.estimate_page_compressed_table_sizes
GO

The stored procedure sp_estimate_data_compression_savings fails if the table has columns with & in the name. With the following query, you can find the table names that should be excluded in definition of the stored procedure dbo.estimate_page_compressed_table_sizes.

SELECT t.name AS table_name
     , col.name AS column_name
  FROM sys.columns col
 INNER JOIN sys.tables t ON col.object_id = t.object_id
 WHERE col.name LIKE '%&%'

Next steps