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.)
- 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)
References
- https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/
- https://social.technet.microsoft.com/wiki/contents/articles/25473.sql-server-dmv-to-track-page-split.aspx
- http://www.jasonstrate.com/2010/10/index-black-ops-part-6-fill-factor-vs-page-splits/
(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)
2 responses to “Dynamics Ax 2012 fill factor”
Hi Kevin,
I’m really impressed, finally another person that works with AX, that tries to see the database as something more than just a magical container for data.
Even for NON-AX databases, fill factor and stuff like that is crucial for getting the right amount of performance out of the system. I believe that ordinary DBA has a rough time handling the AX platform because so few Dynamics AX developers fully understands the concepts that are in play within the SQL Server. So whenever the DBA suggests something, the business normally calls in the ERP partner and makes them qualify the suggestions.
Have you considered sharing the solution on github and making it a community thing? Then we can all assist in making the solution better and hopefully learn something new on the way.
Your last comment made me wonder – I believe that you try state:
“Hence setting a custom fill factor on a recid index is probably not such a good idea)”
You have to keep in mind that all indexes (almost) start with dataAreaId (the company id)
This means a recId will not be added to the end of the index… almost never.
I do agree that a fill factor of 95 is a good starting point – but with transaction tables you may have to drop it a bit lower.
If you have tables shared across all companies (Without Dataareaid) you can set it fill factor to 100.
I find it odd that Microsoft have not made the decision for us. But we have it now and it runs very well