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

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/

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

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

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

BTW, is the connection string always the same?

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

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

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

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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...
>

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

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:(E-Mail Removed)...
> 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/
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>