与 SQL Server 的初始连接连接速度很慢。为什么？
我遇到过这样的情况：在两个站点上安装了 C# 应用程序，与 SQL Server 的初始连接非常慢。我编写了一个测试应用程序来验证速度下降的位置以及它是在第一个 SQLConnection.Open 语句上。通过命名管道建立与服务器的连接大约需要 41 秒。我们认为这可能是 DNS 问题，但使用 TCP/IP 连接时速度同样慢。建立初始连接后，连接将被池化并且应用程序正常响应。工作站和服务器都是不错的机器，运行 Windows 7 Pro、Core 2 Duo 3.16 Ghz 和 4 GB RAM。我确实在微软论坛上找到了以下文章：
关闭 LLMNR 协议确实将初始连接时间缩短了大约一半，达到 21 秒。然而，获得与 SQL Server 的初始连接仍然需要很长时间。唯一与我们的规范略有不同的是，本例中的 DNS 是通过路由器而不是实际的服务器完成的。到目前为止，这种情况只发生在两个地方，其他地方运行没有问题。任何帮助将不胜感激。
谢谢你， 丹尼斯
I have run into a situation with a C# application installed at two sites where the initial connection to SQL Server is extremely slow. I wrote a test application to verify where the slowdown occurs and it is on the first SQLConnection.Open statement. It was taking about 41 seconds to establish a connection to the server via named pipes. We thought it might be a DNS issue but its just as slow using a TCP/IP connection. After the initial connection is made the connection is pooled and the application responds normally. Both the workstation and the server are decent machines running Windows 7 Pro, Core 2 Duo 3.16 Ghz with 4 gig of Ram. I did find the following article on a microsoft forum:
Turning off the LLMNR protocol did cut the initial connection time in about half to 21 seconds. However, this is still a long time to get an initial connection to SQL Server. The only things slightly different from our norm is that DNS in this case is done thru a router and not an actual server. This has occurred at only two places so far, others run with no issues. Any help would be appreciated.
Thank You,
Dennis
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（15）
在连接字符串的服务器前面添加
np:这将变为
Server=np:server\instance并强制使用命名管道而不是默认的 TCP。我可能可以在 TCP 之前更改使用命名管道的优先级...但我不想在服务器上搞乱它。
In front of the server on the connection string, add
np:This becomes
Server=np:server\instanceand forces the Named Pipes instead of the default of TCP.I could of probably changed the priority to use Named Pipes before TCP... but I did not want to mess with that on the server.
我看到了类似的问题，但不确定是否与你的相同。就我而言，不仅仅是 C# 程序建立 SQL 连接速度慢。任何连接到 SQL Server 的工具也会遇到缓慢的情况。此外，一旦与 SQL Server 建立初始连接，任何后续连接都会在一段时间内正常。
原因是 SQL Server 使用了许多托管程序集。它正在尝试验证分配给程序集的证书。它正在连接 crl.microsoft.com。我的 SQL 服务器没有互联网连接。因此，它等待超时。
解决方案是让我的 SQL 服务器能够访问互联网或禁用 CRL 检查。您可以转到 SQL Server 计算机。选择工具>互联网选项>先进的。检查安全节点下发布者的证书吊销是否勾选。如果已选中，请取消选中。
I saw similar problem but not sure it's the same as yours. In my case, it's not just the C# program being slow to make the SQL connection. It's any tools connecting to SQL server also experiencing the slowness. Also, once the initial connection made to the SQL server, any subsequent connections are fine for a period of time.
The reason was that SQL server was using a number of managed assemblies. It's trying to verify the cerfiticates assigned to the assemblies. It's connecting the crl.microsoft.com. My SQL server didn't have the internet connection. So, it waits for the timeout.
The solution was to make my SQL server to have internet access or disable the CRL checking. You can go to the SQL server machine. Select Tools > Internet Options > Advanced. Check if the publisher's certificate revocation under the secuity node is checked or not. If it's checked, uncheck it.
我尝试指定连接字符串，其中集成安全性 = false（意味着用户 ID 和密码位于连接字符串中）和加密 = false（只需 100% 确定未使用 SSL 加密）。这些规范似乎没有帮助，我无法使用 TCP/IP 网络库 (NetworkLibrary = "dbmssocn") 显式获得连接。这可能与服务器防火墙和端口未打开有关。我切换回命名管道，并将命名管道网络库规范放入连接字符串中（NetworkLibrary =“dbnmpntw”）。进行此更改后，连接立即建立。
I tried specifying the connection string with integrated security = false (meaning the user id and password are in the connection string) and encrypt = false (just be be 100% sure SSL encryption is not being used). These specifications did not seem to help and I could not get a connection explicitly using the TCP/IP network library (NetworkLibrary = "dbmssocn"). This could have to do with the server firewall and the port not being open. I switched back to named pipes and put the named pipe network library specification in the connection string this time (NetworkLibrary = "dbnmpntw"). After this change, the connection was made instantaneously.
使用 IP 地址（而不是主机名）建立与 SQL Server 的集成安全连接将阻止使用 Kerberos 身份验证。在这种情况下，请检查 SQL Server 和域控制器之间的连接。
如果您使用主机名（而不是 IP 地址）进行连接，则 Kerbos 正在发挥作用，在这种情况下，您需要检查客户端计算机与域控制器的连接。
Establishing an integrated security connection to SQL Server using the IP address (instead of a host name), will prevent Kerberos authentication being used. In this scenario check the connection between the SQL Server and the domain controller.
If you connect using the host name (not the IP Address) Kerbos is in play, in this case you need to check the client machine's connection to the domain controller.
我们遇到了同样的问题，结果证明我们的远程托管 Active Directory 服务器是罪魁祸首。
我们创建了一个站点本地 Active Directory 服务器来复制远程托管的 AD 主服务器，然后所有缓慢的 SQL Server 集成安全身份验证性能问题都消失了。
我希望这有帮助。
We had the same problem, and it turned out that our remotely-hosted Active Directory server was to blame.
We created a site-local Active Directory server to replicate the remotely hosted AD master and then all of our slow SQL Server integrated security authentication performance issues went away.
I hope that helps.
是的，当您使用集成安全性时，Active Directory 可能是罪魁祸首，整个网络也可能是罪魁祸首，因为这一切都取决于它。我能想到的另一件事是您正在使用的 SQL Server 版本。
另外，当SQL Server长时间不使用时，它的行为类似于IIS，使工作进程进入睡眠状态，因此当您再次联系服务器时，取决于机器（我们可以看到这些有桌面机器配置） ），工作进程恢复并准备工作需要一些时间。
Yeah when you're using integrated security, Active Directory can be the one to blame, also the overall network since it all depends on it. Another thing I could think of is the edition of SQL Server you're using.
Also, when SQL Server is not being used by long periods of time, it behaves similar to IIS, putting the worker process to sleep, so when you contact the server again, depending on the machine (we can see that these have desktop machines configurations), it will take some time while the worker process gets back to life and is ready to work.
你确实检查过我认为的显而易见的事情了吗？ UDP 端口 1434 在防火墙上打开，并且浏览器服务正在运行......否则将需要大约 40 秒进行身份验证。
You did check the obvious I take it? That UDP port 1434 is open on the firewall and the browser service is running .... would take about 40 sec to authenticate otherwise.
我也有同样的问题。经过对 google 和 stackoverflow 的大力研究，我更改了客户端计算机的主机文件（在 Windows 中，位于 C:\Windows\System32\drivers\etc）。我在此文件和 Viola! 中输入了主机的 IP 地址和服务器名称。事情变得超级快！
正如 stackoverflow 中的每个人所说，这是计算机在 DNS 服务中查找服务器名称的地址并超时。
我所做的步骤如下。如果没有其他效果，请尝试一下。
1. 打开命令提示符并 ping 远程数据库所在的服务器。为此，请输入以下命令：
这里我的远程计算机名称是 Juno。所以我应该像这样 ping 。
此命令将 ping 我的服务器并返回 IP 地址，如下所示。
正如您所看到的，服务器的 IP 地址位于括号之间。
复制 IP 地址。
2.现在使用提升的记事本打开主机文件（以管理员身份运行）。
在主机文件的末尾，会有一些像这样的行：
在最底部（此处，在 localhost 之后），键入 # 并输入我们刚刚获得的服务器的 ip 地址，前面是服务器名称。
因此，hosts 文件应该如下所示。
就这样！
了解更多有关编辑主机文件的信息，请单击此处
I had the same problem. After vigorous research on google and stackoverflow, i changed the client computer's hosts file (In windows,located at C:\Windows\System32\drivers\etc).I entered my host's ip address and server name in this file and Viola!. Things became superfast!
Like everybody in stackoverflow said, it was the computer looking up the servername's address in the DNS service and getting a timeout.
The steps i did follows.Give it a try if nothing else works.
1.Open the command prompt and ping your server,where the remote database is located. To do this enter the following command:
Here my remote computer name was Juno. So i should ping like this.
This command will ping my server and returns ip address like this.
As you can see the ip address of the server is between the brackets.
Copy the ip address.
2.Now Open the Hosts file with elevated Notepad(Run as administrator).
At the end of the hosts file,there will be some lines like these:
At the very bottom (here,after the localhost),type # and enter the ip address of the server we just obtained preceeded by server name.
So, the hosts file should look like this.
Annd there you go!
For more info about editing your hosts file,click here
还有其他方法可以创建与 SQL 数据库的连接。 的教程
尝试查找使用sqlconnection myCon = new sqlconnection(details);
，而不是创建对象来实例化连接。
there are other methods of creating connections to SQL databases. Try finding a tutorial which uses the
sqlconnection myCon = new sqlconnection(details);instead of creating an object to instance the connection.
我没有具体的答案，但是您是否尝试过运行 SQL Profiler 以从 SQL 的角度查看发生了什么？
您是否还尝试过使用与您的连接相同的凭据连接到 SQL？
另一方面，它的级别可能要低得多，但我总是先做容易检查的事情。
祝你好运。
I don't have a concrete answer but have you tried running SQL Profiler to see what is going on from SQL's point of view?
Also have you tried connecting to SQL using the same credentials as your connection?
On the other hand it may all be much lower level but I always do the easy to check stuff first.
Good Luck.
听起来要么名称解析需要一段时间，要么身份验证需要一段时间。发生初始解析或身份验证后，服务器会缓存端点的详细信息，因此在缓存过期之前不需要再次执行查找。
作为一个实验，尝试从客户端框中 ping 服务器 - 如果这需要很长时间才能解析主机名，那么您就找到了罪魁祸首：主机名查找（DNS 或 NBNS）。另一种选择是使用主机 IP 地址而不是名称。因此，如果您在服务器
sql2005-01上有一个名为bob的 SQL Server 实例，并且该服务器的 IP 为192.168.200.12，然后尝试连接到192.168.200.12\bob而不是sql2005-01\bob。身份验证更难以解决，但您可以使用 SQL Server 上的 runas 对其进行测试（例如，runas /user:domain\user cmd 来查看是否可以打开命令提示符，如下所示）您尝试验证身份的用户。
It sounds like either name resolution is taking a while or authentication is taking a while. After the initial resolution or authentication's occured, then details of endpoints are cached by the server so it doesn't need to perform the lookups again until caches expire.
As an experiment, try ping'ing the server from the client box -- if this takes a long time to resolve the hostname, then you've found the culprit: hostname lookups (DNS or NBNS). Another alternative is to use the host IP address rather than name. So if you've got a SQL Server named instance of
bobon serversql2005-01, and this server has an IP of192.168.200.12, then try connecting to192.168.200.12\bobinstead ofsql2005-01\bob.Authentication's more awkward to resolve, but you might be able to test it with runas on the SQL server box (e.g.,
runas /user:domain\user cmdto see if you can open up a command prompt as the user you're attempting to authenticate as.这很容易是连接或身份验证的问题，第一次连接需要更长的时间是正常的，因为 ADO.NET 有连接池来避免长时间的连接时间。
有很多因素会影响速度：
- TCP/IP配置
- 服务器端的路由器
- ETC。
It could easily be a problem with either the connection or the authentication, it is normal that the first connection takes longer as ADO.NET has connection pool to avoid that long connection time.
There are many factors that can influence the speed:
- TCP/IP configuration
- Routers on the server-side
- etc.
不过，如果您遇到此问题，请参阅以下解决方案：
根本原因
我们在 Win7 VDI 上看到的问题可能是由于与计算机连接的网络硬件设备造成的。如果网络设备不支持 TCP/IP 扩展，则性能会很慢。
解决方案
禁用 TCP 自动调整级别。请按照以下步骤操作：
1）使用管理员权限打开命令提示符（以管理员身份运行）
2) 输入“netsh interface tcp set global autotuninglevel=disabled”
3）运行上述命令后重新启动机器。
有关此命令的其他信息，请访问链接“http://support.microsoft.com/kb/935400”
Still if you are facing the issue, please see blow resolution:
Root Cause
The issue which we were seeing on Win7 VDIs could be due to the Network hardware device connected with the machine. If TCP/IP scaling is not supported by the network device then the performance will be slow.
Solution
Disable auto tuning level of the TCP. Please follow below steps:
1) Open command Prompt with admin right (Run as Admin)
2) Type “netsh interface tcp set global autotuninglevel=disabled”
3) After running above command restart the machine.
For other information on this command, visit link “http://support.microsoft.com/kb/935400”
就我而言，答案是：
In my case the answer was: