SQL Server, Dead Connection Detection & Windows Registry Setting TcpMaxDataRetransmissions - What's the real deal?

Dead Connection Detection is an important facet of SQL Server operational database support.  If the client side of a database connection is dead-and-gone, the server side is consuming an ephemeral port, may have an open transaction (and thus be preventing transaction log truncation), may be holding database locks (causing blocking), etc.  The sooner the connection is verified as dead and the database thread cleaned up, the sooner orphaned resources can be dealt with.  On the other hand, if dead connection testing is too aggressive, a sluggish client may not respond to keepalive probes in time and its connection may be closed.  This is a special consideration now for virtual servers involved in batch work.  The nature of their workload, and the "urge to converge" can lead to VMs that are CPU-saturated for short periods of time - aggressive dead connection detection could cause what seem to be network-related failures in these cases.  

TCP supplies a mechanism to handle Dead Connection Detection.  When a TCP port is opened, keepidle functionality can be enabled.  The relevant parameters are the keepidle time, the keepinterval, and the retries. (The names attached to these parameters vary a bit with platform.)  When enabled on a port, the keepidle timer counts down time on an idle connection.  Note - any traffic on the port - even the keepidle probes - resets the timer.  Once the timer reaches zero, a keepidle probe is sent.  If the keepidle probe isn't acknowledged by the other side of the connection within the time specified by keepinterval, another probe is sent.  The number of retries for the keepalive probes is also configurable on many platforms.  If any keepalive probe is ack'd by the other side, the keepalive timer resets (and the remaining probes for that round aren't sent).  If the last probe is sent and not ack'd within the keepinterval, in most cases the connection will be terminated.  In the case of SQL Server, that allows the session ID attached to the port to be terminated and cleaned up as well.  (Here's an important parenthetical: typically, the max number of keepalive probe retries and the max number of retransmits for any TCP packet is controlled by the same parameter.  Keepalive probes use a fixed interval, normal retransmits will double each previous interval until reaching the maximum.)       

The following blog post from 2010 explains Windows registry values KeepAliveTime, KeepAliveInterval, and TCPMaxDataRetransmissions and their roles in dead connection detection.

Things that you may want to know about TCP Keepalives

https://blogs.technet.microsoft.com/nettracer/2010/06/03/things-that-you-may-want-to-know-about-tcp-keepalives/

In SQL Server 2005, a valuable addition was made.  SQL Server would use a "Keep Alive" value specified specifically per instance, rather than the KeepAliveTime specified by the Windows registry.  It defaulted to 30000 ms/30 seconds.

The blog post below explains this new addition, and mentions that the interval for SQL Server will be a fixed 1000 ms/1 second, regardless of the KeepAliveInterval specified in the Windows registry. At the time SQL Server 2005 was introduced, TCPMaxDataRetransmissions from the Windows registries still controlled the maximum number of probes.

Understand special TCP/IP property "Keep Alive" in SQL Server 2005
https://blogs.msdn.microsoft.com/sql_protocols/2006/03/08/understand-special-tcpip-property-keep-alive-in-sql-server-2005/

But!!

Let's look at the following piece of developer-facing documentation.

SIO_KEEPALIVE_VALS control code

https://msdn.microsoft.com/en-us/library/windows/desktop/dd877220.aspx

"On Windows Vista and later, the number of keep-alive probes (data retransmissions) is set to 10 and cannot be changed."

Huh.  On Windows Vista and later - so that would include Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2, and Windows Server 2016.

Its true.  Even though there are lots of places out there that still discuss setting TcpMaxDataRetransmissions in the Windows registry - its for naught.  See a recommendation to set it to 3 from its past default of 5?  Nah.  On Windows Vista and later, you'll get 10 as a max.

In a blog post coming soon, we'll look at TCP protocol properties "Keep Alive" and "Keep Alive Interval" for SQL Native Client 11.0.  I'll also discuss testing methodology, and implications.

*****

Here's a round-up of various documentation that discusses setting TcpMaxDataRetransmissions, without mentioning that from Windows Vista onward this is hardcoded at 10 and cannot be changed.

https://blogs.msdn.microsoft.com/axsupport/2013/11/19/dynamics-ax-2012-r2-retail-switch-from-online-to-offline-database-is-slow/

TechNet Library Windows Server > Windows Server 2008 R2 and Windows Server 2008 > Secure Windows Server

> Threats and Countermeasures Guide: Security Settings in Windows Server 2008 and Windows Vista

> Additional Registry Settings

https://technet.microsoft.com/en-us/library/dd349797(v=ws.10).aspx

This entry recommends changing TcpMaxDataRetransmissions from 5 to 3, for Windows Vista.

https://technet.microsoft.com/en-us/library/cc766102(v=ws.10).aspx

This page mentions TCPMaxDataRetransmissions for Windows Compact Embedded 2013.

https://msdn.microsoft.com/en-us/library/dn167252.aspx

This page about accessing SQL Azure database recommends setting registry value TCPMaxDataRetransmissions to 10, which is apparently its hardcoded value.

https://msdn.microsoft.com/en-us/library/hh290696(v=sql.110).aspx

This kb article describes changing the TcpMaxDataRetransmissions registry value for Windows 7, Windows 2008 R2, Windows 2008, and Windows Vista.

https://support.microsoft.com/en-us/kb/170359

This 2015 post describes setting TcpMaxDataRetransmissions registry value as a troubleshooting measure.

https://blogs.technet.microsoft.com/dpm/2015/05/20/an-in-depth-look-at-the-registry-settings-that-control-microsoft-dpm-2012/

O Registry, Registry! But wherefore did you so much tempt the heavens?

O Registry, Registry!  But wherefore did you so much tempt the heavens?
Whither WinsockListenBacklog?
Whither EnableDynamicBacklog?
Whither DynamicBacklogGrowthDelta, MaximumDynamicBacklog?
Whither MinimumDynamicBacklog away so fast?

Apologies from the Sasquatch to the Bard.  Its the coffee talkin'.

Under stress of concurrent TCP connection attempts, various errors can be reported by SQL Server as connections are refused.  Below is a good description of one of the connection failure modes.  Login authentication failures (regardless of credentials supplied) can also be reported under concurrent connection stress.
SQL Server logs 17832 with multiple TCP\IP connection requests
http://support.microsoft.com/kb/154628

The following support document is a classic. Contains of the best descriptions of SQL Server Windows registry setting WinsockListenBacklog.

Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled
KB328476 - Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled (microsoft.com) 


Check the relevant products in the "applies to section".

SQL Server 2005 editions are listed.  Does that mean that registry setting WinsockListenBacklog should be created for SQL Server 2005 and later?  Could that resolve some of the TCP connection failures under stress for SQL Server 2008 or 2008 R2?

Nope.

The support article "applies" to SQL Server 2005 because it provides the following information:
"Starting in SQL Server 2005, the network library passes a value of SOMAXCONN as the backlog setting to the listen API. SOMAXCONN allows the Winsock provider to set a maximum reasonable value for this setting. Therefore, the WinsockListenBacklog registry key is no longer used or needed in SQL Server 2005."

So, no more WinsockListenBacklog for SQL Server 2005 and beyond.

The clever reader will remember the dynamicbacklog family of registry values.  Full description in this support article:
Internet server unavailable because of malicious SYN attacks
http://support.microsoft.com/kb/142641

[update 2020 December 21 - it's a dead link now and I don't have a replacement yet :-( ]

So... should those be set for SQL Server 2005 and beyond?  Not if the OS is Windows Server 2008 or beyond.  They won't have any effect.  This is explained here.
Where have those AFD driver related registry (DynamicBacklogGrowthDelta / EnableDynamicBacklog ...) keys gone?
http://blogs.technet.com/b/nettracer/archive/2010/08/11/where-have-those-afd-driver-related-registry-dynamicbackloggrowthdelta-enabledynamicbacklog-maximumdynamicbacklog-minimumdynamicbacklog-keys-gone.aspx

[update 2020 December 21 - it's a dead link now and I don't have a replacement yet :-( ]

OK.  So... if there are connection failures under concurrent TCP connection stress with SQL Server 2008 or later, on Windows 2008 or later... whatfor to do?  Whither away the salve to soothe?

I'll have to pick that up later :)  As far as I can tell right now, the correctives on the most recent versions of SQL Server and Windows Server OS have to be about servicing the connection requests faster (clear the way for the nonpaged memory pool, make sure there are enough TCP connection memory blocks, take care of any other memory pressure conditions, consider binding the NIC to cores, utilize RSS scaling and potentially increase the number of cores servicing the NIC, etc).

More details as I learn, especially if I find specific correctives or diagnostics... wanted to get this out there so folks don't waste time with registry changes once valued but now merely "lodged with me useless".  My apologies to Milton.  

Disabling IdlePrioritySupported for dedicated SQL Server LUNs?

Disabling disk IO priorities per Windows drive by setting IdlePriority = 0 in the registry is mentioned on page 53 of "Performance Tuning Guidelines for Windows Server 2012" (http://msdn.microsoft.com/library/windows/hardware/jj248719) and also in " Performance Tuning Guidelines for Windows Server 2008 R2" (http://msdn.microsoft.com/en-us/library/windows/hardware/gg463392.aspx).

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\DeviceClasses\{Device GUID}\DeviceParameters\Classpnp\IdlePrioritySupported

I haven't seen comparative test results yet, and also haven't performed my own tests yet. But, trying to crank nightly ETL as fast as possible (without pressuring other systems on shared storage :) ).

I'll update this post later with what I find from the testing of others, or what we see in our own testing.

Windows Port Exhaustion and Connection Failures

Port exhaustion is far from unique to Windows.  I first became familiar with port exhaustion and idle connection behavior by recommending intervention and eventually writing up some best practices for a tiered database architecture on AIX and HP-UX systems.  But, here I'll just describe the Windows details.
  
Although I've seen sources discuss Windows idle connection termination behavior, and other sources discuss TIME_WAIT delay status, and still other sources discuss port exhaustion, I can't think of a single place that I saw all of those concepts tied together in a neat little bundle.  That might still be true once I publish this blog post :)  At least it'll be here for me to refer to in the future :)

If you want to evaluate a particular delayed/rejected connection situation for port exhaustion, the Powershell script at the following location is a great place to start:
http://blogs.msdn.com/b/debuggingtoolbox/archive/2010/10/11/powershell-script-troubleshooting-for-port-exhaustion-using-netstat.aspx

I recommend evaluating the idle connection and TIME_WAIT behavior regardless, from a best practice standpoint.

Client/server communication typically relies on dynamic ports.  Dynamic ports are also known as ephemeral or anonymous ports.
Client communication will often use an available dynamic port on the client system to begin communication with a specified port on the server.  The maximum number of connections from that client is then limited to the number of dynamic ports.
Some server activities use dynamic ports as well: a communication request initially arrives on a specific server port listener, and the client connection is handed off to a dynamic server port.  FTP is an example of server activity which uses dynamic ports.
Port exhaustion occurs when there are no available dynamic ports for new connections.  If dynamic ports are used on the client and server side of the connections, port exhaustion could occur on the client or server system.

What can lead to port exhaustion?  I'll only give a few examples here.  But the main concepts involved are the limited number of dynamic ports on the client and/or server, the amount of time to terminate idle connections, and the amount of time before a "closed" dynamic port can be reused.
If a web service goes crazy and starts gobbling up dynamic ports, all available dynamic ports can get chewed up real fast.  If it keeps those connections open, as long as they are open no more connections for that protocol will succeed.
If it closes the connections, there could still be a problem.  That's because of the amount of time that a port will wait in the TIME_WAIT state after closed, before it can be reused.  By default this is 4 minutes.  A crazy web service could keep the connection inflow rate equal to the port available rate for a long time, leading to lots of connection failures.
Here's another thing to add to the mix: if a connection isn't properly closed, whether due to the connection being severed between client code and server code (power outage, switch crash or even intrusion prevention), the connection may need to time out as an idle connection before its terminated.  By default, that will take more than 2 hours.

So, here's where I've typically seen this come into play in an OLTP setting:
A power outage drops large number of Citrix or web servers from a client server farm.  All of the connections on the 'server' server are now idle.  They aren't terminated immediately, they need to go through TCP timeout protocol.  Assume the number of connections at the time of power loss was 80% of the available dynamic ports.  Assume that many connections will be attempted again when those client servers come back on line 20 minutes after the power loss.

That's 160% of the available dynamic ports. Of the new connections, only 1/4 will succeed until hitting the dynamic port limit.  Since power was restored 20 minutes after power was lost, that means that 3 out of 4 new connections will be waiting - either for one of the other new connections to terminate and the port becomes available, or for the remaining default 1 hour and 40 minutes until idle connections from the power loss are terminated.
 
So, what can be done about this?

*Step 1
Verify dynamic port range is sufficient.

For Windows version up to and including Windows Server 2003, the default dynamic port range was 1025 to 5000.  In those versions, HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\MaxUserPort registry key could set a nondefault max dynamic port number.  Windows Server 2003 security bulletin MS08-037 hotfix introduced a new default dynamic port range from 49152 to 65535.  That default range is still current through Windows Server 2012 6.2.9200.
http://support.microsoft.com/kb/953230
http://support.microsoft.com/kb/956188

Starting at least with Windows Server 2008 (and maybe with Windows Server 2003 after the security bulletin, I'm not sure), the dynamic port range is specified per protocol.
Check it for each protocol with these commands:
netsh int ipv4 show dynamicport tcp
netsh int ipv4 show dynamicport udp
netsh int ipv6 show dynamicport tcp
netsh int ipv6 show dynamicport udp

A typical recommendation for Microsoft Exchange 2007 systems was a dynamic port range of 1025 to 65535.  Seems reasonable for most Windows server systems to me, unless there is a particular reason against it (such as the possibility of consuming too much connection-related memory).

*Step 2
Verify idle connection behavior is appropriate.
For an idle TCP connection, the default Windows behavior is to wait for 2 hours before sending a TCP keepalive packet.
The registry value KeepAliveTime (milliseconds) can specify an alternative elapsed time.
If the keepalive packet is not acknowledged within 1 second by default, another keepalive packet will be sent.  Registry entry KeepAliveInterval (milliseconds) specifies this interval.
The number of keepalive packet retries is determined on some Windows versions by registry vlaue MaxDataRetries.  Various documents indicate this is hardcoded at 10 in Windows Server 2008 R2.

I recommend setting KeepAliveTime to 300000 milliseconds (5 minutes), dropping expected idle time to 5 minutes and 10 seconds.

I do not recommend changing KeepAliveInterval from the 1 second default.  There isn't much to gain there, and I haven't seen recommendations to change this value in any of the sources I've consulted.

While "Additional Registry Entries" below recommends changing MaxDataRetries from default 5 to 3 retries, there just isn't much to gain in terms of idle time expected before termination.

TCP/IP Registry Values for Microsoft Windows Vista and Windows Server 2008
http://www.microsoft.com/en-us/download/details.aspx?id=9152

Windows Server 2008 R2 and Windows Server 2008
>Secure Windows Server
>>Threats and Vulnerabilities Mitigation
>>>Threats and Countermeasures Guide: Security Settings in Windows Server 2008 and Windows Vista
>>>>Additional Registry Entries
http://technet.microsoft.com/en-us/library/dd349797%28v=ws.10%29.aspx

*Step 3
Verify appropriate TIME_WAIT behavior.
The registry value TCPTimedWaitDelay in HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters determines how long a port will remain in TIME_WAIT status after the port has been closed.  By default, the value of 240 seconds or 4 minutes is used.  So, a dynamic port typically cannot be reused for 4 minutes after communication is complete.  The intent for this delay was to allow for efficient handling of a new communication request from the same client.  However, the combination of a small dynamic port range and a long wait time for reuse can easily lead to port exhaustion.
This kb article describes how the size of the dynamic port range and the time period specified for TCPTimedWaitDelay can cause port exhaustion for SQL Server client connections.
http://support.microsoft.com/kb/328476
I recommend a TCPTimedWaitDelay of 30 seconds.  This aligns with the typical Exchange 2007 recommendation.

*UPDATE 4/29/2013 sql.sasquatch
First time I've noticed this. An 'application layer' TCP keepalive was implemented in SQL Server 2005, with a default timeout of 30 seconds and default interval of 1 second. This is separate from the 'looks alive' and 'isalive' checks used with clustering.
END UPDATE*
http://msdn.microsoft.com/en-us/library/ms190771(v=sql.105).aspx
http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546852.aspx