Estimating the data size in your Business Central online tenant

  • Artikel

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. For more information, see 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 possibly modify) the following stored procedure:

CREATE PROCEDURE estimate_page_compressed_table_sizes
AS 
SET NOCOUNT ON
BEGIN
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
-- uncomment and adjust this part if you want to only include some tables in the calculation
-- WHERE name IN ('table name 1', 'table name 2', 'table name 3') 
--
-- uncomment and adjust this part if you want to restrict the tables in the calculation
-- WHERE name NOT IN ('table name 1', 'table name 2', 'table name 3') 
;
 
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', -- Business Central use the dbo schema
        @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
     , avg(size_with_current_compression_setting) as avg_size_with_current_compression_setting_KB
     , avg(size_with_requested_compression_setting) as avg_size_with_requested_compression_setting_KB
     , avg(size_with_current_compression_setting - size_with_requested_compression_setting) AS avg_size_saving_KB
  FROM #compressed_table_report
 GROUP BY table_name
 ORDER BY avg_size_saving_KB DESC 
;
 
DROP TABLE #compressed_table_report
;
 
END
SET NOCOUNT OFF
GO

To run the stored procedure, execute the following commands:

USE <tenant database> // change to your database
GO

EXEC 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 estimate_page_compressed_table_sizes.

SELECT t.name AS table_name,
     , ind.name AS index_name,
     , col.name AS column_ame
  FROM sys.indexes ind 
 INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id 
 INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id 
 INNER JOIN sys.tables t ON ind.object_id = t.object_id 
 WHERE col.name LIKE '%&%'

Next steps