SQL 增加最大池大小和性能

Increasing the Max Pool Size and performance

提问by IT researcher

In my ASP.net website I have a connection to a SQL Server Express database. And sometimes I do get lot of errors like

在我的 ASP.net 网站中，我连接到 SQL Server Express 数据库。有时我确实会收到很多错误，例如

System.InvalidOperationException: Timeout expired. 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.

System.InvalidOperationException：超时已过期。在从池中获取连接之前超时时间已过。这可能是因为所有池连接都在使用中并且达到了最大池大小。

After searching for the error I found that it may be due to unclosed SQL Server connections. But I have used SQL Server connections properly and I have disposed it properly.I have used using-statement to dispose the connection. In my application I get a lot of requests (connections to SQL Server database) at some peak time of the day.

搜索错误后，我发现这可能是由于未关闭的 SQL Server 连接。但是我已经正确使用了 SQL Server 连接并且我已经正确地处理了它。我已经使用 using-statement 来处理连接。在我的应用程序中，我在一天中的某个高峰时间收到了很多请求（连接到 SQL Server 数据库）。

So I am planning to increase the max pool size. Now I have not specified it in my connection string. So it will be 100 (default). I am planning it to increase to 20000 so that I won't get the error.

所以我打算增加最大池大小。现在我没有在我的连接字符串中指定它。所以它将是 100（默认）。我计划将其增加到 20000，以免出现错误。

So does increasing to the max pool size to this number will cause any problem? Does increasing the max pool will cause any performance problem?

那么将最大池大小增加到这个数字会导致任何问题吗？增加最大池会导致任何性能问题吗？

EDIT: Below is a sample of how i used Usingin my code

编辑：下面是Using我如何在我的代码中使用的示例

Using con1 As New SqlConnection
    con1.ConnectionString = "" //conn string here
    con1.Open()
    doSomething()      
End Using 

Using con2 As New SqlConnection
                con2.ConnectionString = "" //conn string here
                con2.Open()
                doSomething()
            Using con3 As New SqlConnection
               con3.ConnectionString = "" //conn string here
               con3.Open()
               doSomething()       
            End Using

End Using 

Catch ex As Exception

End Try

EDIT:I just tested by setting Max pool size to 20000 in my application to check if max pool is really causing the problem.I was expecting to not get this exception any more. But it didn't solve the problem or may not be the reason of the error. After setting max pool also i got above error about 50 times in a duration of 3 hours. So I doubt is it really due to pool size? The error message states that "This may have occurred because all pooled connections were in use and max pool size was reached". So is there any other case where the same error message shown? Please help.

编辑：我刚刚通过在我的应用程序中将最大池大小设置为 20000 来检查最大池是否真的导致了问题。我希望不会再遇到这个异常。但它没有解决问题或可能不是错误的原因。设置最大池后，我在 3 小时内也出现了 50 次以上错误。所以我怀疑这真的是由于池大小吗？错误消息指出“这可能是因为所有池连接都在使用中并且达到了最大池大小”。那么是否还有其他情况显示相同的错误消息？请帮忙。

采纳答案by usr

You can increase the pool size if you want. There are two downsides:

如果需要，您可以增加池大小。有两个缺点：

1. More connections mean more resource usage.
2. SQL Server has a connection limit of about 30k connections. When you exhaust it you will lose availability.

1. 更多的连接意味着更多的资源使用。
2. SQL Server 的连接限制约为 30k 个连接。当您用完它时，您将失去可用性。

I recommend going higher more slowly. Don't increase the pool size to 20k immediately. Think about what 20k connections would mean: It would mean roughly 20k threads (or less if a single thread uses more than one connection) executing at the same time. If you are in that spot you probably have load problems.

我建议更慢地走得更高。不要立即将池大小增加到 20k。想想 20k 连接意味着什么：这意味着大约 20k 个线程（如果单个线程使用多个连接，则更少）同时执行。如果你在那个地方，你可能有负载问题。

Why do you even need that many connections? For web apps, you usually have only one connection open at the same time per running request. A few hundred should be enough for most cases.

为什么你甚至需要那么多连接？对于 Web 应用程序，每个运行请求通常只能同时打开一个连接。大多数情况下，几百应该足够了。

Try to shorten the duration that each connection must be open.

尝试缩短每个连接必须打开的持续时间。