MAXDOP and AOS Batch Threads question...

(0) Share

Report

Posted on by Kazinova

1,500

So I'm having some discussions with fellow AX buddies and the question has come up about parallel query execution. According to Technet and MSDN, the best setting for MAXDOP is 1. "However, the upgrade process benefits from parallelism, as do activities that are used exclusively for batch jobs or maintenance." In that case when batch you want to increase MAXDOP from 1... With that being said, If I have a batch job (MRP for example, because its a favorite performance topic), that is set to run on a batch (AOS) server configured for 8 threads, will the AOS then execute parallel querying where we have SQL set with a MAXDOP set at a value of "1"? From my research I feel like I'm reading conflicting information. Dynamics in the Field has topic where they talk about it, but it still doesn't seem clear, particularly because I'm not running an OLAP app at the same place as the SQL Instance. What I really want to know is, setting MAXDOP to a higher value (CPU #) and clearing SQL plan cache for batch jobs at 2am a good idea? Or will there be conflicts like deadlocks, waits, etc...And does the AOS running multiple threads actually execute parallel queries, even though MAXDOP is set to 1.

Now I just hope that Martin doesn't take a jab at me for asking a question that is very common ;)

Regards,

Zac

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Verified answer

Brandon Wiese 17,788 on at

Like (1)

Report

This is something that I've been paying a lot of attention to in my environment over the last few months.

Master planning as a whole generates a lot of queries that impact a single record. Very few of the queries executed by MRP benefit from MAXDOP of more than 1. What I've found most recently is that doing an update statistics (not with fullscan obviously) on your REQ tables literately every 5 minutes solves most of the query plan issues, way, way better than a dbcc freeproccache does.

Having said that, my SQL Server has 16 logical processors, and I leave my MAXDOP between 4 and 8. I have some direct SQL reporting outside of AX that benefits from this configuration. I have some specific SQL queries where I've specified the MAXDOP as a query hint to exceed the server configuration.

I agree that MRP is a favorite performance topic. I've found it to be a bellweather indicator for deeper performance issues. For example, I have an AOS NLB cluster that was setup mistakenly as unicast which creates a lot of port flooding, and as we added more virtual machines to the Hyper-V cluster (and thus more ports to the virtual switch) we eventually crossed a threshold where Hyper-V started dropping packets within the virtual switch. When this happened, MRP started taking twice as long to run (literately overnight), and I knew immediately that something was wrong, somewhere, Needless to say, we didn't figure out something that subtle right away, and it got much worse and performance in other areas collapsed before the root cause was identified and corrected.

Another good performance indicator is an AXBUILD compile with lots of threads, which generates a sheer ton of network and query activity in a very short time. Simple things like using an IP address in your configuration instead of a DNS name have a surprising effect on compile performance. It's another one of those areas where if anything is wrong you can "feel" it very early and easily.

Was this reply helpful? Yes No
Kazinova 1,500 on at

Like (0)

Report

Thanks Brandon, I am assuming you closely monitored the execution plan(s), so you update statistics on REQ tables... including PurchReqLine, etc? Are you using SQL profiler to trace or just reading the execution plans of queries in activity monitor to determine the tables. I have yet to dive into troubleshooting MRP at a deeper level. For right now I am trying to determine if a simple strategy, such as adjusting MAXDOP, will help with an uptick in performance. I'm curious though, with a 4-8 MAXDOP configuration, are you not seeing performance issues from standard processes in AX? I know this really isn't a factor for ERP system with low user counts, but I can't imagine there wouldn't be issues with a large scale solution and over 1,000 active users. I feel like performance wouldn't be consistent, or the resources would radically change between users.

Aside, I am still wondering, does the AOS running multiple threads actually execute parallel queries (using multiple CPUs), even though MAXDOP is set to 1? And if setting MAXDOP to 8, will an uptick occur when executing parallel queries with several CPUs for a multi-helper batch job...where otherwise it would be a serial execution with all the helpers.

What about setting cost threshold for parallelism and/or adding an OPTION (MAXDOP #)?

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

Like (1)

Report

I'm not updating statistics on PurchReqLine, only ReqTrans, ReqPo, etc., the tables that are effectively deleted and rebuilt with every regeneration run of MRP and tend, in my opinion, to suffer from statistics issues precisely because of the purge and rebuild behavior.

I use DMV's to monitor individual query performance, both during a problematic process and over a long period of system operation. I believe there have been blog posts by Microsoft engineers on the effects of MAXDOP on MRP. I'll see if I can find one.

I'm at 300 concurrent users, so I can't speak to 1000 active users. I can say that most of our performance issues stem from the client, i.e. slow forms such as journals and sales/purchase order entry that aren't constrained by SQL performance as much as compile on the fly and poor UX behavior. It's not at all unusual to see instances of ax32.exe pegged at one full CPU core on a terminal server while my SQL Server and AOS machines barely break a sweat a peak usage. We do load some ledger journals with 10k and even 20k lines periodically, and the base code is far from optimized for larger journals, but that's expected I think.

I still remember reading the "a day in the life" AX performance benchmark paper, and stumbling over the footnote at the end where they explain that they didn't use the AX client in the testing matrix, and that making perfect sense to me.

Yes, multiple MRP helpers heaps up the SQL queries significantly, but most of them are single record operations, i.e. select top 1 this and insert that where MAXDOP doesn't lend much. I've never bothered to play with the cost threshold.

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

Like (1)

Report

You've read through this article?

blogs.msdn.com/.../master-planning-performance-best-practices-from-pfe.aspx

By the way, using PARTITION and DATAAREAID literals is a huge boost for multi-company MRP, where quite literately a single cache plan could never optimally service many companies with varying data distributions. I'm happy to see trace flag 4136 finally go away as a poor solution to a real problem.

Was this reply helpful? Yes No
Kazinova 1,500 on at

Like (0)

Report

Thanks Brandon, I have read the article, and as you can see in point 6, it brings up MAXDOP and increasing it. The details are what I mentioned previously, that you would need to clear the plan cache. Unfortunately, It doesn't give any more detail about MAXDOP and AOS threads, except that AOS threads are consumed by helpers. It's just too vague for me, and if you remove MRP as an example, I'm still left with a bunch of questions for how threading and parallel querying work with AX Batch processes. I appreciate your responses, still a lot of good information, but it looks like I am going to have to really just test this out myself and see the results.

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

Like (1)

Report

I guess I don't understand the question.

Batch threading controls is an AOS setting, allowing you to control the size of a pool of concurrent batch operations. MRP is special here only because it fires up helpers that are batch operations in their own right. Not all tasks lend themselves to being parallel. There's no "helper" option to posting a ledger journal, for example. So turning the dial up on batch threads means, outside of special cases, that you can run more of them, but it won't make them run faster on their own. And, of course, some processes can get in each other's way, like posting two larger ledger journals at the same time can creating blocking no the table.

MAXDOP is an SQL Server setting. It lets SQL Server spread the work across multiple cores. Not all operations lend themselves to being parallel here either. I think it was fairly recently that SQL Server started doing inserts in parallel, but many other operations like selects, joins, indexing, etc., have all been optimized for parallel operation. So MAXDOP is a case where if you turn the dial up, you can actually get more done, or get the same work done faster.

In my case my SQL Server and AOS are on different boxes. So the dials are fully independent in my view. As long as turning up the batch threads doesn't saturate SQL Server, or the AOS CPU pool, or create locking opportunities, it's safe to do.

The issue I have with your question is that it depends almost entirely on the batch process in question and the types of queries that it runs. If you remove MRP and leave the question open to all other possible batch processes, the discussion reduces to generalizations because all specifics have been removed..

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am still wondering, does the AOS running multiple threads actually execute parallel queries (using multiple CPUs), even though MAXDOP is set to 1?

Yes, SQL can execute multiple queries at the same time if you have more than one core.

However, one query can not run on multiple cores at the same time if MAXDOP is set to one.

Does that make sense ?

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

Like (0)

Report

I still think he's asking whether batch tasks are run in parallel simply because there are more threads available. In the case of MRP, if you ask for more helpers, and there are more threads to service them, then yes. For almost all other batch processes, if you turn up the number of threads, there's no effect on general batch processes individually. In other words, the batch framework has no idea how to break a problem into parallel pieces by itself.

Was this reply helpful? Yes No
Kazinova 1,500 on at

Like (0)

Report

Perfect, so AOS will not override server settings, sql. Really my thought is, if I assign MAXDOP to "1", which means it can only utilize the number of threads a single CPU provides, then I have a bottleneck where I could actually have an uptick. I want to know if the AOS would control that server setting, and if it can't, are we hurting from not scripting it to change and wiping the cache plan each time... Im not concerned about batch tasks so much, otherwise the general concept of multiple users running process queries is nonexistent.

Was this reply helpful? Yes No
Kazinova 1,500 on at

Like (0)

Report

All good responses guys, thanks. This article really explains what I know about the parallel quering, technet.microsoft.com/.../ms178065(v=sql.105).aspx. I need to test this against batch jobs and see the result, I think its the only real way to know the benefits. Performance is always the biggest concern.

Was this reply helpful? Yes No