Windows 2012 R2访问数据库缓慢（Windows 也适用）（局域网）

解决办法

前段时间公司新采购了一台ibm的服务器，装的是 windows server 2012R2, 在做完项目迁移后，发现项目访问数据库缓慢，于是逐项查找原因，最后终于找到解决办法：

以Administrator权限执行命令行(开始菜单中右键点击命令行程序，右键菜单中有以管理员身份执行的选型)，并在命令行中执行一下命令(关闭自动调节)：

netsh interface tcp set global autotuninglevel=disabled

译文：

NETSH接口TCP设置全局自动转换级别=禁用

 

 

打开自动调节的命令是：

　　netsh interface tcp set global autotuninglevel=normal

问题原因

原因在于从vista开始，微软在TCP/IP协议栈里新加了一个叫做“Window Auto-Tuning”的功能。这个功能本身的目的是为了让操作系统根据网络的实时性能（比如响应时间）来动态调整网络上传输的数据窗口的大小，从而达到实时优化网络性能的目的。但是，在某种情况下（具体是怎样的一个环境，目前我也不清楚），这个功能反而会使远程桌面的响应变的非常的慢。
通过把autotuninglevel设置成disabled，就可以让数据窗口保持默认值。如果设置成highlyrestriected的话，那么就是非常保守地来调整这个数据窗口大小（不到万不得已，还是使用默认值）。
设置命令如下:
netsh interface tcp set global autotuninglevel=disabled （或者highlyrestricted）。

 

补充内容:(可选择性阅读)

理解：http://www.askmaclean.com/archives/how-to-disable-windows-tcpip-auto-tuning.html

Windows上如何禁用TCP/IP自动调优特性

2012/01/05 BY MACLEAN LIU LEAVE A COMMENT

 

 

 

之前有网友在t.askmaclean.com提问版上提问关于远程PL/SQL Developer连接Oracle数据库发现查询v$session会hang住的问题，而本地连接(local connection)则可以正常查询。

 

分析了问题相关的10046 trace和MOS上的一些案例发现可能是由Bug 9087501: SELECT FROM V$SESSION AND SOME OTHER DICTIONARY VIEWS HANG FROM A REMOTE CLIENT      所引起， 该Bug Note 还指出在Windows Vista上存在因为操作系统默认启用了TCP/IP Auto-tuning(TCP Window Scaling)从而可能导致使用如PL/SQL Developer这样的客户端登录数据库后某些查询Hang住的问题。 而在其他Windows版本中(如Win 7)没有默认启用该TCP/IP Auto-tuning特性，所以默认不存在该问题； 看起来Vista还真是一个失败的作品。

 

该TCP Window Scaling(接收窗口自动调谐级别)用以优化TCP网络的吞吐量，更多信息参考一下文本:

 

TCP Window Scaling
For larger window sizes to accommodate high-speed transmission paths, RFC 1323 (ietf.org/rfc/rfc1323.txt) defines window scaling that allows a receiver to advertise a window size larger than 65,535 bytes. A TCP Window Scale option includes a window scaling factor that, when combined with the 16-bit Window field in the TCP header, can increase the receive window size to a maximum of approximately 1GB. The Window Scale option is sent only in synchronize (SYN) segments during the connection establishment process. Both TCP peers can indicate different window scaling factors to use for their receive window sizes. By allowing a sender to send more data on a connection, TCP window scaling allows TCP nodes to better utilize some types of transmission paths with high BDPs.
Although the receive window size is important for TCP throughput, another important factor for determining the optimal TCP throughput is how fast the application retrieves the accumulated data in the receive window (the application retrieve rate). If the application does not retrieve the data, the receive window can begin to fill, causing the receiver to advertise a smaller current window size. In the extreme case, the entire maximum receive window is filled, causing the receiver to advertise a window size of 0 bytes. In this case, the sender must stop sending data until the receive window has been cleared. Therefore, to optimize TCP throughput, the TCP receive window for a connection should be set to a value that reflects both the BDP of the connection’s transmission path and the application retrieve rate.
Even if you could correctly determine both the BDP and the application retrieve rate, they can change over time. The BDP rate can vary based on the congestion in the transmission path and the app retrieve rate can vary based on the number of connections on which the app is receiving data.

 

 

为了在Windows Vista上正常使用受此影响的网络客户端程序，我们需要手动禁用该TCP/IP Auto-tuning特性，步骤如下：

 

1. 点击开始->ALL Programs -> Accessories(附件) -> 右键以管理员身份运行Command Prompt(命令提示符)

 

2.  输入命令”netsh interface tcp show global”

C:\Windows\system32>netsh interface tcp show global
Querying active state...

TCP Global Parameters
----------------------------------------------
Receive-Side Scaling State          : enabled
Chimney Offload State               : automatic
NetDMA State                        : enabled
Direct Cache Acess (DCA)            : disabled
Receive Window Auto-Tuning Level    : normal
Add-On Congestion Control Provider  : none
ECN Capability                      : disabled
RFC 1323 Timestamps                 : disabled

若命令输出的Receive Window Auto-Tuning Level(接收窗口自动调谐级别)值 为normal则说明系统启用了Auto-Tuning特性。

 

3. 输入命令netsh interface tcp set global autotuninglevel=disabled 以禁用Auto-Tuning特性：

 

C:\Windows\system32>netsh interface tcp set global autotuninglevel=disabled
Ok.

C:\Windows\system32>netsh interface tcp show global
Querying active state...

TCP Global Parameters
----------------------------------------------
Receive-Side Scaling State          : enabled
Chimney Offload State               : automatic
NetDMA State                        : enabled
Direct Cache Acess (DCA)            : disabled
Receive Window Auto-Tuning Level    : disabled
Add-On Congestion Control Provider  : none
ECN Capability                      : disabled
RFC 1323 Timestamps                 : disabled

 

4.  重启计算机，以便设置生效(restart computer)

 

5. 如今后希望再次启用该autotuning特性则可以在命令行中输入”netsh interface tcp set global autotuninglevel=normal”命令，并重启计算机

 

C:\Windows\system32>netsh interface tcp set global autotuninglevel=normal
Ok.

C:\Windows\system32>netsh interface tcp show global
Querying active state...

TCP Global Parameters
----------------------------------------------
Receive-Side Scaling State          : enabled
Chimney Offload State               : automatic
NetDMA State                        : enabled
Direct Cache Acess (DCA)            : disabled
Receive Window Auto-Tuning Level    : normal
Add-On Congestion Control Provider  : none
ECN Capability                      : disabled
RFC 1323 Timestamps                 : disabled

Reference:
http://support.microsoft.com/kb/934430
http://technet.microsoft.com/en-us/magazine/2007.01.cableguy.aspx