Several questions about SQLserverconnectionKeepAlive

1. What is the keepAlive of the SQLserverTCP connection? To put it simply, keepalive is the keepaliveinterval and keepalivetime parameters of the TCP protocol specified by SQLserver when establishing each TCP connection. For each TCP connection, if the idle time of the connection (without any data interaction) exceeds keepalivetime

1. What is the keep Alive of the SQL server TCP connection? To put it simply, keep alive is the keepaliveinterval and keepalivetime parameters of the TCP protocol specified by SQL server when establishing each TCP connection. For each TCP connection, if the idle time of the connection (without any data interaction) exceeds keepalivetime

1What is SQL server TCPConnected keep Alive?

To put it simply, keep alive is the keepaliveinterval and keepalivetime parameters of the TCP protocol specified by SQL server when establishing each TCP connection. In this way, if the idle time of each TCP connection (without any data interaction) exceeds keepalivetime, the TCP protocol will automatically send a keepalive packet to check whether the connection is alive or not. If the number of keepalive detection times exceeds the value defined by the Registry TcpMaxDataRetransmissions and the other party does not respond, TCP considers the connection to be faulty and closes it. Through this mechanism, SQL server can detect problems such as orphaned connection.

For each TCP connection, SQL server sets the default value of keep alive to 30 seconds and keepaliveinterval to 1 second. TcpMaxDataRetransmissions configured for Windows TCP is 5 times by default. That is to say, if the TCP connection idle takes 30 seconds, TCP will send the first keepalive check. If it fails, TCP resends the keepalive packet every one second until it resends the packet five times. If the fifth check still fails, the connection is closed. Therefore, if a TCP connection encounters an exception, it will be closed in about 35 seconds.

2Where you can configure SQL serverKeep aliveConfiguration?

The code in SQL server 2000 also specifies the keep alive attribute for TCP connections, but does not provide user interfaces for custom modifications. SQL server2005 uses configuration manager to modify the keep alive value, but does not modify keepalive interval. Keepaliveinterval is the 1 second of hardcoded.

The Configuration manager interface is as follows:

This value is saved in the following registry location:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft SQL Server \ MSSQL .? \ MSSQLServer \ SuperSocketNetLib \ Tcp

Note that the Native client of SQL server has a similar configuration. Do not mix it with the TCP configuration of server side:

The Native client's keep alive configuration is saved in the following location:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ Client \ SNI9.0 \ tcp