I have been onsite at a lot of Customers who believe that Index Fragmentation is major influence on overall Performance for Dynamics AX. I actually have a hard time convincing them that its impact is relatively low on overall performance. They also raise the issue that they can no longer fit their re-index job into their nightly maintenance window. I then try to explain to them that they do not need to re-index nightly or even weekly in most cases, because Dynamics AX mostly does singleton lookups where fragmentation is not a factor, and it is only really a factor in Range scan activities which AX does not do much of especially outside of reporting. It can be difficult to convince DBA’s of this because of all the information available on the web from “Experts” and I use that term loosely that Index Fragmentation is the scourge of Relational Databases and must be addressed aggressively.
In the example below I will attempt to show that the overall impact of Index Fragmentation on Dynamics AX performance is greatly exaggerated.
Looking at a DynamicsPerf database for one of the larger Dynamics AX 2012 customers by shear transaction volume and looked at their index operation stats. I sliced and diced some numbers and came up with a great example as to why Index Fragmentation is not something that needs to be addressed so aggressively for the Dynamics AX database. I added up ALL the Range Scans and All the Singleton Lookups and here is what I got.
158 Billion Range Scans (OLAP workload)
598 Billion Singleton Lookups (OLTP workload)
That is 4 to 1 in favor of singleton look-ups (which are not effected by fragmentation as it is just looking for a single row). That means almost 80% of what AX “does” is not really affected by fragmentation. Now I am not saying that re-indexing should not be done, I am just saying its impact on performance in AX has been GREATLY exaggerated. Also that index rebuild do not need to be done nightly or even weekly for that matter. Weekly is about as aggressive as we would recommend. That being said there is the possibility of fringe cases where a FEW indexes on very specific tables may need to be rebuilt more frequently, but that is a topic for another Blog post.
If you have witnessed performance getting a lot better after re-indexing you need to ask yourself was it really the re-indexing that did it or the dumping and rebuilding of the plans after the indexes were rebuilt? More than likely the problem is bad plans do to several possible issues most likely parameter sniffing. Next time before you attempt to address a performance issue try rebuilding Statistics first or even running DBCC FREEPROCCACHE and see if the performance increases, if it does it was not an index fragmentation issue. Statistics play a FAR more significant role in overall performance and should be addressed aggressively with nightly rebuilds and the use of SQL Server Trace Flag 2371.