SQL 从池中获取连接之前超时时间已过
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1642252/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The timeout period elapsed prior to obtaining a connection from the pool
提问by Hawk
Our site works fine for 90% of the day, then during our peak hours when traffic is about twice as heavy as normal, everything slows down to a crawl. Page load times that are normally 1 second take 30 seconds. Checking our error logs, it looks like it may be a connection pool issue. We have 3 web servers connected to 1 sql server db. The SQL server is flying under 25% utilization on all cores.
我们的网站一天中有 90% 的时间都可以正常运行，然后在我们的高峰时段，当流量是正常情况的两倍左右时，一切都会变慢。通常为 1 秒的页面加载时间需要 30 秒。检查我们的错误日志，看起来可能是连接池问题。我们有 3 个 web 服务器连接到 1 个 sql server db。SQL 服务器在所有内核上的利用率都低于 25%。
I look at the User Connections counter on our SQL server and see that during our peak we have 400+ User Connections, but off-hours it is around 120+.
我查看了 SQL 服务器上的用户连接计数器，发现在高峰期我们有 400 多个用户连接，但在非工作时间约为 120 多个。
I am pretty sure we are just using whatever default settings MS comes with to deal with our app pool. What can I do to test to see if is an app pool issue? What are the negatives of increasing the app pool size to 1000 (and how do I do this?).
我很确定我们只是使用 MS 附带的任何默认设置来处理我们的应用程序池。我可以做些什么来测试是否是应用程序池问题？将应用程序池大小增加到 1000 有什么负面影响（我该怎么做？）。
Thanks!
谢谢！
回答by Slaggg
In my experience there are 3 primary types of timeouts you can receive from SQL Server:
根据我的经验，您可以从 SQL Server 收到 3 种主要类型的超时：
1) InvalidOperationException- A failure for the client to obtain a pooled connection from its own pool before the timeout specified on the command string (default 15 seconds). The client's pool is at its maximum size, and all pooled connections are in use and stay in use before the timeout elapses.
2) SQLException- Connection Timeout. The client's connection pool is creating a new connection to the database, but the database does not respond before the timeout specified in the command string (default 15 seconds).
3) SQLException- Command Timeout. A connection was obtained, but the time taken for the SQL statement to exercise the command exceeded the timeout specified on the command's CommandTimeout property (default 30 seconds)
Your circumstances of a server performing normally until load is added sounds like case #1. I've found the timeouts come very fast - usually 2 seconds.
I've found the solution to this is to increase the maximum threads in SQL Server. The default is zero - let SQL Server decide. I've seen cases where a stout server sits with little resource use while it has restricted itself by allocating too few threads.
You can increase the max threads setting with this transact-sql:
1) InvalidOperationException- 客户端在命令字符串中指定的超时（默认为 15 秒）之前从其自己的池中获取池连接失败。客户端的池处于其最大大小，并且所有池连接都在使用中，并在超时结束之前保持使用状态。
2) SQLException- 连接超时。客户端的连接池正在创建到数据库的新连接，但数据库在命令字符串中指定的超时（默认为 15 秒）之前没有响应。
3) SQLException- 命令超时。已获得连接，但 SQL 语句执行命令所用的时间超过了命令的 CommandTimeout 属性中指定的超时时间（默认为 30 秒）
您的服务器在添加负载之前正常运行的情况听起来像案例 #1。我发现超时来得非常快——通常是 2 秒。
我发现解决方案是增加 SQL Server 中的最大线程数。默认值为零 - 让 SQL Server 决定。我见过这样的情况，即一个粗壮的服务器占用很少的资源，而它却通过分配太少的线程来限制自己。
您可以使用此 transact-sql 增加最大线程设置：
sp_configure 'max worker threads', 8192
go
Reconfigure
Then, restart your SQL Service.
然后，重新启动 SQL 服务。
BTW, you can see how many threads are currently allocated by SQL Server with this command:
顺便说一句，您可以使用以下命令查看 SQL Server 当前分配了多少线程：
select sum(current_workers_count) from sys.dm_os_schedulers
This threading setting makes an enormous difference in how SQL Server performs under many connections. SQL Server becomes very unresponsive once it runs out of threads.
此线程设置对 SQL Server 在许多连接下的执行方式产生巨大影响。一旦线程用完，SQL Server 就会变得非常无响应。
回答by Darin Dimitrov
This could be related to sql connections not being properly disposed (returned to the pool). Make sure you are calling SqlConnection.Dispose.
这可能与未正确处理 sql 连接（返回到池中）有关。确保您正在调用SqlConnection.Dispose.
回答by Andomar
This could be because the pool of SQL Connections is exhausted (this is different from the app pool.) You can check that by increasing the pool sizethrough the connection string:
这可能是因为 SQL 连接池已耗尽（这与应用程序池不同。）您可以通过连接字符串增加池大小来检查：
Integrated Security=SSPI;Initial Catalog=northwind;Max Pool Size=100;
But more likely, your database can't keep up with the stream of incoming queries. This causes connections to be waiting for their query to end. Adding more connections will help against burst requests, but not against sustained high traffic.
但更有可能的是，您的数据库跟不上传入的查询流。这会导致连接等待其查询结束。添加更多连接将有助于应对突发请求，但不能应对持续的高流量。
Here's some suggestions to improve the performance of your SQL Server under sustained high load:
以下是在持续高负载下提高 SQL Server 性能的一些建议：
- Throw hardware at the problem (especially RAM on the SQL Server)
- Attach SQL Server Profiler to the server, get a trace of one high-load period, and follow its suggested indexes
- From the trace log, examine long running queries, and improve those together with a T-SQL developer
- 把硬件放在问题上（尤其是 SQL Server 上的 RAM）
- 将 SQL Server Profiler 附加到服务器，跟踪一个高负载时期，并遵循其建议的索引
- 从跟踪日志中，检查长时间运行的查询，并与 T-SQL 开发人员一起改进这些查询
Good luck, these things can be pretty complex!
祝你好运，这些事情可能非常复杂！
回答by Jed
When I received this error:
当我收到此错误时：
The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
在从池中获取连接之前超时时间已过。这可能是因为所有池连接都在使用中并且达到了最大池大小
It was due to the fact that I was using the SqlCommand.ExecuteQuery()method instead of SqlCommand.ExecuteNonQuery().
这是因为我使用的是SqlCommand.ExecuteQuery()方法而不是SqlCommand.ExecuteNonQuery().
For example: My original stored procdure call look something like this:
例如：我原来的存储过程调用看起来像这样：
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "InsertSproc";
cmd.Parameters.AddWithValue("@Value", myValue);
cmd.Parameters.AddWithValue("@LoggedDate", myDate);
DatabaseManager.instance.ExecuteQuery(cmd);
}
The code above is what threw the exception. However, changing the call to use ExecuteNonQuery() fixed the problem:
上面的代码是抛出异常的原因。但是，将调用更改为使用 ExecuteNonQuery() 解决了该问题：
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "InsertSproc";
cmd.Parameters.AddWithValue("@Value", myValue);
cmd.Parameters.AddWithValue("@LoggedDate", myDate);
DatabaseManager.instance.ExecuteNonQuery(cmd);
}
回答by Albert
I had this issue with Powershell and back to back queries (invoke-sqlcmdfollowed by another invoke-sqlcmd). Both queries involved data modifications. Resolved by adding -connectiontimeout 1to the parameter call.
我在 Powershell 和背靠背查询（invoke-sqlcmd后跟另一个invoke-sqlcmd）中遇到了这个问题。这两个查询都涉及数据修改。通过在参数调用中添加-connectiontimeout 1来解决。
Example:
例子：
invoke-sqlcmd "insert into testdb..tab1 (cname) select 'x'" -connectiontimeout 1
invoke-sqlcmd "update testdb..tab1 set ctr=ctr+1 where cname='aaa'"
Length of timeout may vary depending on number of rows affected.
超时长度可能因受影响的行数而异。
