轉：http://blogs.msdn.com/b/apgcdsd/archive/2011/05/03/sql-server-connection-keepalive-faq.aspx
1、什麼是SQL server TCP連線的keep Alive？
簡單說，keep alive 是SQL server在建立每一個TCP 連線的時候，指定了TCP 協議的keepaliveinterval 和keepalivetime引數。這樣對每個TCP連線，如果該連線空閒時間（沒有任何資料互動）超過keepalivetime，TCP協議會自動發出keepalive 包檢測連線存活與否。如果keepalive 檢測次數超過登錄檔TcpMaxDataRetransmissions定義的值而對方還是沒有回應，那麼TCP就認為該連線有問題而關閉它。透過這樣的機制SQL server能夠檢測出orphaned connection等問題。
SQL server 對每個TCP連線預設指定keep alive 為30秒，keepaliveinterval為1秒。Windows TCP配置的TcpMaxDataRetransmissions預設是5次。就是說，如果TCP連線idle了30秒，那麼TCP會傳送第一個keepalive檢查。如果失敗，那麼TCP會每隔1秒重發keepalive 包，直到重發5次。如果第五次檢測依然失敗，則該連線就被close。所以，一個TCP連線如果出現異常問題，大概在35秒的時候就會被close。
2、在那裡可以配置SQL server 的keep alive 配置？
SQL server 2000程式碼裡面也有對TCP連線指定keep alive屬性，但沒有提供使用者介面給使用者定製修改。SQL server2005使用configuration manager可以修改keep alive值，但是不能修改keepalive interval。 Keepaliveinterval是hardcoded的1秒。
Configuration manager的介面如下：
該值儲存在登錄檔如下位置：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.?\MSSQLServer\SuperSocketNetLib\Tcp
注意SQL server的Native client也有類似配置，不要和server side 的TCP配置搞混了:
Native client的 keep alive 配置儲存在如下位置:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\tcp
3、SQL server的keepalive 和Windows的TCP協議裡面的keepalive 是一樣的嗎？
原理一樣，但不相互干擾。
Windows 的TCP協議也有keep alive 配置，位置如下：
HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
OS的TCP協議的keep alive 和SQL server 的keep alive 工作原理一樣的，就是在建立TCP連線的時候指定TCP連線的keepalive屬性（參見後面描述）。但是SQL server讀取自己登錄檔的keep alive來設定TCP連線屬性，不理會windows OS的登錄檔裡面的keepalivetime和keepaliveinterval的值。
如果一個應用程式沒有顯式呼叫函式設定TCP連線的keepalive屬性，那麼他的TCP連線預設使用OS 的TCP配置。OSkeep alive配置預設是關閉的。
有關OS 的TCP配置參考如下文件：
http://support.microsoft.com/kb/314053
KeepAliveInterval
Key: Tcpip\Parameters
Value Type: REG_DWORD - Time in milliseconds
Valid Range: 1 - 0xFFFFFFFF
Default: 1000 (one second)
Description: This parameter determines the interval that separates keepalive retransmissions until a response is received. After a response is received, KeepAliveTime again controls the delay until the next keepalive transmission. The connection is aborted after the number of retransmissions that are specified by TcpMaxDataRetransmissions are unanswered.
KeepAliveTime
Key: Tcpip\Parameters
Value Type: REG_DWORD - Time in milliseconds
Valid Range: 1 - 0xFFFFFFFF
Default: 7,200,000 (two hours)
Description: The parameter controls how frequently TCP tries to verify that an idle connection is still intact by sending a keepalive packet. If the remote computer is still reachable and functioning, the remote computer acknowledges the keepalive transmission. By default, keepalive packets are not sent. A program can turn on this feature on a connection
4、SQL server 和OS裡面 的TCP的keep alive是如何實現的？
詳見如下文件：
http://msdn.microsoft.com/en-us/library/ms741621.aspx
SQL server也是呼叫如下API，把keepalive引數（lpvInBuffer）pass給這個API：
int WSAIoctl(
__in SOCKET s,
__in DWORD dwIoControlCode,
__in LPVOID lpvInBuffer,
__in DWORD cbInBuffer,
__out LPVOID lpvOutBuffer,
__in DWORD cbOutBuffer,
__out LPDWORD lpcbBytesReturned,
__in LPWSAOVERLAPPED lpOverlapped,
__in LPWSAOVERLAPPED_COMPLETION_ROUTINE lpCompletionRoutine
);
該文章裡面有如下描述：
SIO_KEEPALIVE_VALS (opcode setting: I, T==3)
Enables or disables the per-connection setting of the TCP keep-alive option which specifies the TCP keep-alive timeout and interval. For more information on the keep-alive option, see section 4.2.3.6 on the Requirements for Internet Hosts—Communication Layers specified in RFC 1122 available at the IETF website. The argument structure for SIO_KEEPALIVE_VALS is specified in the tcp_keepalive structure defined in the Mstcpip.h header file. This structure is defined as follows:
/* Argument structure for SIO_KEEPALIVE_VALS */
struct tcp_keepalive {
u_long onoff;
u_long keepalivetime;
u_long keepaliveinterval;
};
5、Named Pipe也有keepalive設定 嗎？
有。
參見如下文件：
http://support.microsoft.com/?id=137983
Named Pipes: Named Pipes are implemented in Server Message Block (SMB) layer on top of other transport protocols such as TCP/IP, NetBEUI, or NWLink IPX/SPX. A thin layer called NetBIOS is typically implemented between the SMB and the transport layer. Therefore, a convenient way of adjusting how long a non-responsive Named Pipes session has to wait before being closed is through adjusting the KeepAlive parameters of the relevant NetBIOS layer. For TCP/IP, the NetBIOS layer involved is NBT (NetBIOS over TCP), and the parameter involved is SessionKeepAlive in the following registry key:
KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Netbt\Parameters
6. 如何看SQL server 的連線？SQL server 會主動關閉連線嗎？如何看連線的idle時間？
SQL 2005查詢dm_exec_connections即可：
select session_id from sys.dm_exec_connections
Screen clipping taken: 20/08/2013 15:09
比較輸出裡面的last reads/writes 和現在時間可以大概知道一個連線的idle時間。
SQL server 2000則需要查詢sysprocesses表。Last_batch時間代表最近一次執行batch的時間。
SQL server 不會關閉一個正常的TCP連線。除非底層TCP報告錯誤。或者連線或接收資料出錯。
7、相關參考文件
SQL server TCP的keep alive
http://blogs.msdn.com/sql_protocols/archive/2006/03/09/546852.aspx
DBNetLib sets hard-coded KeepAliveTime and KeepAliveInterval
http://support.microsoft.com/kb/816649
TCP Keep-Alive Messages
http://msdn.microsoft.com/en-us/library/ms819735.aspx