SQL ServerSQL server connection KeepAlive[转]

1、什么是SQL
server TCP连接的keep Alive?

粗略说,keep
alive 是SQL server在建立每一个TCP 连接的时候,指定了TCP
协议的keepaliveinterval
和keepalivetime参数。那样对各类TCP连接,假设该连接空闲时间(没有其他数据交互)当先keepalivetime,TCP协议会自动发出keepalive
包检测一连存活与否。即使keepalive
检测次数超越注册表Tcp马克斯DataRetransmissions定义的值而对方仍然没有回答,那么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的界面如下:

SQL Server 1

该值保存在注册表如下地方:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft
SQL
Server\MSSQL.(版本[+实例])\MSSQLServer\SuperSocketNetLib\Tcp

SQL Server 2

注意SQL
server的Native client也有接近配置,不要和server side
的TCP配置搞混了:

SQL Server 3

Native client的
keep alive 配置保存在如下地点:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI(版本号)\tcp\Property(序号)

SQL Server 4

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 2008 R2
查询dm_exec_connections即可:

SELECT
    *
FROM [sys].[dm_exec_connections]

比较输出里面的last
reads/writes 和现行光阴足以大约知道一个接连的idle时间。

SQL server
2000则须求查询sysprocesses表。Last_batch时间代表近日一回举办batch的年月。

SQL server
不会关闭一个好端端的TCP连接。除非底层TCP报告错误。或者延续或接收数据出错。

 

 

 

 

相关文章