AX Issue: Application very slow. SQL server not releasing memory

There can be an issue (usually in lower environments) where the AX application can almost come to a screeching halt. Emails fly about not being able to post SOs/POs, GL issues, the whole works. The fingers fly to deployments and new changes to the environments when looking at the individual issues but its important to see if the issues being sent out are for all users and are applying to all processes and not just deployment issues.

The first place I would look is to make sure the AOS is not hitting the 'rails' (aka maxing out the server). If there is more than 15% of the physical memory free, I would look at the SQL server for the block. Odds are it might look like Figure 1 below (if using 8GB SQL box space).

As I understand it, SQL will dynamically cache memory and not release it until it hits a certain memory limit. Once it hits these limits, it will start releasing memory. Out of the box, the memory is set to be unlimited. I'm sure there are reasons for this but I don't know them. Please share below if you can add to this topic. All I know is that this issue was causing an issue in an implementation and this solution fixed it.

To help resolve the issue, You can right click the DB of interest, click properties, select memory, and finally select an amount of memory to use.  When adjusting this maximum server memory, the actual consumed RAM will be a little higher. For example, adjusting to 6GB (6000 MB) will actually consume 6.4GB. If the value were 5500 (5.5GB), the actual memory consumed for the sqlservr.exe would be 5.9 GB. SQL must have enough memory to cache the appropriate number of objects as well as be small enough for the server to still have some RAM to do its thing as well. Its a balancing act so don't be afraid to play with these settings.

NOTE: In order to change these settings, your user must have the appropriate permissions. In my experience, the user must have either the Sys Admin or Security Admin roles added to allow the changes above.

 Figure 1 - SQL without a max cap

Figure 2 - Changing the Maximum server memory setting in SQL

 

Figure 3 - The sever sqlserv.exe size from Figure 1 after the change from Figure 2