DynamicsPerf 2.0 - SQL Server Compression scripts explained!

Within the DynamicsPerf installation files in the DynamicsPerf/Scripts SQL Server Compression folder, we have two 2 SQL Query files that will allow you to systematically define, analyze, and compress your Dynamics AX SQL Server database at the index/table level, allowing you to first perform a pre-compression analysis in a test environment, identifying the tables/indexes subject to being compressed, calculate the compression times of each index, and finally create an automated SQL script to execute the compression of tables/indexes in the production environment. 

The 2 SQL Query files can be used to perform compression in the 3 major steps described below: 

1. Analysis, compression, and estimation of compression times in Test environment.
2. Running compression in Production environment with script result from Test environment
3. Running Script “2 Dynamics SQL Data Compression Tool - AX ONLY Post Process.sql” post-compression in Prod environment.

Important considerations before we go into detail on each step: 

• PLEASE MAKE A BACKUP BEFORE DOING ANY DATABASE COMPRESSION ACTIVITIES!! 

• Performance Analyzer “DynamicsPerf” needs to be implemented to complete the compression process. It can be downloaded from GitHub 

• Compression is only available on SQL 2008 and later and Enterprise/Developer Editions or SQL server 2016 and above. 

• After compressing your database, you will only be able to restore a database backup to Enterprise/Developer Edition of SQL Server. 

• These scripts are presented "AS IS" and has no warranties expressed or implied. 

• For Dynamics AX you will need to implement DynamicsPerf 2.0 to complete the compression process. 

• DynamicsPerf 2.0 must be installed and the AOTExport2012_Direct Class Run Before Executing the script “2 Dynamics SQL Data Compression Tool - AX ONLY Post Process.sql”. 

Now, let’s go into detail on each step.       

1. Analysis, compression, and estimation of compression times in testing environment. 

     Defining parameters in the script before execution.

The first step is to open the 1.1 Dynamics SQL Data Compression Tool – Data Collection.sql script via SSMS in a Dynamics AX 2012 Test environment where before execution you must define the compression parameters as described below:

@COMPRESSION_TYPE: indicates the type of compression to apply. It can be Page ‘PAGE’ or ‘ROW’. PAGE is the recommended option most of the time.

@COMPRESS_NOW: Indicates whether compression is performed during script execution at the same time and not just compression script generation. With the value 'Y' the compression of the tables/indexes would be executed in the current test environment and in turn would generate the compression script that later will be executed in the production environment. This option allows you to calculate the compression time in seconds to be considered when running in production.  A value of 'N' would indicate that compression is not performed in the execution and that it only generates the compression script for later execution in production.

@KEEPCOMPRESSION: indicates whether you want the current compression of tables/indexes to be maintained.

@MIN_ROWCOUNT: indicates the number of minimum rows that a table/index must have to be considered by compression.

    Execute the script 1.1 Dynamics SQL Data Compression Tool – Data Collection.sql  in a Test Environment. 

After the parameters are defined in the script, Execute (F5) the script 1.1 Dynamics SQL Data Compression Tool – Data Collection.sql from SSMS against the MicrosoftDynamicsAX database in the Test environment. 

This script will create a table called Compression_Analysis in the MicrosoftDynamicsAX DB, which will contain the analysis of the tables/indexes to be compressed, the compression time used (In this test environment, that will serve to estimate execution time in production environment) and the SQL script to be executed as the final compression in the Production environment. 

This information will allow you to plan the tables/indexes and time that it would take to apply compression in Production environment based on the maintenance windows available for its execution.

Note: The execution time of this script depends on the @COMPRESS_NOW parameter, whether it is defined to compress the tables/indexes. For example, a 600GB AX DB on a SQL server with processor for 2.40 GHz , 4  Cores and 180 GB of RAM took about 3 hours. If compression is not activated, the execution can take about 10 mins, only that compression times per table/index are not calculated. 

