Yesterday Kennie Nybo Pontoppidan (from the Dynamics 365 Business Central Team) asked us an interesting question: do you use data compression on your Dynamics 365 Business Central on-premise database? If yes, what type of compression?
From the answers I can see that there are partners that uses data compression widely, partners that uses data compression only on certain tables and partners that are not using data compression at all. Who is the winner? It’s quite difficult to say I think…
What is data compression?
SQL data compression is a technology that’s been around since SQL Server 2008 Enterprise Edition. Starting from SQL Server 2016 SP1, data compression is available on ALL the SQL editions.
There are two possible types of data compression on SQL Server:
- Row-level compression: it works by converting any fixed length data types into variable length types (for example a char(150) field that doesn’t cover all the 150 characters is compressed to the real length).
- Page-level compression: this is a more advanced level of compression. By default, page compression also implements the row level compression at first, then it adds two other types of compression (Prefix compression and Dictionary compression on columns).
As of Business Central April 2019, the use of SQL Server data compression is a natively supported configuration. You can use data compression to help reducing the size of selected tables in your Dynamics 365 Business Central database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries have to read fewer pages from disk. This is especially useful if your storage system is based on standard disks and not SSD disks.
With AL language, when you define a table you can use the new CompressionType property to define data compression:
Saying that, what’s my opinion on this topic? 🙂
I normally suggest to always use data compression on your Dynamics 365 Business Central on-premise databases. My favourite rule (absolutely a personal rule) for Dynamics 365 Business Central is to use Page-level compression on the Ledger Tables, Posted Sales and Purchase documents and on archive tables. Normally, I suggest to use the sp_estimate_data_compression_savings stored procedure to decide which type of data compression to apply on a table (this advice comes from a suggestion given to me by Jörg Stryk years ago). If the difference between row-level and page-level compression is under 5%, use row-level compression otherwise use page-level compression.
As an example, this is the query I use for choosing a data compression strategy for an heavy table:
EXEC sp_estimate_data_compression_savings @schema_name = 'dbo' ,@object_name = 'Cronus$Item Ledger Entry' ,@index_id = 1 ,@partition_number = NULL ,@data_compression = 'PAGE' GO
The above query estimates the data compression by applying page-level compression to the Item Ledger Entry table.
This is what happens on my database if I test page-level and row-level compressions on this table:
As you can see, with page-level compression, my table from 283360 Kb is reduced to 54152 KB.
With row-level compression:
the table size from 283360 Kb is reduced to 123600 KB.
As you can see, for this table is much better to use page-level compression.
If you want to save space and increase performances on big tables, I suggest to use data compression on your heavy tables. But what are the connected “problems” on using data compression?
I think that you need always to remember that when you use SQL data compression, the data is also stored in RAM in a compressed state. When the data is retrieved by the client it’s uncompressed and this can cause an increase load on your CPU. Row-level compression requires less CPU usage than page-level compression. If you use data compression, you need to be sure that your SQL Server CPU has the power to satisfy your needs (please check that your CPU is never above 80%). But normally I think that you don’t under-estimate your CPU power isn’t it? 🙂
Stefano Demiliani 