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.
Disable TCP Chimney features on the both the SQL Server & Client application server:
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
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.
- 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.
- 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
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
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.
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