DB Server Slow down in AX

Asif Naeem
Asif Naeem used Ask the Experts™
on
Dear Experts,

We have DB server (SQL Server 2008 R2) running Dynamic AX. The server is hosted on VM ware having 64 GB RAM and etc. But for some reason once or twice in every day we are experiencing Slowdown or AX which cause serious problem for logistic department to ship the order.
We have tried to figure out why it is happening but could not found any solid reason.
As this happen we run the following command which speedup the AX
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Any help how we can fix this problem happening permanently.

Regards
Asif
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Can you confirm patch level of SQL and which version of AX and also patch level please

Commented:
This is either QueryPlan cache or ToeknAndPermUserStore cache built.  Has been known to make even the simple quick query into a performance nightmare.
Asif NaeemSr. System Administrator ( Wintel & UNIX (AIX)

Author

Commented:
Hi,

SQL server 2008 R2 and AX 2012 if this help.

Regards
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
This is QueryPlan cache or ToeknAndPermUserStore cache built.

Need a maintenance script running regularily to keep this in check without flushing the cache unless neccesary.
Asif NaeemSr. System Administrator ( Wintel & UNIX (AIX)

Author

Commented:
Hi Damon,

Please can I have nay KB of bit Explanation of this so I can understand in depth about this.

Regards
Commented:
We have a doc on it, what's your email address?
Asif NaeemSr. System Administrator ( Wintel & UNIX (AIX)

Author

Commented:
Hi,

please email me on

ramay_aku@hotmail.com

Thanks
Asif NaeemSr. System Administrator ( Wintel & UNIX (AIX)

Author

Commented:
Hi Damon,

Please looking forward document from ur side on my email address.

Ramay_aku@Hotmail.com

Thanks
David ToddSenior Database Administrator

Commented:
Hi,

Another setting that pays to change from the default with almost no downside is the optimise for ad hoc queries.

https://msdn.microsoft.com/en-us/library/cc645587.aspx

HTH
  David

Commented:
how did you get on?
Asif NaeemSr. System Administrator ( Wintel & UNIX (AIX)

Author

Commented:
The documnet provided is good enough to do the Job

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial