I'm currently doing some GUI testing on a ASP.net 2.0 application. The RDBMS is SQL Server 2005. The host is Win Server 2003 / IIS 6.0.
我目前正在ASP.net 2.0应用程序上做一些GUI测试。RDBMS是SQL Server 2005。主机是Win Server 2003 / IIS 6.0。
I do not have the source code of the application because it was programmed by an external company who's not releasing the code.
我没有应用程序的源代码，因为它是由一个不发布代码的外部公司编写的。
I've noticed that the application performs well when I restart IIS but after some testing, after I have opened and closed my browser for a couple of hours, the application starts to get slower and slower. I was wondering if this behaviour was due to a bad closing connection practice from the programmers : I'm suspecting an open connection leak on the database here.
我注意到，当我重新启动IIS时，应用程序运行得很好，但是在一些测试之后，在打开和关闭浏览器几个小时之后，应用程序开始变得越来越慢。我想知道这种行为是否是由于程序员糟糕的关闭连接实践造成的:我怀疑这里的数据库上有一个打开的连接泄漏。
I guess the .Net garbage collector will eventually close them but... that can take a while, no?
我想。net垃圾收集器最终会关闭它们，但是…这可能需要一段时间，不是吗?
I've got SQL Server Management Studio and I do notice from the activity monitor that there are quite a few connections opened on the database.
我有SQL Server Management Studio，我从活动监视器中注意到，在数据库上打开了许多连接。
From all that's being said above, here are some questions related to the main question :
从以上所说的一切，以下是一些与主要问题相关的问题:
-
Is there any way to know in SQL Server 2005 if connections are open because they're waiting to be used in a connection pool or if they're open because they are used by an application?
如果连接是打开的，那么在SQL Server 2005中有什么方法可以知道吗?因为它们等待在连接池中使用，或者如果它们是打开的，因为它们被应用程序使用?
-
Does somone know of good online/paper resources where I could learn how to use performance counters or some other kind of tools to help track down these kind of issues?
有人知道好的在线/纸质资源吗?在那里我可以学习如何使用性能计数器或其他工具来跟踪这些问题。
-
If performance counters are the best solution, what are the variables that I should watch?
如果性能计数器是最好的解决方案，那么我应该关注哪些变量呢?
6 个解决方案
#1
2
You can always check the connection strings from web.config (mainly if they have connection pooling activated, if they have any connection limits enabled).
您总是可以检查来自web的连接字符串。配置(主要是如果激活了连接池，如果启用了任何连接限制)。
Also, if you are using IIS 6, you could set your web application to use a separate Application pool, and set other option for the recycling of the memory and processes.
此外，如果您正在使用IIS 6，您可以设置您的web应用程序使用一个单独的应用程序池，并为内存和进程的回收设置其他选项。
About the performance counters, you could check how long the garbage collector is running, how much memory the application is using, etc.
关于性能计数器，您可以检查垃圾收集器运行了多长时间，应用程序使用了多少内存，等等。
If you have access to sql server, you could monitor the connections made from your application (there are performance counters defined for every installed instance of SQL Server).
如果您可以访问sql server，那么您可以监视来自应用程序的连接(对于每个已安装的sql server实例，都定义了性能计数器)。
There were some articles in MSDN Magazine. Also you could use the SOS Debugging library to attach to the application's process and check it manually.
MSDN杂志上有一些文章。您还可以使用SOS调试库附加到应用程序的进程并手动检查它。
And, if you don't have the source code, try to use Reflector to get the sources of the application (they would be very usefull for debugging)
如果您没有源代码，请尝试使用Reflector获取应用程序的源代码(它们对于调试非常有用)
@Later edit:You could check this question here on *.com too
@Later edit:你也可以在*.com上查看这个问题
#2
54
Found this thread researching a similar problem. I came up with the following sql as a good way to debug leaky connections in SQL Server:
发现这个线程正在研究一个类似的问题。我想到了下面的sql，作为调试sql Server中泄漏连接的好方法:
SELECT S.spid, login_time, last_batch, status, hostname, program_name, cmd,
(
select text from sys.dm_exec_sql_text(S.sql_handle)
) as last_sql
FROM sys.sysprocesses S
where dbid > 0
and DB_NAME(dbid) = '<my_database_name>'
and loginame = '<my_application_login>'
order by last_batch asc
What this gives you is all open connections on a particular database and login, along with the last sql executed on that connection, sorted by the time at which that sql was executed.
这为您提供的是特定数据库上的所有打开连接和登录，以及在该连接上执行的最后一个sql，按执行sql的时间排序。
Because of connection pooling, you can’t just rely on the fact that there are a lot of connections hanging around to tell you that you have a connection leakage, because connection pooling will keep connections around even if they are closed correctly from code. However, if you do have a connection leakage, what you will see is that some connections become “frozen”—they will show up in the above query and the “last_batch” timestamp will never change. The other connections will also hang around, but every time new sql is run on them, the “last_batch” timestamp gets updated. So the effect is that the frozen connections will float to the top of this query.
由于连接池，您不能仅仅依靠一个事实，即有许多连接挂在一起，告诉您有一个连接泄漏，因为连接池将保持连接，即使它们从代码中正确地关闭。但是，如果您确实有一个连接泄漏，您将看到一些连接变得“冻结”——它们将出现在上面的查询中，并且“last_batch”时间戳永远不会改变。其他连接也会挂起，但是每次在它们上运行新的sql时，都会更新“last_batch”时间戳。因此，冻结的连接将浮动到查询的顶部。
If you have the source code of the application in question, the fact that this gives you the last sql executed on the orphaned connection is very valuable for debugging.
如果您有问题应用程序的源代码，那么在孤立连接上执行的最后一个sql对于调试是非常有价值的。
#3
6
I faced this problem and found SQL Server Profiler to be a great tool, I monitored the site in a short testing run and noticed lots of connections being created (sp_who) that were not reused by Connection Pool, so I just opened SQL Server Profiler and then check if all calls to SP made from code were followed by a "sp_reset_connection" call. If the call is not there before the start of a new batch you are just lacking the first connection.
我面临这个问题,发现SQL Server分析器是一个伟大的工具,我在短测试运行和监控现场发现大量的连接被创建(sp_who)没有重用连接池,所以我就打开SQL Server分析器,然后检查是否所有调用SP由代码后面会跟着一个“sp_reset_connection”电话。如果在新批处理开始之前没有调用，那么您只是缺少第一个连接。
#4
2
The MSDN reference about (ADO.NET performance counters) is pretty clear about what you can look for when profiling the application. You can monitor the counters using the perfmon application built into Windows.
关于(ADO)的MSDN引用。NET性能计数器)非常清楚在分析应用程序时可以寻找什么。您可以使用内置在Windows中的perfmon应用程序监视计数器。
Other than that, I'd suggest learning about ADO.NET connection pooling. If you really suspect a bug in their code, you can take a look at it using Red Gate's Reflector (free for now) which disassembles the MSIL into C#.
除此之外，我建议了解一下ADO。网络连接池。如果您确实怀疑他们代码中的错误，您可以使用Red Gate的Reflector(目前免费)查看它，它将MSIL分解为c#。
#5
1
I would start by looking at the connections and looking at activity times, and see if you can find items that are keeping the connections open.
我将首先查看连接并查看活动时间，然后查看是否可以找到保持连接打开的项。
I would say thought that if the solution is to restart IIS, you might also look at the memory usage of the application to see if there is a memory leak or something there that really causes its footprint to grow.
我想说，如果解决方案是重启IIS，那么您还可以查看应用程序的内存使用情况，看看是否存在内存泄漏或某些确实导致其内存增长的东西。
If open connections were an issue, in activity monitor you would see a VERY large number of connections with no activity.
如果打开连接是一个问题，那么在activity monitor中您将看到大量没有活动的连接。
For performance counters you might start looking at the "SQL Server : General Stats" performance object.
对于性能计数器，您可以开始查看“SQL Server: General Stats”性能对象。
#6
1
Todd Denlinger wrote a fantastic class http://www.codeproject.com/KB/database/connectionmonitor.aspx which watches Sql Server connections and reports on ones that have not been properly disposed within a period of time. Wire it into your site, and it will let you know when there is a leak.
Todd Denlinger编写了一个奇妙的类http://www.codeproject.com/KB/database/connectionmonitor.aspx，它监视Sql服务器连接并报告在一段时间内未正确处理的连接。将它连接到您的站点，当有泄漏时，它会通知您。
#1
2
You can always check the connection strings from web.config (mainly if they have connection pooling activated, if they have any connection limits enabled).
您总是可以检查来自web的连接字符串。配置(主要是如果激活了连接池，如果启用了任何连接限制)。
Also, if you are using IIS 6, you could set your web application to use a separate Application pool, and set other option for the recycling of the memory and processes.
此外，如果您正在使用IIS 6，您可以设置您的web应用程序使用一个单独的应用程序池，并为内存和进程的回收设置其他选项。
About the performance counters, you could check how long the garbage collector is running, how much memory the application is using, etc.
关于性能计数器，您可以检查垃圾收集器运行了多长时间，应用程序使用了多少内存，等等。
If you have access to sql server, you could monitor the connections made from your application (there are performance counters defined for every installed instance of SQL Server).
如果您可以访问sql server，那么您可以监视来自应用程序的连接(对于每个已安装的sql server实例，都定义了性能计数器)。
There were some articles in MSDN Magazine. Also you could use the SOS Debugging library to attach to the application's process and check it manually.
MSDN杂志上有一些文章。您还可以使用SOS调试库附加到应用程序的进程并手动检查它。
And, if you don't have the source code, try to use Reflector to get the sources of the application (they would be very usefull for debugging)
如果您没有源代码，请尝试使用Reflector获取应用程序的源代码(它们对于调试非常有用)
@Later edit:You could check this question here on *.com too
@Later edit:你也可以在*.com上查看这个问题
#2
54
Found this thread researching a similar problem. I came up with the following sql as a good way to debug leaky connections in SQL Server:
发现这个线程正在研究一个类似的问题。我想到了下面的sql，作为调试sql Server中泄漏连接的好方法:
SELECT S.spid, login_time, last_batch, status, hostname, program_name, cmd,
(
select text from sys.dm_exec_sql_text(S.sql_handle)
) as last_sql
FROM sys.sysprocesses S
where dbid > 0
and DB_NAME(dbid) = '<my_database_name>'
and loginame = '<my_application_login>'
order by last_batch asc
What this gives you is all open connections on a particular database and login, along with the last sql executed on that connection, sorted by the time at which that sql was executed.
这为您提供的是特定数据库上的所有打开连接和登录，以及在该连接上执行的最后一个sql，按执行sql的时间排序。
Because of connection pooling, you can’t just rely on the fact that there are a lot of connections hanging around to tell you that you have a connection leakage, because connection pooling will keep connections around even if they are closed correctly from code. However, if you do have a connection leakage, what you will see is that some connections become “frozen”—they will show up in the above query and the “last_batch” timestamp will never change. The other connections will also hang around, but every time new sql is run on them, the “last_batch” timestamp gets updated. So the effect is that the frozen connections will float to the top of this query.
由于连接池，您不能仅仅依靠一个事实，即有许多连接挂在一起，告诉您有一个连接泄漏，因为连接池将保持连接，即使它们从代码中正确地关闭。但是，如果您确实有一个连接泄漏，您将看到一些连接变得“冻结”——它们将出现在上面的查询中，并且“last_batch”时间戳永远不会改变。其他连接也会挂起，但是每次在它们上运行新的sql时，都会更新“last_batch”时间戳。因此，冻结的连接将浮动到查询的顶部。
If you have the source code of the application in question, the fact that this gives you the last sql executed on the orphaned connection is very valuable for debugging.
如果您有问题应用程序的源代码，那么在孤立连接上执行的最后一个sql对于调试是非常有价值的。
#3
6
I faced this problem and found SQL Server Profiler to be a great tool, I monitored the site in a short testing run and noticed lots of connections being created (sp_who) that were not reused by Connection Pool, so I just opened SQL Server Profiler and then check if all calls to SP made from code were followed by a "sp_reset_connection" call. If the call is not there before the start of a new batch you are just lacking the first connection.
我面临这个问题,发现SQL Server分析器是一个伟大的工具,我在短测试运行和监控现场发现大量的连接被创建(sp_who)没有重用连接池,所以我就打开SQL Server分析器,然后检查是否所有调用SP由代码后面会跟着一个“sp_reset_connection”电话。如果在新批处理开始之前没有调用，那么您只是缺少第一个连接。
#4
2
The MSDN reference about (ADO.NET performance counters) is pretty clear about what you can look for when profiling the application. You can monitor the counters using the perfmon application built into Windows.
关于(ADO)的MSDN引用。NET性能计数器)非常清楚在分析应用程序时可以寻找什么。您可以使用内置在Windows中的perfmon应用程序监视计数器。
Other than that, I'd suggest learning about ADO.NET connection pooling. If you really suspect a bug in their code, you can take a look at it using Red Gate's Reflector (free for now) which disassembles the MSIL into C#.
除此之外，我建议了解一下ADO。网络连接池。如果您确实怀疑他们代码中的错误，您可以使用Red Gate的Reflector(目前免费)查看它，它将MSIL分解为c#。
#5
1
I would start by looking at the connections and looking at activity times, and see if you can find items that are keeping the connections open.
我将首先查看连接并查看活动时间，然后查看是否可以找到保持连接打开的项。
I would say thought that if the solution is to restart IIS, you might also look at the memory usage of the application to see if there is a memory leak or something there that really causes its footprint to grow.
我想说，如果解决方案是重启IIS，那么您还可以查看应用程序的内存使用情况，看看是否存在内存泄漏或某些确实导致其内存增长的东西。
If open connections were an issue, in activity monitor you would see a VERY large number of connections with no activity.
如果打开连接是一个问题，那么在activity monitor中您将看到大量没有活动的连接。
For performance counters you might start looking at the "SQL Server : General Stats" performance object.
对于性能计数器，您可以开始查看“SQL Server: General Stats”性能对象。
#6
1
Todd Denlinger wrote a fantastic class http://www.codeproject.com/KB/database/connectionmonitor.aspx which watches Sql Server connections and reports on ones that have not been properly disposed within a period of time. Wire it into your site, and it will let you know when there is a leak.
Todd Denlinger编写了一个奇妙的类http://www.codeproject.com/KB/database/connectionmonitor.aspx，它监视Sql服务器连接并报告在一段时间内未正确处理的连接。将它连接到您的站点，当有泄漏时，它会通知您。