ado.net, connection pooling, and leaked connections

[matthew_glen_evans]

Hi,

I have inherited some legacy code with performance issues:

I ran perfmon on the problematic use case, with a counter on .NET CLR
Data / Current # Pooled connections (amongst others)

I noticed that while performing a set of tasks in the use case, pooled
connections increased systematically peaking at 9. I think this is
because of the workload governor which MSDE uses, but I think that this
would simply carry on increasing on a full instance of SQL Server.

I am unsure why this is happening: my question is this: given a
non-threaded single-user environment, why would the current pooled
connections increase in this way.

I have not yet tried to debug the entire process, but on the face of
it, all data access is mediated through a data access layer, in which
all method calls dutifully close connections which they open in a
finally block.

I would have expected the same connection to be iteratively re-used,
but it seems like connections are being leaked...

My expectation in a full user environment is that the pool would run
out of connections and begin to throw "Timeout in obtaining a
connection from the pool" exceptions:

i.e.

System.InvalidOperationException: Timeout expired. The timeout period
elapsed prior to obtaining a connection from the pool. This may have
occurred because all pooled connections were in use and max pool size
was reached.


Typically what does happen is that the server starts to become slow and
generates System.Data.SqlClient.SqlException Timout Expired.The timeout
period elapsed prior to completion of the operation or the server is
not responding

Could they be related? Any thoughts on why the connections are not
being released?

TIA,

Matthew

 

[Miha Markic [MVP C#]]

Hi Matthew,

If there is connection leak than it probably means that somebody is not
closing and/or disposing connections.

 

[matthew_glen_evans]

Thanks for your reply

Yes.. I agree, but would you say that the situation as described is
indicative of leaking connections?

It seems like it to me.

thanks,

Matthew

 

[Miha Markic [MVP C#]]

Yes, leaking. But leaking because they are not closed properly...

 

[Miha Markic [MVP C#]]

BTW, is the connection string always the same?

 

[matthew_glen_evans]

Yep. The code seems to be using two data access layers. The one seems
fine i.e. closes all connections in a finally block. The other is
hidden in an in house framework, which I guess I'm going to have to
debug.

Thanks for your feedback...

 

[matthew_glen_evans]

Yes and there's only 1 connection pool, when I check that counter.

 

[Miha Markic [MVP C#]]

A memory profiler (such as AQTime or Devpartner community edition you get
for free) would be very very useful for such an exercise.

 

[Dave Lech]

Should you dispose of the connections after closing them? What about
command objects? Should they be disposed of also? I have a data access
class in which I do all the stored proc calls for our app. The command and
connection objects are local variables in the various methods of the class.

Thanx,
Dave Lech

Hi Matthew,

If there is connection leak than it probably means that somebody is not
closing and/or disposing connections.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi,

I have inherited some legacy code with performance issues:

I ran perfmon on the problematic use case, with a counter on .NET CLR
Data / Current # Pooled connections (amongst others)

I noticed that while performing a set of tasks in the use case, pooled
connections increased systematically peaking at 9. I think this is
because of the workload governor which MSDE uses, but I think that this
would simply carry on increasing on a full instance of SQL Server.

I am unsure why this is happening: my question is this: given a
non-threaded single-user environment, why would the current pooled
connections increase in this way.

I have not yet tried to debug the entire process, but on the face of
it, all data access is mediated through a data access layer, in which
all method calls dutifully close connections which they open in a
finally block.

I would have expected the same connection to be iteratively re-used,
but it seems like connections are being leaked...

My expectation in a full user environment is that the pool would run
out of connections and begin to throw "Timeout in obtaining a
connection from the pool" exceptions:

i.e.

System.InvalidOperationException: Timeout expired. The timeout period
elapsed prior to obtaining a connection from the pool. This may have
occurred because all pooled connections were in use and max pool size
was reached.


Typically what does happen is that the server starts to become slow and
generates System.Data.SqlClient.SqlException Timout Expired.The timeout
period elapsed prior to completion of the operation or the server is
not responding

Could they be related? Any thoughts on why the connections are not
being released?

TIA,

Matthew