In this blog post we will discuss the Benefits and Costs of database compression and we will provide Best Practices for successful data compression.
Why data compression matters?
Data compression is one of the Data Volume Management strategy (together with Data Clean up and Data Archive) which as we know, consists on actively managing the amount and type of data retained in the Dynamics AX database.
What are the key benefits of Data Volume Management?
- It minimizes database storage requirements.
- It minimizes time to recovery in case of disaster.
- It improves query performance.
When visiting our Dynamics AX customers, we can see they’re often facing issues with databases growing very fast. There is no volume threshold rule on when to apply the compression, however there are few conditions to be considered:
- DVM strategy order: usually it’s recommended to clean up temporary data first (there will be a blog post coming soon on Dynamics AX clean up strategies so stay tuned) and optional archive data before compressing
- Versions of SQL Server and Dynamics AX
- CPUs condition of SQL Server
- Type and usage of data you want to compress
For a quick reminder: SQL Server Data compression was introduced in SQL Server 2008. It is only allowed with SQL Server Enterprise and Developer editions. Recently the compression support was also announced for SQL 2016 and Azure SQL Database.
Compression is supported for Dynamics AX 2009 and Dynamics AX 2012 from SQL Server. Starting from Dynamics AX 2012 you also have the option to enable compression directly from within the application.
Benefits and Costs of database compression
- Storage: Decrease the size of database size (40-60% smaller)
- Performance: Improve performance when disk I/Os is a bottleneck because it will reduce the I/Os to the disk subsystem. It will also keep more data in memory, improving overall system performance as the compressed index is read into SQL Memory (Buffer Pool) in a compressed state.
- Requires SQL Server Enterprise Edition.
- CPU Utilization: Increased processor utilization (5 -15%) so may decrease performance if CPU is already a bottleneck.
- Performance: Writing to a compressed index takes longer than writing to an uncompressed index (especially for when using PAGE compression) See this blog article for more details.
Best practices for Data compression – Before you start:
- We recommend all tables to have at least a clustered index
- Compression should be tested and measured on Test environment with similar characteristics than Production to be able to have reliable operations.
- It’s recommended to apply compression during offline hours with no users on the system as the compression process itself may impact I/Os performance and any running queries. Compression goes through ALTER Rebuild Index ONLINE where possible, but some indexes need to be Rebuild OFFLINE only.
- Depending on the hardware and data size, multiple maintenance windows may be needed to accomplish entire scope of compression. There are many factors which can impact the duration of compression operation, particularly the performance of the IO sub-system. But for a given index, it is common to see compression taking 1,5 to 2 times more time than classic ALTER REBUILD operation.
- Backup should be taken before the compression in case any unexpected damages occur (hardware failures, sudden power outages, etc...)
See part 2 of this blog series: Planning Database compression here.
Premier Field Engineer Dynamics AX
Senior Premier Field Engineer SQL Server