SQL Server Worker Threads and Microsoft Dynamics NAV


Microsoft Dynamics NAV has been configured to use the Microsoft SQL Server database engine as efficiently as possible. This includes using SQL queries which are appropriate for parallel query execution to speed up the retrieval of data when the SQL Server is configured to allow this. Parallelism in SQL Server can be controlled using the “max degree of parallelism” (aka MAXDOP) configuration option as explained in this article:

https://msdn.microsoft.com/en-us/subscriptions/downloads/ms189094.aspx

If you enable parallel query execution on SQL Server, complex queries executed by the Microsoft Dynamics NAV application may be broken up into component sub-queries by the SQL Server engine. These sub-queries can run in parallel (each utilizing a different processor), and the results are then combined into a single result set before being returned to the Dynamics NAV middle tier service. On an 8 processor machine, for example, 8 small queries running in parallel will return all of the results faster than a single big query running on a single process doing the same task.

If you set the SQL Server MAXDOP configuration option to any value other than ‘1’ on a multiprocessor machine, then you are allowing the SQL Server query optimizer to use a parallel query execution strategy where it calculates that this will provide a significant performance benefit. Note that MAXDOP=0 by default after you install SQL Server.

The kind of SQL Server queries submitted by the Microsoft Dynamics NAV application can often benefit from parallel query execution. In some scenarios with multiple queries split into many parallel query processes there can be hundreds of query threads competing for resources inside the SQL engine. These SQL Server query threads are known as “Worker Threads” and the maximum number allowed is controlled by another configuration option in SQL Server. You can read more about the “Max Worker Threads” configuration option here:

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

Normally, the default value for the “Max Worker Threads” SQL Server configuration option is more than enough to cope with whatever Dynamics NAV will throw at it. However, in certain rare scenarios the SQL Server engine can run out of worker threads, especially if the Dynamics NAV middle tier cannot read all of the results being returned to it quickly enough. When this happens the SQL Server instance can become unresponsive until some of the executing threads complete their tasks and can take on some of the pending workload. If you encounter this situation you may see the following error message in the SQL Server errorlog:

All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on ASYNC_NETWORK_IO. Process Utilization 0%.

In the unlikely event that you encounter this scenario, we suggest that you connect to SQL Server using the dedicated administrator connection (DAC), and kill the process which is consuming the majority of Worker Threads in order to regain control of the SQL Server instance. Then increase the “Max Worker Threads” configuration option as explained in the article above to avoid future occurrences of the issue.  

 

Gerard Conroy

Microsoft Dynamics NAV Support EMEA

Comments (9)

  1. kine says:

    Thanks Gerard. Will be good to me too from which version of NAV is this applicable. I assume from 2013 up…

  2. Alex says:

    Hi, so you recommend to set MAXDOP higher than '1'? That's what we do at customers, if they have 8 Cores we recommend MAXDOP '4'. But Microsoft Support always tells to set it down to '1' again.

  3. hi Gerard,

    i agree with Alex… Microsoft support tells to set MAXOP = '1' (i asked last year for an installation with NAV 2013 R2),

    same thing has been explained in some events that i attended in the past (with the presence of Microsoft).

    Now I'll try your suggestions.

    Thanks.

  4. Mike_Glue says:

    It would be nice if we could get a document from Microsoft on these types of SQL settings as they pertain to different versions of NAV. We too still leave the MAXDOP to '1' as for many many years, this has been the recommendation from both Microsoft and various independent SQL performance-focused partners.

  5. Thanks for all the feedback. The above article is relevant for all currently supported versions of Microsoft Dynamics NAV. Setting MAXDOP to 1 has indeed been recommended by Microsoft Dynamics NAV support and will be perfectly fine for the performance requirements of most Dynamics NAV scenarios. However, there are various situations where MAXDOP will be set to allow parallelism, e.g:

    1. The Dynamics NAV database may share a SQL Server instance with other applications that require MAXDOP to be configured to allow parallelism.

    2. The default for the MAXDOP setting in SQL Server is 0 and many customers decide to stick with this default for their own reasons.

    3. For very high performance environments where SQL Server must to be finely tuned to get the maximum performance on high specification multi-processor servers. The SQL Server consultants involved in these scenarios may decide that using parallelism will yield incremental performance improvements which merit the extra complexity of managing such an environment.

    The purpose of this blog is to document a specific issue and explain how to configure SQL Server to avoid it in cases where the customer does not want to turn off parallelism. It is not my intention to alter the advice given by Microsoft regarding how to use the MAXDOP configuration option for a SQL Server which hosts a Dynamics NAV database.

    I hope the information provided is helpful.

    Gerard Conroy

    Microsoft Dynamics NAV Support EMEA

  6. Rui Duarte says:

    Hello,

    how can a user know wahat are the setting of the NAV installation he is using?

    [Complementary explanation: I am an Internal Auditor in a Hospital (25 NAV users, listing up to hundreds of thousands of registers)., I find MS DUnamics NAV extremely slow. I suspect NAV may not be indicated for an operation of this size (+ 4 000 employees); or it can be that hardware is is insufficient; OR it can be that the software is just poorly implemented here. Information is contracdictory, as one would expect, but I did notice I can not submit multiple queries.. and a single quesry may take many hours. Hence my question.]

    Thank You,

    Rui

  7. navteam says:

    Hello Rui Duarte,

    Unfortunately, we cannot identify what may be the cause of the performance challenges you are experiencing based on this information.

    We recommend that you contact the Microsoft Dynamics NAV reselling partner that implemented your solution for technical support. They should be able to help you identify what the problem is, whether it is hardware allocation or something in the Dynamics NAV solution that is taking too long to run.

  8. Michel Zehnder says:

    Gerard

    So on a greenfield SQL Installation (defaults to MAXDOP 0), with only NAV running on it, would you change MAXDOP to the Microsoft recommended value for SQL (i.e. set to number of CPU cores, 4 cores = MAXDOP 4), or would you set it to 1?
    If you would set it to 1, why? What is the benefit?

    Thanks
    Michel

  9. Ales Jeglic says:

    Good to hear that basicaly SQL threadpool blocking in connection with net wait is rare but known issue with NST.
    It would be nice to hear in which direction goes mentioned ‘certain rare scenario’ when this blocking could happens.