All right, I must confess;  my SQL Server is not installed on a super computer.  Also, it is not installed and configured by a SQL Server MVP nor by Alain Krikilion (picture from NAV TechDays 2013).Alain_Krikilion

The Hyper-V machine running my SQL Server is using two processors and 12GB of memory.  Operating system is Windows Server 2012 R2 x64 and SQL Server version 2014.

I have two NAV 2016 CU5 instances on two servers running a multi tenant application.  Every time I started the second instance everything froze.  Even the SQL Server stopped responding.  I am not going to pretend that I understand the issue, but still, I found a solution and wanted to share that with you.

We have a property for the NAV Service called “Max Concurrent Calls”.  This is, by default, set to 40.  I found out that if I lowered this value to 10 on the second instance I was able to start it.  That is a workaround, not a solution.  What if I want to start the third service instance, do I then need to lower that even more or update the configuration for all the previously running instances?

So I turned my attention to my SQL Server.  On MSDN Microsoft states:

This topic describes how to set the user connections server configuration option in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL. The user connections option specifies the maximum number of simultaneous user connections that are allowed on an instance of SQL Server. The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that the maximum (32,767) user connections are allowed.

This page also shows an example on how to configure this setting.  In that example the property is set to 325.  So, I tried that, returning the “Max Concurrent Calls” back to the default value for all NAV Services.  Happy times, everything is running smooth.

So how can two NAV Servers that have “Max Concurrent Calls” set to 40 flood a SQL server that supports 325 concurrent user connections?  I even looked at the network connections by executing

netstat -nao | find “1433” > connections.txt

in command prompt and looking at the output.  The total number of connections was less than 50!  I think I must leave this to Microsoft or my friend Alain to explain this.

So it was clear; the Next, Next, Finish methodology failed me this time.  I needed to get my hands dirty and fix the SQL Server configuration.

First, I looked at the “Max Degree of Parallelism” property.  The default value is zero but is should be set to “No. of CPUs” – 1.

Parallelism

Then updated the connections property.

UserConnections

I restarted the SQL Server Service to apply changes.

I updated one more property and below is the SQL Query I used to update these properties.

 

Why this turns up in NAV 2016 and not in previous NAV versions, what changed, I don’t know.  Perhaps someone out there has the answer.

If you get stuck in a problem like this, see if you have NAS services running on both instances.  That seemed to be my problem.

5 thoughts on “My SQL Server 2014 gets flooded and stops responding

  1. Alain Krikilion says:

    Max degree of parallelism is max the no. of physical processors. Or max half of cores assigned to the VM. And cost threshold for parallelism =50. Maybe a parallel query was waiting for itself because assigned to 2 virtual cores of the same physical core. Also 5 cost threshold is too low for parallelism. The overhead is probably higher than the gain in speed.

  2. phenno says:

    Gunnar,

    what was your original setup for “max worker threads” and max “user connections”?

    1. I am not sure. However, I believe Microsoft fixed this issue in CU13

Leave a Reply

%d bloggers like this: