Intermittent "Login timeout expired" after migrating to VmWare SQL Server

Question

We are experiencing intermittent Login timeout expired in our application after migration from a physical standalone SQL Server 2008 R2 / W2k8 R2 to a VMWare SQL Server 2014 / Win 2012 R2.
It is happening around 5 times per day, so it's difficult to troubleshoot.

Failure Message

Error: Microsoft SQL Server Native Client 10.0 : Unable to complete login process due to delay in prelogin response.
Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

More Details

• I am pretty sure the sp_configure are the same in both instances.
• We have less cpu compared to the previous server, but Max Worker Threads is OK.
• When I was monitoring connections locally on SQL Server it was working fine at the same time I got Login timeout expired from the application.
• Configure the remote query timeout will not solve my problem here.
• We are using VMXNET3 Virtual NIC drive.

Answer 1

I am here answering my own question to help those who are experiencing the same behavior. After many observations and experiments with searching over the internet, I found the solution to this issue following the next steps. Make sure you test everything before going to production.

1. Disable TCP Chimney features on the both the SQL Server & Client application server:

2. Disable TCPChimney and Offload features on the system level registries.

You can do it by running the command "Netsh int ip set chimney DISABLED" from command prompt. But you can do it manually.

Go to the registry "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters"

a. Double-click the EnableTCPChimney registry entry. If any entry is not present, create it by right clicking on the blank space and click on Create DWORD (32 bit).
b. In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
c. Double-click the EnableRSS registry entry.
d. In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
e. Double-click the EnableTCPA registry entry.
f. In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
g. Create the registry key “SynAttackProtect” and – In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
h. Restart the server.

3. Disable TCPChimney and Offload features in the properties of NIC card. Below are the instructions.

a. Click Start, click Run, type ncpa.cpl, and then click OK.
b. Right-click a network adapter object, and then click Properties.
c. Click Configure, and then click the Advanced tab.
d. In the Property list, click Receive Side Scaling, click Disable in the Value list, and then click OK.
e. In the Property list, click TCP/IP Offload, click Disable in the Value list, and then click OK.
f. Repeat steps 2 through 5 for each network adapter object.

4. Check if “IPv4 Checksum Offload” & “IPv4 Large Send Offload” OR “Checksum Offload” & “Large Send Offload” are enabled under Advanced Settings of NIC. If they are, disable them. These options may vary with the model and make of the NIC. The other properties to look for and disable are:

i. Offload Receive IP Checksum
ii. Offload Receive TCP Checksum
iii. Offload TCP Segmentation
iv. Offload Transmit IP Checksum
v. Offload Transmit TCP Checksum

Related articles:

Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008: http://support.microsoft.com/kb/951037
Error message when an application connects to SQL Server on a server that is running Windows Server 2003: "General Network error," "Communication link failure," or "A transport-level error": http://support.microsoft.com/kb/942861

Regarding the performance impact:

TCP Chimney Offload – Possible Performance and Concurrency Impacts to SQL Server Workloads: http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx

Note: This would trigger the network breaking for a couple of seconds, so schedule this accordingly. Any registry changes would come into effect only after a reboot of the machine is done. Also if were disabling any NIC card property, the network will go down for a couple of seconds for the changes to reflect before it can resume back. So if this is a PRODUCTION box, schedule this during your downtime or Maintenance window

TCP TIME-WAIT Delay & MaxUserPort :
The following changes needs to be done on machines from where we connect to the SQL Server from the client applications. Add this on your application / web servers universally.

5. TCP TIME-WAIT Delay:
Set the TcpTimedWaitDelay registry key that is available in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters to 30 secs.
If the registry key is not present, create them and set it to the value that we had suggested above.

6. MaxUserPort:
Set the MaxUserPort registry key that is available in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters to 65,000. Again if the registry key is not present, create them and set it to the value that we had suggested above.

Refer the following article for more information:
TCP TIME-WAIT Delay: https://technet.microsoft.com/en-us/library/cc938217.aspx
MaxUserPort: https://technet.microsoft.com/en-us/library/cc938196.aspx