池中的所有连接都在使用中
目前我们遇到了一些情况 - 似乎有人在某个地方忘记了在代码中关闭连接。结果是连接池相对较快地耗尽。作为临时补丁，我们将 Max Pool Size = 500; 添加到 Web 服务上的连接字符串，并在所有连接都用完时回收池，直到我们弄清楚这一点。
到目前为止，我们已经完成了以下操作：
SELECT SPId
FROM MASTER..SysProcesses
WHERE DBId = DB_ID('MyDb') and last_batch < DATEADD(MINUTE, -15, GETDATE())
获取 15 分钟内未使用的 SPID。我们现在尝试获取最后使用该 SPID 执行的查询：
DBCC INPUTBUFFER(61)
但是显示的查询是多种多样的，这意味着要么有关连接操作的基础级别上的某些内容被破坏，要么我们的推论是错误的...
是否有错误我们的想法在这里？ DBCC / sysprocesses 是否给出了我们期望的结果，或者是否存在一些副作用？ （例如，池影响力中的连接？）
（请坚持我们可以使用 SQL 找到的内容，因为编写代码的人很多，但现在并不是全部都在场）
We currently have a little situation on our hands - it seems that someone, somewhere forgot to close the connection in code. Result is that the pool of connections is relatively quickly exhausted. As a temporary patch we added Max Pool Size = 500; to our connection string on web service, and recycle pool when all connections are spent, until we figure this out.
So far we have done this:
SELECT SPId
FROM MASTER..SysProcesses
WHERE DBId = DB_ID('MyDb') and last_batch < DATEADD(MINUTE, -15, GETDATE())
to get SPID's that aren't used for 15 minutes. We're now trying to get the query that was executed last using that SPID with:
DBCC INPUTBUFFER(61)
but the queries displayed are various, meaning either something on base level regarding connection manipulation was broken, or our deduction is erroneous...
Is there an error in our thinking here? Does the DBCC / sysprocesses give results we're expecting or is there some side-effect catch? (for example, connections in pool influence?)
(please, stick to what we could find out using SQL since the guys that did the code are many and not all present right now)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我希望输入缓冲区“记住”无数不同的查询 - 根据失败的时间和运行的查询的种类，您似乎不太可能以这种方式看到一致的查询。回想一下，连接最终将被关闭，但只有当它们被 GC 并最终确定时。
正如 Mitch 所建议的，您需要在源代码中搜索打开的连接，并确保它们已本地化并包装在 using() 中。还要寻找可能保持连接的长期存在的对象。在我们的目录 ASP 页面对象的早期版本中，持有未正确管理的连接。
为了缩小范围，您可以在专注于应用程序的特定部分时监控连接计数（perfmon）吗？与报告或其他查询相比，它在 CRUD 区域中发生的次数是否更多？这可能有助于缩小您需要进行的源搜索范围。
I would expect that there is a myriad of different queries 'remembered' by inputbuffer - depending on the timing of your failure and the variety of queries you run, it seems unlikely that you'd see consistent queries in this way. Recall that the connections will eventually be closed, but only when they're GC'd and finalized.
As Mitch suggests, you need to scour your source for connection-opens and ensure they're localized and wrapped in a using(). Also look for possibly-long-lived objects that might be holding on to connections. In an early version of our catalog ASP page objects held connections that weren't managed properly.
To narrow it down, can you monitor connection-counts (perfmon) as you focus on specific portions of your app? Does it happen more in CRUD areas vs. reporting or other queries? That might help narrow down the source-scour you need to do.
您是否能够更改连接字符串以包含有关在“应用程序”字段中创建连接的位置和原因的信息？
Are you able to change the connection strings to contain information about where and why the connection was created in the Application field?