Manufacturing Execution and SQL 2014 Statistics

(0) Share

Report

Posted on by lispyj

181

We are about two months into our AX2012 R3 rollout, and we experience some severe slow downs on the Job Registration form in Manufacturing Execution. After some admitted non-SQL expert evaluation, I can find no resource issues, no blocks, no red flags on either the SQL 2014 database or the AOS. We have a twice daily Update Statistics job that runs (before and after our MRP runs) to keep the queries in order. As a desperate measure, during one of our shop floor slow downs, I ran the update statistics job. After the update, the performance dramatically improved. I have repeated this behavior several times now. So, given that updating the statistics is the cure, what can I do to stop the slow downs from occurring?

SQL2014, MOP 1, Autocreate statistics=True, AutoUpdate statistics=True, Trace Flags, 1224, 2371, 4199

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (0)

Suggested answer

Denis Macchinetti 16,444 on at

Like (0)

Report

Hi

Setup DynamicsPerf and check the long queries, index missing, locks, blocking,etc.

blogs.msdn.com/.../dynamicsperf-2-0-deployment-guide.aspx

You can also setup the Windows Performance Monitor in order to log informations about disk, processor, memory and network related Db Server, AOSs, etc.

blogs.msdn.com/.../setting-up-windows-performance-monitor-templates.aspx

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

I recently started running an SQL job to UPDATE STATISTICS on key Master planning tables every 5 minutes throughout the entire 3-4 hours of batch processing (not WITH FULLSCAN obviously). The job only takes 5-15 seconds to run, and it has dramatically helped smooth out performance. May not be a bad idea to mix in a DBCC FREEPROCCACHE.

Was this reply helpful? Yes No
lispyj 181 on at

Like (0)

Report

I read your blog on this topic. My experience with MRP in AX2009 is what led me to the Update Statistics last ditch try. It was absolutely necessary to keep any consistent run times for planning. I haven't figured out exactly which tables are the problem yet, but it shouldn't be to difficult. It just seems like that is a bandaid to coverup something that isn't set up correctly. But I suppose if it works, I won't complain. I do see that there are a number of updates for ME in CU10, so I am going to investigate those.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Updating statistics will cause plan recompilations. Therefore, is it a cached plan issue or a statistic issue ?

Next time, run DBCC FREEPROCCACHE only and see if you get better performance.

Was this reply helpful? Yes No
lispyj 181 on at

Like (0)

Report

Thanks! I will give that a try.

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

You didn't say so in your original post, but are you using Partition and DataAreaId literals since you didn't put 4136 in your list of trace flags?

Was this reply helpful? Yes No
lispyj 181 on at

Like (0)

Report

No! You might be onto something! As I read the information on trace flags, I did not enable 4136 because it didn't seem to apply. We only have one company and one partition. I will give it a try, though, because we certainly are showing the signs of parameter sniffing. Hopefully, that will make a difference.

Thanks for the input.

LJ

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

I wouldn't recommend 4136 anymore. Literals really solved the problem for the most part, and the value there is small with only one company. The nice thing is that you can try it in real time and turn it off if it doesn't work without restarting any servers or services, so there's no harm in trying it. Let us know how it works.

Statistics really does seem to be the solution in these cases. After the dramatic improvement it had on master planning in my environment (I'll be posting a follow-up to my original blog post soon), I've gone looking for other cases where it might be helpful. It's a little sad that SQL Server can't keep up with its statistics better than it does, but I understand your sentiment that it's a a baidaid where something more fundamental is probably wrong, but I disagree. Statistics are the meat and potatoes of query plan compilation. It's not just a bandaid to a deeper problem, it's the real problem in my opinion.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

@lispyj From my understanding, I can't see how enabling DATAAREAID and PARTITIONID literal could help in your case since your have only one company and one partition.

Read this : http://blogs.msdn.com/b/axinthefield/archive/2014/01/09/sql-server-parameter-sniffing-with-dynamics-ax-just-plain-evil.aspx

@Brandon The article above explicitly says that all AOS servers must be restarted after applying DATAAREAIDLITERAL. it makes sense since the AOS reads SYSGLOBALPARAMETERS only when it starts.

I agree that trace flag 4136 is not recommended.

Was this reply helpful? Yes No
lispyj 181 on at

Like (0)

Report

One last update on this one....I haven't really found a full solution. the DBCC FREEPROCCACHE did not make any difference. Updating Statistics makes a difference about 1/2 of the time. I did find a missing index on the DocuRef that made a difference. It seemed like a really odd table to be involved in this, but it did help. Overall, the performance is very, very inconsistent. I do have the Dynamics Performance analyzer running, but nothing is jumping out as a problem.

LJ

Was this reply helpful? Yes No