Lately wo do have many questions related to this subject:
- What are the recommendations for NAV to work on Parallel execution Plans in SQL?
- Right DOP Settings and parallelism in NAV DBs.
To answer this, we decided to right this post:
First, we need to understand the parallel Execution Plans Properly in order to give you an answer if this is recommended for NAV from our side or not.
Main question, how the parallelism work first:
Imagine for a moment that SQL Server has no built-in support for parallelism. You might try to improve the performance of the original row-counting query by manually splitting the query into equally sized pieces, and running each one concurrently on a separate connection to the server.
Figure 3: Manual Parallelism
Each query in Figure 3 is written to process a separate range of rows from the table, ensuring that every row from the table is processed exactly once overall. With luck, SQL Server would run each query on a separate processing unit, and you could expect to receive the three partial results in roughly a third of the time. Naturally, you would still need to perform the extra step of adding the three values together to get a correct final result.
This brings us to Parallel Execution as Multiple Serial Plans:
The ‘manual parallelism’ example is not that far removed from the way SQL Server actually implements its parallel query facility. Recall the parallel query plan from Figure 2, and assume that SQL Server allocates three additional workers to the query at runtime. Conceptually, we can redraw the parallel plan to show SQL Server running three serial plans concurrently (this representation is not strictly accurate, but we will correct that shortly).
Figure 4: Multiple Serial Plans
Each additional worker is assigned to one of the three plan branches that feed into the Gather Streams operator. Notice that only the Gather Streams operator retains the little yellow parallelism icon; it is now the only operator that interacts with multiple workers. This general strategy suits SQL Server for two main reasons. Firstly, all the SQL Server code necessary to execute serial plans already exists, and has been optimized over many years and product releases. Secondly, this method scales extremely well: if more workers are available at runtime, SQL Server can easily add extra plan branches to split the work more ways.
The number of extra workers SQL Server assigns to each parallel plan region at runtime is known as the degree of parallelism (often abbreviated to DOP). SQL Server chooses the DOP just before the query starts executing, and it can change between executions without requiring a plan recompilation. The maximum DOP for each parallel region is determined by the number of logical processing units visible to SQL Server.
To jump more concretely to the Parallel execution plan itself, Parallel execution plans require additional operations that in some cases the additional overhead negates the benefit of a parallel execution plan, but this needs to be determined from actual tests. It has not been determined what the time unit the plan cost represents. A reasonable guess would be that the plan cost is in seconds on a processor that was prevalent at the time SQL Server 7.0 was in development. This could anything from a Pentium 100MHz to a Pentium Pro at 200MHz or way more with the continuous Hardware development we see every day. This would make sense in that the actual run time was on average 20 times faster on the Xeon 2.4GHz system than represented by the plan cost if the unit of measure was time in seconds
But on the other Hand, we cannot neglect the effect of the DOP / MAXDOP setting on it:
When the DOP is 2, it appears that the operating system or SQL Server knows to use the two different physical processors rather than both logical processors on a single physical processor. It is possible this is achieved entirely by the sequencing of physical and logical processors. At DOP 4, there are only 2 physical processors, so this is not a test of the ability of SQL Server parallel execution to scale to 4 physical processors, but rather a test of the benefit of HT in parallel execution plans.
The blue font in the DOP 2 column shows cases where the query execution time speedup was more than 30%. In all, 7 of the 18 queries with parallel plan showed better than 30% speed-up, while 4 were slower. The sum total execution time for all 18 queries with parallel execution plans was 29% faster than the same 18 queries without parallel execution plans. The queries that were slower were not predicted to be slower by the execution plan, and the query predicted to be slower was in fact faster with parallel execution. It does appear that SQL Server significantly speeds up the parallel execution of table and index scans even though this is not reflected in the plan cost estimate.
In all but one query, CPU utilization was higher with the parallel plan compared to the non-parallel plan. For the 18 queries with parallel plans, the overall average increase in CPU utilization was 52%. For this reason, parallel execution plans are not recommended for applications where very high through-put is desired, typically OLTP application. Parallel plans are very valuable when speed is desired for a single or few users, typically maintenance operations and DSS applications.
Before choosing to execute the query using serial or a parallel plan, the SQL Server Database Engine will check if the SQL Server instance is running on a server that has more than one processor. The Maximum Degree of Parallelism (MAXDOP) is configured to allow parallel plans where a MAXDOP with a value equal to 1 means that a serial plan will always be used to execute the query. If the MAXDOP setting is greater than 1 or 0 (if 0 all processors will be used) and the cost of the query exceeds the Cost Threshold of Parallelism value and the cost of the parallel plan is less than the cost of a serial plan, then a parallel plan will be created and used. If the decision is taken to use a parallel plan, the SQL Server Engine detects the number of CPUs required to execute the query, called the Degree Of Parallelism (DOP), and distributes the tasks among these threads in order to execute the query.
What value of MAXDOP is the one for NAV Then?
Since NAV Started to work on SQL Server ever we have been so clear on this, please have a look here:
https://blogs.msdn.microsoft.com/nav/2010/09/28/microsoft-dynamics-navsql-server-configuration-recommendations/
We always recommended MAXDOP on 1, and never any other value, and we will never recommend any other value.
In some specific cases we saw that other values can give better performance but this is not even 5% of what we always monitored and saw, hence from support till program management we are always recommending MAXDOP of 1.
Why?
"Max Degree of Parallelism"
Microsoft Dynamics NAV communicates to SQL Server with client side cursors via ODBC. The C/AL is converted to simple TSQL statements via the NDBCS.DLL. These statements are very simple Select, Update, Insert, and Delete SQL statements and do not require parallel execution in SQL Server. This does not stop the compiler from sometimes attempting to use parallel execution when planning queries and this can sometimes negatively impact performance. This is why we recommend setting the - Max degree of Parallelism in SQL Server to 1 to prevent this.
Simply most OLTP systems like NAV that run under a single thread react always better on maxdop 1.
And for this, and to get the best out of this, we suggest parallel to this to run all services under dedicated service accounts to get the best out of the whole setup, why?
First, all services do cache lots of information on temporarily files on disk, this is always to be saved under the specific user folder on the system disk.
Means if all services use the same account, then imagine if more than one service want to access the temp. files at a time, what happens? It queues because this path is accessible also on a single thread base.
If each service has its own account, it runs parallel. Further on while communicating with SQL Server (Here comes also the MAXDOP) we all know that since NAV 2013 we do no longer do the classic delegation, rather we do application pooling, and in this case the result of the pooling will be that SQL Server will get a working thread to be attached to the SPID under the login representing the service account.
If you use the same login / Service account, specifically while SQL Server is on MXDOP one then the chance that this process and similar other processes might queue up, or wait for each other will grow bigger, and in some worse scenarios even cause longer locking than desired.
Hence our guidelines since we ever worked on SQL Server up from NAV 5 was clear about MAXDOP 1 and the best practice and different sizing guidelines and intensive tests showed us that it performs the best while each service is running under dedicated service account.
Further on, if the customer has fears of drawbacks in performance, we never ask to run the recommendation on the production, you are kindly requested to have a test environment very similar to the PROD from the customer to test on it, and then see if our recommendation brings the best out of the system or not.
The main conclusion of all this is: no we recommend MAXDOP to have always the values 1 (combined with more tuning from NAV service point of view, like running each single service under dedicated service account…etc), which means parallel execution Plans would be anyway nothing we recommend for NAV as a Typical OLTP System.
References:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/
http://www.sql-server-performance.com/2005/parallel-execution-plans/5/
https://www.mssqltips.com/sqlservertip/4939/how-to-force-a-parallel-execution-plan-in-sql-server-2016/
https://blogs.msdn.microsoft.com/nav/2010/09/28/microsoft-dynamics-navsql-server-configuration-recommendations/
