负载下的SQL连接错误40 [英] Sql Connection Error 40 Under Load
问题描述
我的Web服务器上的数据库连接正常工作，但是当我触发一个写沉重的Windows服务时，我将随机得到与SQL Server建立连接时发生了与网络相关或特定于实例的错误。错误40 。
My database connections from my web servers work fine normally, but when a write heavy windows service I have triggers, I will randomly get "A network-related or instance-specific error occurred while establishing a connection to SQL Server. Error 40." from my webservers.
我尝试关闭Web服务器的连接池，因为它们与Windows服务共享相同的连接字符串，但我对此感到茫然
I have tried turning connection pooling off for my web servers since they share the same connection string as the windows service, but I am at a loss as to what is still causing this.
我的SqlConnection对象也都包装在using语句中，因此它们也被丢弃了。
My SqlConnection objects are all wrapped in using statements as well, so they are being disposed.
Dapper当前用于查询扩展方法。
Dapper is being used currently for the query extension method.
连接字符串示例：数据源=服务器；初始目录= DBNAME；用户ID = USERNAME； password = PASSWORD; Pooling = False;
Connection String example: "Data Source=SERVER;initial catalog=DBNAME;user id=USERNAME;password=PASSWORD;Pooling=False;"
using (IDbConnection connection = new SqlConnection(_connectionString))
{
return connection.Query<TotalPosts>("analytics.TotalPosts_Sel", new { AccountId = accountId }, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
推荐答案
好的，我知道这个问题已经有好几年了，但是当我在2019年使用.NET 4.8遇到相同的问题时，我将发布该问题以及解决方法。
OK, I realize this question is several years old, but as I hit the same problem in 2019 with .NET 4.8, I will post the problem, and a workaround.
您的问题是由于以下原因引起的创建和关闭连接的速度以及保持打开状态的连接数。您正在低级数据库驱动程序/库中造成瓶颈。
Your problem is caused because of the speed you create and close connections and the number of connections that remain opened. You are causing a bottleneck in the low level database driver/library.
一些有用的信息可以理解该问题：
Some useful information to understand the problem:
使用连接池也会带来其他问题：
Using connection pooling can bring other issues as well:
< a href = http://florianreischl.blogspot.com/2011/08/adonet-connection-pooling-internals-and.html rel = nofollow noreferrer> http://florianreischl.blogspot.com/2011/08 /adonet-connection-pooling-internals-and.html
我的解决方法是将线程休眠20毫秒。每次迭代之后。
My workaround was to sleep the thread for 20 ms. after each iteration. Dirty, but worked.
问候，
MarianoC。
这篇关于负载下的SQL连接错误40的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
