How to detect SqlServer connection leaks in a ASP.net applications ?

Question

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.

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.

I guess the .Net garbage collector will eventually close them but... that can take a while, no?

I've got SQL Server Management Studio and I do notice from the activity monitor that there are quite a few connecions opened on the database from the activity monitor.

So, from all that's being said above, here are some questions related to the main question :

1. 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?

2. 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?

3. If performance counters are the best solution, what are the variables that I should watch?

Answer 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.

If open connections were an issue, in activity monitor you would see a VERY large number of connections with no activity.

For performance counters you might start looking at the "SQL Server : General Stats" performance object.

Answer 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).

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.

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).

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.

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)

@Later edit:You could check this question here on stackoverflow.com too

Answer 3

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.

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#.

Answer 4

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.