.net 超过 100 个连接到 sql server 2008 处于“休眠”状态

More than 100 connections to sql server 2008 in "sleeping" status

提问by Allende

I have a big trouble here, well at my server.

我在这里遇到了大麻烦，在我的服务器上。

I have an ASP .net web (framework 4.x) running on my server, all the transactions/select/update/insert are made with ADO.NET.

我有一个 ASP .net web（框架 4.x）在我的服务器上运行，所有的事务/选择/更新/插入都是用 ADO.NET 进行的。

The problem is that after being using for a while (a couple of updates/selects/inserts) sometimes I got more than 100 connections on "sleeping" status when check for the connections on sql server with this query:

问题是，在使用一段时间后（几次更新/选择/插入），当使用此查询检查 sql server 上的连接时，有时我会在“睡眠”状态下获得 100 多个连接：

SELECT 
 spid,
 a.status,
 hostname,  
 program_name,
 cmd,
 cpu,
  physical_io,
  blocked,
  b.name,
  loginame
FROM   
  master.dbo.sysprocesses  a INNER JOIN
  master.dbo.sysdatabases b  ON
    a.dbid = b.dbid where program_name like '%TMS%'
ORDER BY spid 

I've been checking my code and closing every time I make a connection, I'm gonna test the new class, but I'm afraid the problem doesn't be fixed.

我一直在检查我的代码并在每次建立连接时关闭，我要测试新类，但我担心问题没有解决。

It suppose that the connection pooling, keep the connections to re-use them, but until I see don't re-use them always.

它假设连接池，保持连接重新使用它们，但直到我看到不要总是重新使用它们。

Any idea besides check for close all the connections open after use them?

除了在使用它们后检查关闭所有打开的连接之外，还有什么想法吗？

SOLVED(now I have just one and beautiful connection on "sleeping" status):

已解决（现在我只有一个关于“睡眠”状态的美丽联系）：

Besides the anwser of David Stratton, I would like to share this link that help explain really well how the connection pool it works: http://dinesql.blogspot.com/2010/07/sql-server-sleeping-status-and.html

除了David Stratton的回答者之外，我想分享这个链接，它有助于很好地解释连接池的工作原理：http: //dinesql.blogspot.com/2010/07/sql-server-sleeping-status-and。 html

Just to be short, you need to close every connection (sql connection objects) in order that the connection pool can re-use the connection and use the same connectinos string, to ensure this is highly recommended use one of the webConfig.

简而言之，您需要关闭每个连接（sql 连接对象），以便连接池可以重新使用该连接并使用相同的 connectinos 字符串，以确保强烈建议使用 webConfig 之一。

Be careful with dataReaders you should close its connection to (that was what make me mad for while).

小心使用 dataReaders 你应该关闭它的连接（这让我很生气）。

回答by David

It sounds like it is connection pooling.

听起来像是连接池。

From here: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

从这里：http: //msdn.microsoft.com/en-us/library/8xx3tyca.aspx

A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default). Connections are released back into the pool when they are closed or disposed.

为每个唯一的连接字符串创建一个连接池。创建池时，会创建多个连接对象并将其添加到池中，以满足最小池大小要求。连接会根据需要添加到池中，直至指定的最大池大小（默认值为 100）。连接在关闭或处置时会释放回池中。

To ensure you're not creating unnecessary pools, ensure that the exact same connection string is used each time you connect - store it in the .config file.

为确保您不会创建不必要的池，请确保每次连接时都使用完全相同的连接字符串 - 将其存储在 .config 文件中。

You can also reduce the Maximum Pool Size if you like.

如果您愿意，您还可以减少最大池大小。

Actually, I'd recommend just reading the entire article linked to above. It talks about clearing the pools, and gives you the best practices for using pooling properly.

实际上，我建议您阅读上面链接的整篇文章。它讨论了清除池，并为您提供了正确使用池的最佳实践。

Edit - added the next day

编辑 - 第二天添加

The pools on your server are there because of how Connection pooling works. Per the documentation linked to above:

由于连接池的工作方式，服务器上的池在那里。根据上面链接的文档：

The connection pooler removes a connection from the pool after it has been idle for a long time, or if the pooler detects that the connection with the server has been severed. Note that a severed connection can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only when they are closed or reclaimed.

连接池程序在连接池空闲很长时间后，或者池程序检测到与服务器的连接已被切断时，从池中删除连接。请注意，只有在尝试与服务器通信后才能检测到断开的连接。如果发现不再连接到服务器的连接，则将其标记为无效。无效的连接只有在关闭或回收时才会从连接池中删除。

This means that the server itself will clean up those pools eventually, if they remain unused. If the are NOT cleaned up,l that means that the server believes that the connections are still in use, and is hanging on to them to increase your performance.

这意味着服务器本身最终会清理这些池，如果它们仍然未使用。如果未清除，则意味着服务器认为连接仍在使用中，并且正在挂起以提高您的性能。

In other words, I wouldn't worry about it unless you see a problem. Connection Pooling is happening exactly as it should be.

换句话说，除非您发现问题，否则我不会担心。连接池正在按照它应有的方式进行。

If you REALLY want to clear the pools, again, per the documentation:

如果您真的想清除池，请再次按照文档：

Clearing the Pool

ADO.NET 2.0 introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections being used at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.

清理池

ADO.NET 2.0 引入了两种清除池的新方法：ClearAllPools 和 ClearPool。ClearAllPools 清除给定提供程序的连接池，而 ClearPool 清除与特定连接关联的连接池。如果在呼叫时正在使用连接，则会对其进行适当标记。当它们关闭时，它们被丢弃而不是返回到池中。

However, if you want to adjust pooling, the Connection String can be modified. See this page, and search for the word "pool":

但是，如果要调整池化，则可以修改连接字符串。请参阅此页面，并搜索“池”一词：

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

Or you can enlist a DBA to assist and set pooling at the server-level. That's off-topic here, but ServerFault.com might have people to assist there.

或者，您可以招募 DBA 来协助和设置服务器级别的池。这是题外话，但 ServerFault.com 可能有人在那里提供帮助。