Understand special TCP/IP property “Keep Alive” in SQL Server 2005
When open TCP/IP properties by following SQL Server Configuration Manager -> SQL Server 2005 Network Configuration ->Protocols -> TCP/IP, you will notice there is new added property named “Keep Alive”, which is not available in SQL Server 2000.
This parameter controls how often TCP attempts to verify that an idle connection is still intact by sending a keep alive packet to its peer. If the remote system is still reachable and functioning, a acknowledge packet is sent back. Otherwise, the local TCP will keep sending “Keep Alive” packet in an interval of KeepAliveInterval for TcpMaxDataRetransmissions times. If no acknowledge packet is received during this period, the local TCP will reset the connection. For every SQL Server TCP connections, Keep Alive is 30,000 millisecond by default and KeepAliveInterval is hard-coded 1,000 millisecond. TcpMaxDataRetransmissions is 5 by default, configurable for entire machine through HKLMSYSTEMCurrentControlSetServicesTcpipParameters. As an example, under default configuration, SQL Server can detect a “orphaned” connection in about 35 seconds. Orphaned connections are often caused by remote system reset, hardware/power failure or network outages. In case where the remote process is shutdown or killed, TCP reset is usually send out by system TCP/IP stack and the connection will be closed as soon as the reset packet is received. The detailed description about TCP keep-alives is in IETF RFC 1122.
In Windows, as well as many other operating systems, TCP/IP Keep-Alive is not enabled by default. By enabling this, SQL Server can, in a timely manner, detect “orphaned connection” and free up valuable resource associated with each connection, including its session context, locks, kernel TCP buffers and etc., which sometime can become very expensive for a SQL Server running heavy transactions. The major drawbacks, among many others, are (1) Keep-Alive consumes bandwidth on a perfect idle connection; (2) It causes good connection to break during transient network failures. So configuring the keep-alive values too small is not recommended.
The following KB about orphaned connection/session for SQL Server 2000 shares valuable insight on how to configure Keep-Alive for named pipe connections.
Do you know that you can post question w.r.t SQL Server data access, connectivty issues at https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1 ?
Software Design Engineer, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights