如何解决 ASP.NET 和 SQL Server 之间的连接池问题? [英] How can I solve a connection pool problem between ASP.NET and SQL Server?

问题描述

最近几天，我们在我们的网站上看到太多这样的错误信息:

The last few days we see this error message in our website too much:

"超时已过.超时时间在获得之前经过来自池的连接.这可能已经发生，因为所有汇集正在使用连接和最大池已达到大小."

"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."

我们有一段时间没有更改代码中的任何内容.我修改了代码以检查未关闭的打开连接，但发现一切正常.

We have not changed anything in our code in a while. I revised the code to check open connections which didn't close, but found everything to be fine.

• 我该如何解决这个问题?

• How can I solve this?

我需要编辑这个池吗?

如何编辑此池的最大连接数?

How can I edit this pool's max number of connections?

高流量网站的推荐值是多少?

What is the recommended value for a high traffic website?

我需要在 IIS 中编辑某些内容吗?

Do I need to edit something in IIS?

我发现活动连接数在15到31之间，我发现SQL server中配置的最大允许连接数超过3200个连接，31太多了还是应该在ASP中编辑一些东西.NET 配置?

I found that the number of active connections are anywhere from 15 to 31, and I found that the max allowed number of connections configured in SQL server is more than 3200 connections, is 31 too many or should I edit something in the ASP.NET configration?

推荐答案

在大多数情况下，连接池问题与连接泄漏有关.您的应用程序可能没有正确且一致地关闭其数据库连接.当您让连接保持打开状态时，它们将一直处于阻塞状态，直到 .NET 垃圾收集器通过调用它们的 Finalize() 方法为您关闭它们.

In most cases connection pooling problems are related to connection leaks. Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.

您要确保真正关闭连接.例如下面的代码会导致连接泄漏，如果.Open和Close之间的代码抛出异常:

You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open and Close throws an exception:

var connection = new SqlConnection(connectionString);

connection.Open();
// some code
connection.Close();                

正确的做法是:

var connection = new SqlConnection(ConnectionString);

try
{
     connection.Open();
     someCall (connection);
}
finally
{
     connection.Close();                
}

或

using (SqlConnection connection = new SqlConnection(connectionString))
{
     connection.Open();
     someCall(connection);
}

当您的函数从类方法返回连接时，请确保在本地缓存它并调用它的 Close 方法.例如，您将使用此代码泄漏连接:

When your function returns a connection from a class method make sure you cache it locally and call its Close method. You'll leak a connection using this code for example:

var command = new OleDbCommand(someUpdateQuery, getConnection());

result = command.ExecuteNonQuery();
connection().Close(); 

第一次调用 getConnection() 返回的连接没有关闭.此行不是关闭您的连接，而是创建一个新连接并尝试关闭它.

The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.

如果您使用 SqlDataReader 或 OleDbDataReader，请关闭它们.尽管关闭连接本身似乎可以解决问题，但请在使用数据读取器对象时额外努力明确关闭它们.

If you use SqlDataReader or a OleDbDataReader, close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.

本文为什么连接池会溢出?"来自 MSDN/SQL Magazine 解释了很多细节并提出了一些调试策略:

This article "Why Does a Connection Pool Overflow?" from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:

• 运行 sp_who 或 sp_who2.这些系统存储过程从 sysprocesses 系统表返回信息，该表显示所有工作进程的状态和信息.通常，您会看到每个连接有一个服务器进程 ID (SPID).如果您使用连接字符串中的应用程序名称参数命名您的连接，您的工作连接将很容易找到.
• 使用 SQL Server Profiler 和 SQLProfiler TSQL_Replay 模板来跟踪打开的连接.如果您熟悉 Profiler，则此方法比使用 sp_who 进行轮询更容易.
• 使用性能监视器来监视池和连接.我稍后会讨论这个方法.
• 监控代码中的性能计数器.您可以通过使用例程提取计数器或使用新的 .NET PerformanceCounter 控件来监控连接池的运行状况和已建立的连接数.

• Run sp_who or sp_who2. These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
• Use SQL Server Profiler with the SQLProfiler TSQL_Replay template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.
• Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
• Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.

这篇关于如何解决 ASP.NET 和 SQL Server 之间的连接池问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！