We are on AX 2012 R1
Short Summary of Issue:
After around a week of running after a SQL service restart, the database stops using memory caches to execute queries, and starts depending on file IO.
This makes every query in the system take 20 times longer to run, and makes the system unusable for employees.
Current Resolutions:
When we restart the SQL server service, the system works for 1 to 2 weeks, and then we need to restart again.
We also have attempted to utilize the following scripts to clear cached memory, with varied, and temporary results.
DBCC FREEPROCCACHE (This buys us an extra 1-2 days at most, but we can run it several times before it stops fixing the issue)
DBCC FREEPROCCACHE('default') (Same as above)
DBCC FREESYSTEMCACHE('SQL Plans') (Does not seem to help, or actually clear any caches)
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL (Does not seem to help, or actually clear any caches)
Use TP_AX2012;
Declare @dbid int = db_ID()
DBCC FLUSHPROCINDB (@dbId) (Did not see any improvement)
DBCC FREESESSIONCACHE (This seems to make the situation worse)
