超过 100 个到 sql server 2008 的连接处于“睡眠”状态地位

发布于 2024-12-06 12:03:24 字数 1055 浏览 2 评论 0原文

我这里遇到了大麻烦，尤其是我的服务器。

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

问题是，使用一段时间后（几次更新/选择/插入），有时在使用此查询检查 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

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

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

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

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

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

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

小心 dataReaders，你应该关闭它的连接（这就是让我生气的原因）。

原文

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.

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:

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

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.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

何其悲哀 2024-12-13 12:03:24

听起来像是连接池。

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

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

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

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

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

编辑 - 第二天添加

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

连接池程序在连接完成后将其从池中删除
长时间闲置，或者池化器检测到
与服务器的连接已被切断。请注意，被切断的
仅在尝试通信后才能检测到连接
服务器。如果发现某个连接不再连接到
服务器，它被标记为无效。无效连接被删除
仅当连接池关闭或回收时才从连接池中获取。

这意味着如果这些池仍未使用，服务器本身最终将清理它们。如果未清理，则意味着服务器认为连接仍在使用中，并会保留它们以提高性能。

换句话说，除非你发现问题，否则我不会担心。连接池正在按其应有的方式发生。

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

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

但是，如果您想调整池化，可以修改连接字符串。请参阅此页面，然后搜索单词“池”：

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

或者您可以聘请 DBA 来协助并在服务器级别设置池。这是题外话，但 ServerFault.com 可能会有人提供帮助。

It sounds like it is connection pooling.

From here: 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.

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.

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.

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

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.

回复收藏 0 原文

~没有更多了~