The result of running this script is seen in the following image, which is the selection of records in the table Compression_Analysis, and which the first column UPDATESCRIPT contains the compression SQL script for each table/index (to run in production), the column Compression_time_secs with the time used to perform the compression in this Test environment, plus other additional information.

Something interesting about this, is that once the script execution is complete, the information is stored in the Compression_Analysis table, and can be consulted and used as many times as necessary, to filter and/or sort as required. It will help you to plan the tables/indexes to be compressed in the Production environment later, based on the maintenance windows given and/or according to the criteria that best suit your installation. The column Compression_time_sec gives you the time required to compress the table/index in this test environment which will be a guide to plan the compression in Production, which could be lower due to usually Prod environment has more resources. 

This last SELECT we see in the query window above over the Compression_Analysis table, can be executed independently as many times as necessary and customized according to the criteria described above that you would like to analyze.

 2. Running compression in Production environment with SQL script Results from Test environment 

     Copying the SQL script Result from SSMS Test environment to the SSMS Production environment.

With the compression result in the test environment from the table Compression_Analysis, we will select the content of the UPDATESCRIPT column for all, of the indexes/tables that we will compress in the given maintenance window and copy it.

Next, paste the UPDATEDSCRIPT column information on a New Query window in SSMS of the Production environment. There we will confirm the Tables/indexes to be used and compressed, as shown as an example in the following image.

     Execute compression in Production environment.

Once the compression script with the tables/indexes to be compressed has been copied and pasted to the SSMS Production environment, it must be executed to run SQL compression in the AX Production DB.

Once the script is executed, the indexes included will be compressed at the SQL level and can be verified in SSMS, however at the Dynamics AX level it is still required to update the AX SQLStorage Table with the compression performed. This process will be performed in the next and last step.

3. Running Script “2 Dynamics SQL Data Compression Tool - AX ONLY Post Process.sql” post-compression in Production environment.

The last step after each time you run compression is required to execute the “1.2 Dynamics SQL Data Compression Tool - AX ONLY Post Process.sql” script in your SSMS AX Prod Database as you can see in the following image. This will synchronize compression information already performed at the SQL level to the Dynamics AX SQLStorage Table level.

 Please have in mind the following steps before executing the script: 

-  DynamicsPerf 2.0 must be installed and xpo Class should be run before executing the script “1.2 Dynamics SQL Data Compression Tool - AX ONLY Post Process.sql”. 

-  There are several execution options, depending on where the DynamicsPerf DB and the Dynamics AX Production DB are installed, choose the best suit you. 

Option 1: If the DynamicsPerf DB resides on the same production AX DB SQL instance, it is the simplest option, because the two databases are on the same instance, the script will run on that instance simply. 

Option 2: If the DynamicsPerf DB resides in a different instance than the Production DB, the script must run from the instance where the DynamicsPerf DB is and enter the Dynamics AX PROD DB using a Linkedserver. 

Option 3: If the DynamicsPerf DB resides in a different instance than the Production DB, run the AOT export class process to this database. Next move this DynamicsPerf DB to the Production SQL instance temporarily where the Dynamics AX DB is located, and then run this 1.2 script. After execution and compression, delete the DynamicsPerf DB in this instance.

This is the final step of the compression process.

I hope this explanation has helped you with the compression activities of your Dynamics AX database!

If you have a Premier/Unified Support contract, we can help!!  Just ask your CSAM for a Dynamics 365 Finance and Operations Performance Review and we’ll have one of our performance specialist work with you to resolve your performance issues.

Visit Dynamics 365 Service Offerings for Dynamics 365 Finance and Operations (AX) to find out more about our Performance  Reviews and Hands-on-labs, available for AX 2012 and a Dynamics 365 Finance and Operations!

https://community.dynamics.com/ax/b/axinthefield/posts/dynamics-365-service-offerings

 Acknowledgments

I want to thank my colleagues Rod "Hotrod" Hansen and Amy Flowers (Customer Engineers) for their contributions and peer review.