Understanding the KeepAlive and KeepAliveInterval Parameters in SQL Server

[SAPSupport]

I want to increase the TCP Keepalive duration in my SQL Server.
Currently, the setting is dbs/mss/conn_opts = KeepAlive=30;KeepAliveInterval=12.
If I change it to dbs/mss/conn_opts = KeepAlive=120;KeepAliveInterval=20, how will the behavior change?

------------------------------------------------------------------------------------------------------------------------------------------------
Learn more about the SAP Support user and program here.

[SAPSupport · Accepted solution]

Below is the explanation about the parameters.

Keep Alive: How often TCP verifies that an idle connection is still intact.
Keep Alive Interval: The retry interval for unresponsive TCP Keep Alive transmissions. The default is 1000ms.

The number of Keep Alive messages sent is controlled by TCP/IP parameter TcpMaxDataRetransmissions(default: 5) .

When KeepAlive is set to 30 seconds and KeepAliveInterval is set to 12 seconds, the TCP keep-alive mechanism works as follows:

1. Assume that the TCP connection is idle for a period of time. After 30 seconds of idleness, the operating system sends the first keep-alive probe packet to the remote endpoint to verify if the connection is still active.
2. If the remote endpoint responds to the keep-alive probe packet, the connection remains open.
3. After 12 seconds, the operating system sends another keep-alive probe packet if there is no response from the remote endpoint.
4. Repeat step 3 until the maximum number of retries(which is controlled by the TCP/IP parameter TcpMaxDataRetransmissions).

Given the above configuration of SQL Server clients, Keep Alive messages must be responded to by a SQL Server in no more than 90 seconds(30+12*5=90) best case.

dbs/mss/conn_opts = KeepAlive=30;KeepAliveInterval=12.
TcpMaxDataRetransmissions=5

If the configuration is changed to below, then it would allow 220s(120+20*5=220).

dbs/mss/conn_opts = KeepAlive=120;KeepAliveInterval=20,
TcpMaxDataRetransmissions=5

A connection disconnect error will be reported if the keep-alive probe is lost due to a remote host or network failure, or if multiple probes (default 5 in this case) are sent without receiving a response.