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.)
- 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)
- Process: This service process our setup to the standard Ax (kernel) tables.
- 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)
(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)