SQL refusing connection in load test

Question

I'm running a load test on my system. At a certain level of load, I start getting SQL errors in my log:

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Prprovidererror: 40 - Could not operrorconnection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The network path was not found

By running performance monitor on the SQL server in question, I found the following:

• CPU level rarely exceeds 50%. (On a previous iteration I saw that it was maxing out at 100%, so I increased the specs of the VM, which helped push the problem to a higher load level.)
• Number of user connections got to a shade over 8,000. The Sql Server has the default setting of 32,767 max connections.
• The connection string specifies a max pool size of 1000 connections to each database, and there are 100 databases on the server. The load test is randomly distributed between the 100 databases, so there should be a fairly even distribution, meaning about 80 connections per database. Nowhere near the 1k limit.

What other factors might cause Sql Server to stop being able to accept connections?

UPDATE: extra info: I'm using Entity Framework Core (EF7) for my DB connections, if that helps any.

Answer 1

"Network Path Not Found" does not seem like an error related to SQL Server's capacity. As a former "IT Guy," I suspect that a firewall is dropping your packets. If this is during a stress test, the firewall could be interpreting the numerous requests as a denial of service attack, and using some sort of predefined rule to drop connections for a specified time period.

What is your network environment? If you have a hardware firewall or router with IPS capabilities, I would check those logs to see if you find a smoking gun. You might have to create a special rule to allow unlimited traffic to your SQL Server.

Answer 2

It's a bit curious that you are getting that many connections to the database. You should be utilizing connection pooling; even under intense load, the connection pooling should greatly reduce the number of active connections being used.

Can you provide the code that's accessing the database? Are you calling the dispose() method or closing the connection?

Also, have you looked to see if data datacaching would ease the db load? A 2-5 second datacache can greatly reduce database calls.

Answer 3

You are running into the TCP listen() backlog limit for the SQL-Server's listening port. When this happens, Windows platforms (but not *nix platforms) will issue 'connection refused' for further incoming connections.

I'm not an SQL-Server guy but there is bound to be a parameter somewhere by which you can increase its listen backlog.

Alternatively you should look into better or more connection pooling at the client.

Answer 4

It turns out the problem wasn't on SQL at all. The problem was on our API server, where some of the APIs were spinning off hundreds of parallel threads, each making its own connection to the database. The load was simply too much for the API server, and it started returning "Access Denied" exceptions without even really attempting to connect to the database.

Solution: we throttled the number of threads being spun off, using the pattern shown in this answer.