Starting an AX implementation out with incorrect SQL Settings is a sure fire way to ensure that you end up making healthy donations to the local psychiatrist for stress relief. SQL is always the first place I look when people tell me things are going slow. However, what I often find is that much of the guidance out there is in need of a ‘Dynamics AX’ flavor so to speak. Good concepts, but AX has it’s own requirements. Here I share one of my own SQL checklists for righting your SQL server Dynamics AX ship with much of the knowledge gained through the hard lessons of experience.

1. Enable Trace Flag T1117
   

   If there are problems with TembDb, you will always see performance problems within AX. Dynamics AX relies heavily on the TempDb for generating the complicated logic for reporting and everything else. What this does is ensure that your TempDb files all stay the same size if Autogrow is configured. Best practice is for all files to be the same size for performance. This is absolutely critical.

2. Enable Trace Flag T1204, T1222 along with T3605

   This is one you will thank yourself for later. Basically, at some point in an implementation, you will have to deal with locking problems if you have enough code. Knowing when they happen and the resources involved is essential. It will often correspond with application crashes and slowdowns. Both T1204 and T1222 are essential in seeing what caused the deadlock. T3605 enables that information to be written to the SQL server error log so that you can see it later.

3. Always leave at least 10% of Maximum Memory free for Other Processes
   

   Lots of feedback on RAM. I typically go by a simple rule of thumb which isn’t a best practice or worst practice where I size out initial RAM by having the total amount of RAM at least equal the size of the model and transactional databases combined. One rule that doesn’t change however is how much of the operating system RAM SQL can occupy. At the very least, you need to leave 10% free. So, be certain to set max_physical_memory to 90% of the total memory.

4. Set Maximum Degree of Parallelism to 1 for Prod
   

   This is a big deal. Microsoft says that the only exceptions should be either if you are running heavy batches – which even in that cases, this should never exceed 4. Or if you are doing a database upgrade. Otherwise, you will eventually run into slowdowns and parallelism issues if this isn’t set correctly.

5. Turn on Lock Pages In Memory and Enable it for SQL Server
   

   This is critical for Prod and SQL Server instances running on VM’s. What it says is that SQL can manage it’s own memory more effectively than another process, such as the OS. There is a lot here on why you may not want to do this in Test environments to make the memory spread across VM’s but everybody agrees that this should be turned on in a Production Scenario ERP type database like Dynamics AX.

6. Turn on Instant File Initialization
   

   If you have autogrow enabled, this will speed up the file growing process tremendously for everything but log files. Details are explained all over but basically by cutting down on a zeroing operation, it saves SQL server an expensive step in growing a disk.

7. Turn on the Blocked Process Report Extended Events and set the threshold equal to 5
   

   Absolutely critical to set this. When processes are blocked for over 5 seconds, Microsoft warrants that this should be investigated. Whether it is normal or not is dependent upon your implementation and what is going on, but 5 seconds is the baseline. Without this beautiful report, it can become very hard to troubleshoot what is being blocked.

8. Set the Query timeout equal to 600 seconds
   

   As a baseline, individual queries should not be taking more than 10 minutes to run. Even the big reports often involve 10 or 20 queries in AX. When queries are taking longer to run it is often symptomatic of poorly run code or something else and you’ll start to see plenty of problems on SQL.

9. Heavily Consider Enabling SQL Trace Flag 610
   

   For AX shops that are seeing slow insert performance due to indexing or over indexing, this thing can be like a miracle worker. Though it is supposed to be a log size reducing tool, the performance effects are well documented. As we all know, AX write performance can affect read operations because the reads are often dependent on generated tables in TempDb. This flag can significantly speed things up, especially if you have an Excel Friendly data loading shop with Atlas or the Excel Add-in or some tool that is not inserting 10 million records at a time.

10. Set Fill Factor at 85, override as needed for individual tables
    

    Microsoft recommends a fillfactor of 85 to 95 for Dynamics AX. This is important as the degree of free space will significantly impact the speed of your read operations along with the DML operations. What I’ve found is that I typically set a fillfactor of 85 as the server default. This is to minimize page splitting in the TempDb. Then, I override the settings with an Index script after code moves which sets the fillfactors to appropriate values for the optimized tables. If you want to start simple, you could set every AX transactional database fillfactor at 90, every modelstore fillfactor at 95, but leave TempDb alone making it 85.

    BONUS SETTING but not included in the TOP 10 to Enable ConnectionContext in Registry on AOS
    

    Do Enable the ability to see the user behind the AX query. This one is interesting. It’s actually a slight hit on performance. However, the benefits of this slight hit are more than outweighed by it’s advantages. At the very end of the day you need the ability to go ask someone which form they were visiting when the problematic circumstance happened or what they were doing and correlate it with real data. Plenty of google on this but basically you just need to go to each AOS, navigate to “HKEY_LOCAL_MACHINESYSTEMCurrentControlSetservicesDynamics Server6.0<instance><configuration>” and create a key called “connectioncontext” and set it to 1. Then, Restart the AOS.