Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL refusing connection in load test

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.

like image 526
Shaul Behr Avatar asked Jan 05 '23 19:01

Shaul Behr


2 Answers

"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.

like image 200
Dave Smash Avatar answered Jan 11 '23 22:01

Dave Smash


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.

like image 27
Paul Tsai Avatar answered Jan 11 '23 23:01

Paul Tsai