Dynamics Ax 2012 History cleanup

On of my most popular posts is Cleaning up the AIF document log, but this is not the only table that could benefit from a regular cleanup. For some of these standard tables there are little or no cleanup jobs, as an example the cleanup for the AIF document log only runs online in the client without any option to schedule it in batch.

I know that a lot of partners and customers use SQL scripts (as I did for the AIF post) to delete this data but there are some things to keep in mind:

  • Pro:
    • Very fast.
    • No new release of models needed.
  • Cons:
    • Deleting a big volume of data might cause more locks and the database log file to expand where the disk might run out of space.
    • All business logic is skipped and new customizations might be ignored. For example: a new delete action causing orphaned data.

Because of these reasons I started thinking about building a simple framework that is easy to extend, can be limited in the amount of data so database transactions and expansion of the log file is limited, and of course can be scheduled in batch.

So here’s the result:

  • Type: This Enum is what makes the stuff easy to extend, the classes that do the processing use the extension framework to execute the correct logic.
  • Number of days: This parameters defines the retention in a number of days.
  • Number of records in transaction: the maximum number of records that will be deleted in one database transaction. If your SQL Server is configured to use lock escalation selecting a big amount of data could cause a table lock which will stop all other processes on the same table.
  • Number of bulks: One transaction is one bulk. This is very useful to not over flood the database logging system. For example: if a database log transaction backup runs every hour you could schedule the cleanup to run hourly for a maximum amount of data. If the backup is finished the log file is freed up again and the cleanup can run once more.

So with this example I already provide 3 of the most used scenarios with standard Ax:

  • Batch history: This job cleans the BatchJobHistory and related (delete actions) tables with the following ranges:
    • CreatedDatetime: Older then the number of days.
    • Status: Ended.
  • AIF logging: This job cleans the AifMessageLog and related (delete actions) tables with the following ranges:
    • CreatedDatetime: Older then the number of days.
    • Status: Processed.
  • Database logging: This job cleans the  SysDataBaseLog table with the following ranges:
    • CreatedDatetime: Older then the number of days.

If you want to extend this with other scripts for new tables all you have to do is this:

  • Add your new type to the BLOGHistoryCleanupType enum.
  • Make a new class that uses the BLOGHistoryCleanupAttribute with this enum value, inherit from BLOGHistoryCleanupProcessorBase and implement the run method.

The source is below, enjoy!


(This tool has only been tested on a Dynamics Ax 2012 R3 CU12 environment, please test this before putting in to a production environment and use at your own risk)

Dynamics Ax 2012 my ideal Azure VM setup

Introduction

This is just a quick note on how I setup an Ax 2012 on an Azure machine to get the most bang for my buck. The example I’m using is a DEV machine where I keep the sample code for this blog. But you could apply the principles for every environment.

Sizing & disks

Since I’m on a limited budget with my Visual Studio enterprise subscription I don’t use premium storage. So my favorites are the D2/D3 and D11 v2 machines, especially the D11 which has more memory, less cores (compared to D2/D3), it can have 4 data disks and the temporary local storage is larger than DS machines. This storage will become very useful for SQL Server and the extra memory is also a must.

Since standard disks are limited to 500 IOPS and 60 MB/s throughput I like to attach multiple disks and spread my installations over these. So I did my installation like this:

  • Disk 1: SQL service + SSMS + LDF files + backup folder.
  • Disk 2: SQL MDF files.
  • Disk 3: Dynamics Ax client and service + visual studio.

(You can even add a 4th disk on this machine size but I didn’t really need it.)

SQL Server

Another neat performance trick is to use the temporary local SSD for the SQL temp db and buffer pool extension file. Check the link below on how to do it, I also have modified their powershell startup script to start my AOS.

Source: https://blogs.technet.microsoft.com/dataplatforminsider/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions/

Automation

I prefer starting and stopping my machine manually but another good way is to use Azure Automation to automatically start and stop your machine when you are not using it. This will save a lot of money on your bill.

https://docs.microsoft.com/en-us/azure/automation/automation-solution-vm-management

If this is too much trouble but you’re afraid that you might forget to shutdown your VM you can also use the auto-shutdown feature. This feature can be found on the menu of every VM.

Dynamics Ax 2012 fill factor

Database synchronize

We all know that Dynamics Ax constructs its tables and indexes during a DB synchronize, and if we would make changes directly in SQL these could be overridden by Ax when installing model updates and syncing those to the database. But there’s more to creating an index then the defaults Ax does and in my experience the fill factor is never specified or the SQL Server default fill factor is used.

Wait what fill factor?!

If this is your first question you might want to read this 🙂 After reading this you should come to the conclusion that on certain tables we might need a proper fill factor to lower IO on the subsystem.

