Sql-server – Intermittent failures to connect to 127.0.0.1

sql serversql-server-2016windows-server

I have an instance of SQL Server 2016 (13.0.4451) which I can reliably connect to over shared memory (lpc:(local)\instance). If instead I try to connect to 127.0.0.1,1433 I experience low (~1/1000) failure rates with the error message:

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=3; handshake=14995;

What would affect the loopback connection, but not the shared memory connection?

Related, but far more broad: Connection Timeout Expired without apparent network issue

Best Answer

Since this question is already quite old, I won't bother asking you to clarify the problem, and will instead try to focus on providing some things that might be worthwhile looking at, that some future visitor might find helpful.

  • Microsoft .Net Framework 4.5 and 4.5.1 have a known issue with connections to localhost

  • It's possible your server is suffering from thread-starvation. Confirm the maximum number of expected simultaneous connections, including pooled connections that might not be actively performing tasks is below the maximum number of threads for your server.

  • Confirm there are no network-related filters in place that might be dropping connections. Running netsh winsock show catalog will show the base service providers and any layered service providers that might be interfering with connectivity. Particularly look for anti-virus or firewall apps.

  • Check network utilization, and if necessary tune networking parameters for performance.

  • If SQL Server is running in a virtual machine, check the host server to ensure a memory reservation has been set that prevents memory being swapped out (VMware, Hyper-V). Ensure a CPU reservation has been set to prevent the virtual machine being starved of processing power.

Related Question