NOTE: Compression is only available in SQL Server Enterprise Edition and Higher and also Developer Edition.
Many Dynamics AX Customers have multiple non-production environments used for TEST, DEV, STAGING, UAT, or etc... For each one of those non-production environments there is usually a full copy of the Dynamics AX Production database and if that database is of any significant size all those copies of the database can use up a lot of expensive drive space whether that be SAN, DAS, or even local. With SQL Server 2008 we added the ability to compress databases. This compression is done on an index by index basis and has two distinct levels PAGE and ROW. In SQL Server 2008 R2 we further expanded the compression functionality to add Unicode compression which was built into ROW compression.
A quick description of ROW and PAGE Compression
ROW Compression - Uses variable-length storage format for numeric types (for example integer, decimal, and float) and the types that are based on numeric (for example datetime and money). It stores fixed character strings by using variable-length format by not storing the blank characters. If the types are Unicode and does not contain double byte data it removes the extra unnecessary byte. This is the "lowest" level of compression.
PAGE Compression - PAGE Compression starts with ROW compression and then "de-duplicates" data down columns and across rows on each data page in the index. This is the "highest" level of compression
For more information on SQL Server Compression please see the link below
Compression as all things has both its Pro's and Con's
1. Use less disk space as the data is now stored in the disk in a compressed format which means it requires less IO to read a compressed index
2. The compressed index is read into SQL Memory (Buffer Pool) in a compressed state. So if an uncompressed index took up 1 GB of memory and you compressed 50% it not only takes up 500 MB of SQL Memory leaving 500 MB more to cache more data.
The more data you can cache the less you have to go to disk and the more responsive SQL Server will be.
1. SQL Server compression with increase CPU utilization across the board on your SQL Server anywhere from 5-30%. (Usually between 7 and 15%)
2. Writing to a compressed index takes longer that writing to an uncompressed index
Example on a 30,000,000 million row table with 1 clustered index and 5 non-clustered indexes
PAGE COMPRESSION (LEDGERTRANS ALL INDEXES)
AVG INSERT TIME FOR 100000 ROWS - 0.201 MS
ROW COMPRESSION (LEDGERTRANS ALL INDEXES)
AVG INSERT TIME FOR 100000 ROWS - 0.131 MS
NO COMPRESSION (LEDGERTRANS ALL INDEXES)
AVG INSERT TIME FOR 100000 ROWS - 0.095 MS
As you can see there is a write penalty when dealing with Compressed Indexes. Taking the Pro's and Con's into consideration full PAGE Compression of the Dynamics AX Database is a perfect fit for non-production environments. If he copy of the database is not used for any type of load testing or performance resting the write penalty associated with PAGE compression becomes a non-issue. PAGE compressing all indexes in non-production copies of your Dynamics AX Database running on SQL Server 2008 R2 especially can yield some pretty significant space savings.
BEFORE Dynamics AX 2012 database. Size uncompressed, 872,862 MB, SQL Server 2012 Enterprise Edition.
BEFORE Dynamics AX 2012 database. Size with all Indexes PAGE Compressed, 287,636 MB, SQL Server 2012 Enterprise Edition
Based on the sample above you can see if you have 3 TEST, DEV, UAT, copies of he production Dynamics AX database you can save significant amounts of drive space. In this example if the customer had a TEST, DEV, and UAT copy of the database they would have reduced, reused, and recycled 1,755 GB or 1.8 TB of disk space.
NOTE: Compressed Indexes and the Dynamics AX AOT. Dynamics AX 4.0 - 20012 R2 support database compression through the use of the SQLSTORAGE table in the AX Database. When an index is compressed in Dynamics AX it needs a set of corresponding records in the SQLSTORAGE table to make the AOT aware of the compression so if a full synchronization is done the Compression is not lost. Now for non-production AX database you may or may not need to do this depending on your code promotion practices. The most import thing is that you do not want to carry the Compression or the info in the SQLSTORAGE table from the non-production environment over to Production.
I have attached a set of scripts to this BLOG article to aide you in in these tasks and allow you to claim some of valuable disk space that their never seems to be enough of.
1. "1 - PAGE Compression ALL Indexes (OFFLINE only).sql"
2. "2 - Populate SQLSTORAGE to sync Compression to AOT.sql"
3. "3 - Delete Compression Info from SQLSTORAGE to remove Compression info from AOT.sql"
4. "4 - ROLLBACK Compression.sql"
Script "1" when run against a Dynamics AX non-production database will PAGE compress all indexes in the non-production database. Depending on Database size and hardware this can take anywhere from a few hours to a day on large databases on underpowered SQL Servers. When this script is complete all indexes with the PAGE compressed, but the "Size" of the database will not have changed. The device size will stay the same it will just have more Free Space. You can shrink the database device size down by right clicking on the database you just compressed and selecting TASK--> SHRINK --> FILES. Make sure the file type is set to "DATA". Then select "Reorganize Space before releasing unused space". Then in the "Shrink File To:" box set the number to what you want to shrink the device file to, remember to leave 10% or so space so it does not autogrow right away".
Script "2" when run against a Dynamics AX non-production database. This will insert the necessary records into the SQLSTORAGE table in Dynamics AX to make the AOT aware of the compression. In order for this to work you will need to have Performance Analyzer for Dynamics (DynamicsPerf) at least downloaded. In the "\DynamicsPerf1.16 for SQL2008+\DynamicsPerf\Scripts - Dynamics AX" directory you will find two *.XPO files "PrivateProject_AOTExport_Batch" for 4.0 and 2009 and "PrivateProject_AOTExport2012" for all newer versions. Import the correct AOTEXPORT.xpo file and run on the non-production Dynamics AX database or this step will not work.
You can download the latest version of Performance Analyzer for Dynamics (DynamicsPerf) from the link below.
Script "3" when run against a Dynamics AX non-production database. It will remove all the SQLSTORAGE entries from the compression.
Script "4" when run against a Dynamics AX non-production database. It will ROLLBACK all the PAGE compressed indexes to uncompressed
We have done compression for Dynamics AX customers on their production databases, but we take a much more nuanced approach. This approach looks at index utilization statistics so we do not compress tables with a heavy write to read ratio and use both ROW and PAGE compression to come up with the most optimal compression solution for a customer that provides the most benefit while avoiding compressing indexes that could provide performance issues due to the write penalty. We offer this as an Ad-Hoc service to any of our Dynamics AX customers who have Premier support. If you are interested contact your TAM or your Dynamics DSE for more information.