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（framework 4.x），所有事务/ select / update / insert都是用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你应该关闭它的连接（这是让我生气的原因）。
1 个解决方案
#1
17
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.
这意味着如果它们仍然未使用，服务器本身最终将清理这些池。如果没有清理，l表示服务器认为连接仍在使用中，并且正在挂起以提高性能。
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":
但是，如果要调整池，可以修改连接字符串。查看此页面，然后搜索“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可能有人在那里提供帮助。
#1
17
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.
这意味着如果它们仍然未使用，服务器本身最终将清理这些池。如果没有清理，l表示服务器认为连接仍在使用中，并且正在挂起以提高性能。
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":
但是，如果要调整池，可以修改连接字符串。查看此页面，然后搜索“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可能有人在那里提供帮助。