使用Windows 2008中的经典ADO进行SQL 2005连接会产生奇怪的性能 [英] SQL 2005 connection using classic ADO from Windows 2008 yields odd performance

问题描述

更新：请查看下面的更新，因为我在运行时会在底部添加更多信息。

UPDATE: Please see the updates below, as I'm adding more information to the bottom as I run into it.

我正在努力建立一个新的运行Windows Server 2008的Web服务器，并通过SQL Server 2005连接到另一台Windows 2003 Server。尝试将查询从服务器运行到SQL Server 2005服务器时，我遇到了一些奇怪的结果。基本上，如果返回结果，其他任何查询都会有625毫秒的固定开销，但如果是非查询，则没有开销：

I'm working on setting up a new web server running Windows Server 2008, connecting to another Windows 2003 Server with SQL Server 2005. I'm running into some weird results when trying to run queries from the server to the SQL Server 2005 server. Basically, ever other query has a fixed 625 millisecond overhead if results are returned, but no overhead when it's a non-query:

我将让我的示例VB脚本代码说话：

I'll let my sample VB script code do the talking:

set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLNCLI;Server=thesqlserver;Integrated Security=SSPI;initial catalog=thedatabase"

t = Timer

for i = 1 to 20
    set rs = cn.Execute("select getdate()") //Note getting recordset here
    s = s & (timer - t) & VbNewLine
    t = Timer
next

for i = 1 to 20
    cn.Execute("select getdate()") //Note no recordset returned here
    s = s & (timer - t) & VbNewLine
    t = Timer
next

set fso = CreateObject("Scripting.FileSystemObject")
set file = fso.OpenTextFile("c:\results.txt", 2, True)

file.WriteLine(s)
file.Close

实际结果如下：

First Set - Returning recordset
0
0.625
0
0.6640625
0
0.625
0
0.625
0
0.625
0
0.625
0
0.625
0
0.625
0
0.625
0
0.6171875
Second Set - Non-query execution
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0.015625
0
0
0
0
0

我很困惑。任何帮助将不胜感激。

I'm baffled. Any help would be greatly appreciated.

更新：

为响应Kevin的回答，我尝试运行相同的命令直接从SQL Server编写脚本。结果是开销一直为零。两台服务器之间的连接是连接到每台服务器的第二个NIC的交叉网线。

In response to Kevin's answer, I tried running the same script directly from the SQL server. The result was zero overhead all the way down. The connection between the two servers is a crossover network cable connected to the second NICs of each server.

更新：

新信息。我尝试在 Server =参数中更改服务器名称，并且使用内部（交叉电缆）IP时，它从每个偶数请求625毫秒增加到甚至偶数请求4,550毫秒（奇数请求仍为零）。然后，将其更改为公用IP可使开销完全消失！我必须在内部NIC上配置错误。

New information. I tried changing the servername in the "Server=" argument, and when using the internal (crossover cable) IP, it went from 625 milliseconds every even request, to 4,550 milliseconds ever even request (with the odd requests still at zero). Then, changing it to the public IP made the overhead go away completely! I must have something configured incorrectly on the internal NIC.

更新：

在网络上执行Wireshark跟踪活动，似乎偶数请求正在执行某种Windows质询/响应，并且挂起了。这些服务器不在域中，并且如上所述，我正在使用集成安全性。不过，只要我使用外部IP，使用集成安全性就可以了。此外，删除集成安全性，并在使用内部IP时显式提供凭据也可以避免开销。

Doing a Wireshark trace on the network activity, it appears the even requests are doing some sort of Windows challenge/response, and that's hanging. These servers are not on a domain, and I'm using integrated security as shown above. However, again, using integrated security is fine as long as I'm hitting the external IP. Also, removing integrated security, and explicitly providing credentials when using the internal IP also gets rid of the overhead.

推荐答案

我能找到的唯一解决方案是引用外部IP与内部IP。我不确定这是否会对性能产生影响（例如，数据包现在需要花费更长的时间），但至少可以正常工作。

For now, the only solution I can find is to reference the external IP vs. the internal IP. I'm not sure if this has any performance impact (i.e. is the route the packets take longer now), but at least it's working.

这篇关于使用Windows 2008中的经典ADO进行SQL 2005连接会产生奇怪的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！