SQL Server's KeepAlive

A TCP keepAlive

KeepAliveTime indicates that the TCP connection is in the open when the detection frequency, once the probe packet is not returned, the frequency of the KeepAliveInterval sent, after several retries, if the probe packet is not returned, then concluded that the TCP connection has been disconnected.

The keepalive of SQL Server and the TCP protocol of Windows are the same keepalive, but do not interfere with each other.

SQL Server defaults to each TCP connection by specifying keep alive for 30 seconds and keepaliveinterval to 1 seconds. The TcpMaxDataRetransmissions default for Windows TCP configuration is 5 times. This means that if the TCP connection is idle for 30 seconds, TCP sends the first keepalive check. If it fails, TCP will re-send the keepalive packet every 1 seconds until it is re-sent 5 times. If the test fails for five times, the connection is close. Therefore, if a TCP connection has an abnormal problem, it will be close for about 35 seconds.

SQL server2005 uses Configuration Manager to modify keep alive values, but keepalive interval cannot be modified. KeepAliveInterval is the hardcoded for 1 seconds.

Note that SQL Server's native Client also has a similar configuration and does not mix with the TCP configuration of server side.

Server-Side TCP connections and TCP connections to clients each send their own keepalive packets

SQL Server does not shut down a normal TCP connection. Unless the underlying TCP reports an error. or error connecting or receiving data. Query Sys.dm_exec_connections compare output inside the Last_read/last_write and now time can probably know the idle time of a connection.

SQL Native Client and SQLclient. Where SQLclient is the managed provider used within . NET. the KeepAlive settings currently available in SQL Server Configuration Manager are for the SQL Native Client :

SQL Server Management Studio (ssms.exe) is SQLclient with . NET managed, so for native the client setting is not valid for SQLclient. SQL Server Management Studio uses a value of hard-coded and cannot be modified.

two what happens to the TCP connections that already exist if the network cable is unplugged on the server side ? will SQL Server close the connection right away? What if you unplug the cable and plug it back in right away?

[ answer ] This is a complicated problem. When the network cable is unplugged, the NIC first learns about the event. both Windows 2003 and the following operating systems implement Media Sense. After the operating system receives a Media Sense disconnection (disconnect) event from the NIC, if the disconnectdampinterval(typically 5-10 seconds), the network cable Connection has not been restored, then the message will be sent to listen to the network card IP connection. Then all upper-level applications that listen to the IP, such as SQL Server, get disconnect events, and then do the appropriate actions such as kill connection , rollback affairs and so on.

more specifically,SQL Serverof NetworkIois throughIocp(Iocompletionport) is implemented. SQL ServerThere are several specialized threads listening to the port in the background. These threads callGetQueuedCompletionStatus() function to get the network data state, and then call the correspondingCallbackfunctions, such asReadhandlerfunction) To do the specific data processing. When the network cable is broken, the operating system is over.Disconnectdampintervaltime later,GetQueuedCompletionStatusfunction will getDisconnectinformation, andCallbackfunctionReadhandleris called to read the data but is actually readDisconnectNetwork fault events. SQL Serverlearned that theDisconnectevent, you generate aTask, theTaskis to doKill sessionthe task of putting the transaction will roll, put the connection(connection)turn it off.

So if you're in a very fast time (a few seconds), unplug the network cable and plug it back in, then the SQL Server TCP connection (connection) will remain closed.

SQL Server's KeepAlive