We're experiencing a problem with connections to SQL Server and after a week of digging through I was able to pinpoint it to Winsock limits. Here's the description:
- We have SQL Server 2014 (and 2016) Ent installations running on Windows Server 2012 R2. Virtualized and not virtualized. We observe the problem in both environments.
- The clients do not use connection pooling (please don't recommend turning it on, it's root of the problem, I know, but it's something I can't do anything about)
- Clients create and close new connections at extremely high rate: normally it's 1500 closed/opened connections per second from a single client. There are 20-25 clients per SQL Server that are doing it at the same time.
Now, at certain points during the business day, we experience traffic bursts, which essentially makes the new connection rate spike up to 2500-3500 connection attempts per second and it continues for around 2-3 seconds.
That's when the fun starts: clients get connection errors during the same 2-3 second bursts.
What we have confirmed and done:
- It is not port exhaustion, neither on the server nor on the client.
- It is not SQL Server worker thread exhaustion. Here're graphs that demonstrate it:
- It is not a
TIME_WAITproblem, previous connections by the clients are gracefully closed with
- A connection does not reach SQL Server and gets rejected by Winsock. Here is a PerfMon graph that demonstrates it:
- We tried adjusting the NIC settings and Windows TCP/IP feature settings (RSS, TSO, Scaling Window - enabling/disabling) and it did not fix the issue.
- It is not SYN attack protection. We have collected a
netshtrace and there were no indications of entering SYN flood attack protection mode.
- Hardware resources should not be an issue. We see this problem even on 72 core / 144 thread 4-socket box with 2TB RAM.
Prior to SQL Server 2005 we were able to adjust the backlog window using registry (I'm talking about these instructions). Right now we don't have this kind of ability.
The same document I've linked above says:
Starting in SQL Server 2005, the network library passes a value of SOMAXCONN as the backlog setting to the
listenAPI. SOMAXCONN allows the Winsock provider to set a maximum reasonable value for this setting.
My understanding is that it happens based on the
max connections setting. We have it configured to
0 (unlimited) in SQL Server.
So, here's the question: what can we do to fix the issue? Or are we virtually hitting the limits of Winsock?