Dynamics Ax 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 been developed on Ax 2012 R3 CU12 and has not been tested on other versions.)

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.

MS SQL Server performance with Dynamics Ax

Hi,

Lately I’m working on some projects who experience a bad performance in Dynamics Ax. This can have multiple causes but one of the most important is maintaining the SQL Server. In my humble opinion every Dynamics Ax developer should have a basic knowledge on how SQL Server works and is maintained because it is the backbone of our beloved ERP software. :-).

This said if terms like rebuilding and reorganizing indexes, updating statistics or recovery model don’t ring a bell these links will be very interesting for you ;-).