What options do we have?

  • Maintenance: With the standard SQL maintenance jobs or tools like the one from Ola Hallengren we can specify one general fill factor. But setting one value for all tables might be more of a problem then don’t setting it.
  • Scripting: We can script something to change these after a maintenance but execution might be forgotten, indexes might be renamed, …
  • Ax: What if we can setup Ax so it wouldn’t overwrite our settings?

The right way

We can setup a fill factor by going to the System Administrator module -> Periodic -> Database -> SQL Administration. This screen uses a treeview to define some extra SQL instructions like the fill factor per table or per index.

But since this screen uses a treeview with way too much data it’s a nightmare to work in, you cant load excel sheets and all the buttons execute long running processes online on the client tier without any possibility to run it in batch. That’s why I decided to write my own solution for this problem. (Source on the bottom of the post)

This screen is a sort of automated frontend for what the screen with the treeview does and consists out of three steps. (When no indexes are specified for a table the logic will run for all indexes.)

  1. Initialize: This service fills the setup table based on the table group for instance transaction tables could benefit from using this. (When in real life situations I use another method based on SQL DMV’s explained below)
  2. Process: This service process our setup to the standard Ax (kernel) tables.
  3. Reindex: This service reindexes and also sets the fill factor on the indexes we have specified in our setup.

Finding the right fill factor

Other then my first idea to base my setup on the table group I would rather use a better way. If you measure a high amount of page splits, especially during business hours you want to find out which tables cause them.

In the blog posts linked below you can find good tips on how to find indexes that need tuning on this. I usually start with setting the fill factor to 95% and work my way from there. (Make sure these are tables with frequent updates or delete scenarios because when you have a table with only inserts and a clustered index only adding at the back of the index setting a fill factor might not be so useful. Hence setting a fill factor on a recid index is probably not such a good idea)

References


(This tool has only been tested on a Dynamics Ax 2012 R3 CU12 environment, please test this before putting in to a production environment and use at your own risk)

Dynamics Ax 2012 Trace parser with ETW

Hi All,

The following subject already exists for a while but I found it so useful that I want to get it out there once more!

Lets consider the following scenario: There’s a performance issue in production and no other environment around where the customer or the partner is able to reproduce it or the database is just too big. (Yes I know don’t comment on this statement 🙂 ) The last thing you want to do is to put this environment even under more load by installing tools or writing extra code just to log something so maybe this is the solution for you.

When you download Performance Analyzer for Dynamics Ax  it comes with all kinds of SQL goodies but also with 2 Performance Monitor templates (AX_Trace_Detail.xml and AX_Trace_ClientAccessOnly.xml) which you can find in the “DynamicsPerf\Windows Perfmon Scripts” folder. As the filenames suggest these allow you to do tracing for Ax. But wait we already have that in the tracing cockpit of the client? Yes we do but it doesn’t allow you to schedule it which is really handy to investigate a batch process running at night in production and let’s be honest do you trust starting this on an Ax client in a production environment?

So here’s how to set it up:

  • Open the Windows Performance Monitor.
  • Create a new Data Collector Set.
  • Use a template to create the Data Collector Set.
  • Click Browse and pick one of the templates delivered by the Performance Analyzer solution. AX_Trace_Detail.xml or AX_Trace_ClientAccessOnly.xml. This last one is useful for putting on RDP or Citrix servers to reduce the amount of logging.
  • Change the default “%systemdrive%\PerfLogs\Admin\DAX 2012 Trace” path to another drive which has enough space and avoid using the same drive as Dynamics Ax so that when it runs full the application will still run stable.
  • After this you can use the standard functionality to set up schedules, disk usage, copy’s, …
  • Start and stop the tracing and import the .etl file in the Dynamics Ax 2012 Trace Parser.

It’s just that simple and this feature is available on every Windows installation. Just be aware that this generates a high volume of logs so you want to set up the data manager really good. Processing it in the trace parser also might take a long time so I suggest you always to this on another environment than production.

Happy tracing!

MS SQL Server index maintence

Hi,

As we all know index maintenance is important especially on large Dynamics Ax databases, but often I see installations where there are little or no maintenance plans or all kinds of exotic scripts. Therefor I want to show you guys the SQL Server Maintenance Solution by Ola Hallengren, this does not only contain stored procedures for index maintenance but also for database backup and integrity.

Installing it is easy, grab a copy of the installation script and run it. But I would suggest you install it on a new maintenance database and change the following parameters of the install script.

  • USE [master]: Installing it on a separate database maintenance instead of the master makes it easier to uninstall or update.
  • @CreateJobs: I like to set this option to ‘N’ because I don’t want to call the stored procedures directly from the agent but from a T-SQL block inside of a maintenance plan. This looks more consistent so that it doesn’t look like a lack of maintenance plans.
  • The rest of the configuration is quite self-explanatory and personal 😉

At the moment I only use it for index and statistics maintenance so here’s an example on how I like to run it on Dynamics Ax databases.

More information on the parameters of this stored procedure.