我该如何解决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."

我们已经在我们的code在一段时间没有改变任何东西。我修改了code检查哪个没有关闭打开的连接，却发现一切都很好。

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 CONFIGRATION？

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.

您想确保你的真的关闭连接。例如下面的code会造成连接泄漏，如果在。开和关闭抛出一个异常：

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:

SqlConnection myConnection = new SqlConnection(ConnectionString);
myConnection.Open();
// some code
myConnection.Close();                

正确的方法应该是这样：

The correct way would be this:

SqlConnection myConnection = new SqlConnection(ConnectionString);
try
{
     conn.Open();
     someCall (myConnection);
}
finally
{
     myConnection.Close();                
}

或

using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
     myConnection.Open();
     someCall(myConnection);
}

当你的函数的返回从一个类的方法连接确保你在本地高速缓存，并调用它的关闭方法。你会使用这个code例如泄漏的连接：

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:

myCommand = new OleDbCommand(SomeUpdateQuery, getConnection());
result = myCommand.ExecuteNonQuery();
myConnection().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杂志解释了很多的细节，并提出了一些调试策略：

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事件探查器与SQLProfiler TSQL_Replay 模板来跟踪打开的连接。如果你熟悉探查，这种方法比轮询更容易使用的sp_who。
• 使用性能监视器来监视池和连接。我在某一时刻讨论这个方法。
• 在code显示屏性能计数器。您可以通过使用例程来提取计数器或通过使用新的.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屋！