How much should I need to Dispose() of

[Raterus]

I'm starting to run into timeout issues on my web application with my connection object trying to get a connection from the connection pool. I use this connection object on every page. I make sure I Close() it after I use it, but now I think I need to call it's Dispose() method as well, after my page finishes. My question is, how "anal" should I be about cleaning up before .NET's Garbage collection supposedly cleans up. Should every object I use that has a dispose method, namely ado.net's object be disposed of? Should I just worry about the connected layer of ado.net, connections, commands... or should I get into disposing datasets and datatables, etc?

Thanks,
--Michael

 

[Miha Markic [MVP C#]]

Hi,

Basically it is a good behaviour if you dispose everything you can.
For the connection it is necessary to Close it, Dispose is optional (look at
above sentence)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I'm starting to run into timeout issues on my web application with my
connection object trying to get a connection from the connection pool. I
use this connection object on every page. I make sure I Close() it after I
use it, but now I think I need to call it's Dispose() method as well, after
my page finishes. My question is, how "anal" should I be about cleaning up
before .NET's Garbage collection supposedly cleans up. Should every object
I use that has a dispose method, namely ado.net's object be disposed of?
Should I just worry about the connected layer of ado.net, connections,
commands... or should I get into disposing datasets and datatables, etc?

Thanks,
--Michael

 

[David Browne]

I'm starting to run into timeout issues on my web application with my

connection object trying to get >a connection from the connection pool. I
use this connection object on every page. I make sure I >Close() it after I
use it, but now I think I need to call it's Dispose() method as well, after
my >page finishes. My question is, how "anal" should I be about cleaning up
before .NET's Garbage >collection supposedly cleans up. Should every object
I use that has a dispose method, namely >ado.net's object be disposed of?
Should I just worry about the connected layer of ado.net, >connections,
commands... or should I get into disposing datasets and datatables, etc?

Just worry about the connected layer: Connections and DataReaders. You
don't need to .Close and .Dispose, either will do. Many of the other objects
are Disposable only because they derive from Component.

David

 

[Jon Skeet [C# MVP]]

Basically it is a good behaviour if you dispose everything you can.
For the connection it is necessary to Close it, Dispose is optional (look at
above sentence)

Well, Close isn't necessary if you call Dispose - they'll basically
both close the connection.

I just use Dispose wherever I use something which implements
IDisposable.

 

[Cor Ligthert]

Hi Raterus,

To add, to this to the others, the conncetion.dispose removes the
connections string what the close does not.

So when you dispose everytime you have before the open to set the connection
string again.

Angel always tells in the dotNet newsgroups that with SQLservers with more
than 100 connections it is better to use the dispose.

Just a little addition.

Cor

 

[Miha Markic [MVP C#]]

Well, Close isn't necessary if you call Dispose - they'll basically
both close the connection.

Well yes, I was sort of speaking for his case where he is closing a
connection.
Plus, Close method is part of provider namespace thus different providers
could handle it differently.

I just use Dispose wherever I use something which implements
IDisposable.

Jon, you are a wise man

 

[William \(Bill\) Vaughn]

Dispose won't help if the connection is orphaned or still in use.
Connection pool timeouts are caused by one thing: the connection pool is
full. Why does the pool fill when it gets to 100 connections when 5 should
be enough for a healthy system? Generally, there's two reasons:
1) The connection was not done in time to be reused by the next request.
To compute this, divide the available bandwidth of the system (its
processing power) by the demand. Suppose your ASP page takes 5 seconds to
service a query. This means it can handle about 12 hits/minute per
connection. If you have 120 hits/minute you're going to exhaust the capacity
of the system and your pool will overflow. The solution to this problem is
either improve the hardware performance or decrease the length of time each
ASP takes to do its job.
2) The connections are not closed when they should be. We see this very
frequently when the DataReader is used. There are many consultants and dev
organizations that prohibit the use of the DataReader for just this reason
(and others). The problem with the DataReader and the other Command methods
is that you are responsible for connection management. You have to open AND
close the connection. The language won't do it for you (in time) and ADO
won't do it unless you program it to. Unlike VB6, the GC won't close the
connections in time to use them again--especially in heavily loaded systems.
If you make a practice of creating a DR in one scope and passing it to
another, you MUST use CommandBehavior.CloseConnection AND the receiving
function MUST close the DataReader. None of the other Command methods that
require open connections have this switch so you're on your own.
To see if your connection pool is leaking, watch it with PerfMon. If it's
leveled off, that's good. It's probably not leaking. However, if you see a
stair-step pattern, you're leaking if the graph does not level off. Consider
that heavily used sites stabilize at 6-12 connections--not 80-100.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I'm starting to run into timeout issues on my web application with my
connection object trying to get a connection from the connection pool. I
use this connection object on every page. I make sure I Close() it after I
use it, but now I think I need to call it's Dispose() method as well, after
my page finishes. My question is, how "anal" should I be about cleaning up
before .NET's Garbage collection supposedly cleans up. Should every object
I use that has a dispose method, namely ado.net's object be disposed of?
Should I just worry about the connected layer of ado.net, connections,
commands... or should I get into disposing datasets and datatables, etc?

Thanks,
--Michael

 

[David Browne]

Dispose won't help if the connection is orphaned or still in use.
Connection pool timeouts are caused by one thing: the connection pool is
full. Why does the pool fill when it gets to 100 connections when 5 should
be enough for a healthy system? Generally, there's two reasons:
1) The connection was not done in time to be reused by the next request.
To compute this, divide the available bandwidth of the system (its
processing power) by the demand. Suppose your ASP page takes 5 seconds to
service a query. This means it can handle about 12 hits/minute per
connection. If you have 120 hits/minute you're going to exhaust the capacity
of the system and your pool will overflow. The solution to this problem is
either improve the hardware performance or decrease the length of time each
ASP takes to do its job.

In practice ASP will generally not allow that many simultaneous requests to
execute. The exact mechanism is obscure, but requests should start queuing
before you run out of connections. If this does happen, the solution is to
properly configure the ASP worker thread pool. If all your ASP pages
require DB access, you should never allow more ASP worker threads than
available connections.

David

 

[Dmitri Khanine]

Bill

My # of pooled connections stays leveled under load (about 15 sql sp
calls/sec) for about 25 min until the GC kicks in. As soon as the
memory frees up my SQL server conncetions take a dip and go back to
where they were and at the same time the new connection pool is
created and I leak about 15 connections (system-wide). The max pool
size is set to 20. DO I leak conncetions in my code or there is
something else I need to know about the GC logic?

Thanks very much!

 

[Jon Skeet [C# MVP]]

My # of pooled connections stays leveled under load (about 15 sql sp
calls/sec) for about 25 min until the GC kicks in. As soon as the
memory frees up my SQL server conncetions take a dip and go back to
where they were and at the same time the new connection pool is
created and I leak about 15 connections (system-wide). The max pool
size is set to 20. DO I leak conncetions in my code or there is
something else I need to know about the GC logic?

If the GC is affecting your connections, you probably aren't closing
collections properly, IMO.

 

[David Browne]

Bill

My # of pooled connections stays leveled under load (about 15 sql sp
calls/sec) for about 25 min until the GC kicks in. As soon as the
memory frees up my SQL server conncetions take a dip and go back to
where they were and at the same time the new connection pool is
created and I leak about 15 connections (system-wide). The max pool
size is set to 20. DO I leak conncetions in my code or there is
something else I need to know about the GC logic?

The GC should never be freeing up your connections. That's a sure sign that
you are leaking connections in your application code.

David

 

[Dmitri Khanine]

Here is what I do:

public void Close() {
if (conn != null && conn.State != ConnectionState.Closed )
conn.Dispose(); // or conn.Close() - works the same way
}

The method invoked from finally {} block. THe # of pooled connections
stay constant for a long time. How am I leaking a connection if my max
pool size is not even reached?

Thank you!

 

[Cor Ligthert]

Hi Dmitri,

Have a look at what Angel wrote about this in this thread.

The other text including mine you can ignore.
http://tinyurl.com/2a4ay

I hope this helps,

Cor

 

[Jon Skeet [C# MVP]]

Here is what I do:

public void Close() {
if (conn != null && conn.State != ConnectionState.Closed )
conn.Dispose(); // or conn.Close() - works the same way
}

How is that better than using a using block to start with?

The method invoked from finally {} block. THe # of pooled connections
stay constant for a long time. How am I leaking a connection if my max
pool size is not even reached?

You could be leaking a fixed number at some time rather than leaking
them repeatedly. Without seeing all your code, it's hard to say exactly
what's going on - but if the garbage collector is making a difference,
chances are something is wrong *somewhere*.

 

[Angel Saenz-Badillos[MS]]

Cor has already posted a good link, just wanted to follow up.

Never use the finalizer to close SqlConnections. In fact you should not use
the finalizer to touch any managed resources at all, use it only to release
native resources that you are consuming directly. In your current
implementation you are lucky that you are only leaking connections (as has
been stated, GC affecting pooling is a sure sign that you are leaking). When
your application gets into heavy load you will probably crash outright as
hard to debug timing issues result from the finalizer trying to close
objects that have been GC'd.

This problem is so common I added the following comment to the Docs:
CAUTION Do not call Close or Dispose on a Connection, a DataReader, or
any other managed object in the Finalize method of your class. In a
finalizer, you should only release unmanaged resources that your class owns
directly. If your class does not own any unmanaged resources, do not include
a Finalize method in your class definition. For more information, see
Programming for Garbage Collection.
Hope this helps,

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

Here is what I do:

public void Close() {
if (conn != null && conn.State != ConnectionState.Closed )
conn.Dispose(); // or conn.Close() - works the same way
}

The method invoked from finally {} block. THe # of pooled connections
stay constant for a long time. How am I leaking a connection if my max
pool size is not even reached?

Thank you!

Jon Skeet [C# MVP] <[email protected]> wrote in message

If the GC is affecting your connections, you probably aren't closing
collections properly, IMO.

 

[Dmitri Khanine]

Thanks everyone for your replies. Not sure if I have an answer though.
If I leak connections - my pool must overflow and I should start
getting exceptions.
What happens is - app works perfectly fine with no exceptions for 25
min on just 20 connection pool and when GC kicks in - the new pool is
created and the old one being abandoned. After about 10 iterations
like that and about 200 - 250 total connections across all the pools I
am starting to get exceptions and the app dies for good and never
recoverers). If I use the same connection string (from web.config) -
why are the new pools are being created?

 

[Jon Skeet [C# MVP]]

Thanks everyone for your replies. Not sure if I have an answer though.
If I leak connections - my pool must overflow and I should start
getting exceptions.

No, not necessarily. You could be leaking them either very slowly
(slower than the GC is picking them up) or leaking just a fixed number
at some point.

What happens is - app works perfectly fine with no exceptions for 25
min on just 20 connection pool and when GC kicks in - the new pool is
created and the old one being abandoned. After about 10 iterations
like that and about 200 - 250 total connections across all the pools I
am starting to get exceptions and the app dies for good and never
recoverers). If I use the same connection string (from web.config) -
why are the new pools are being created?

How are you determining that a new pool is being created? I don't know
the details, but it might be possible that something is noticing that
connections are leaking, and deciding to start a new pool in case
connections have been going stale.

 

[Dmitri Khanine]

How are you determining that a new pool is being created? I don't know
the details, but it might be possible that something is noticing that
connections are leaking, and deciding to start a new pool in case
connections have been going stale.

..NET CLR Data performance counter tells me that the new pool is being
created. Nothing in documentation (unless I overlook something!)
suggests that this is something the pooler can do. The new pool can
only be created when the connection string has changed.

Also this happens even when I have a max pool size set to 100 and the
number of pooled connections is only 65!

 

[Jon Skeet [C# MVP]]

.NET CLR Data performance counter tells me that the new pool is being
created. Nothing in documentation (unless I overlook something!)
suggests that this is something the pooler can do. The new pool can
only be created when the connection string has changed.

Also this happens even when I have a max pool size set to 100 and the
number of pooled connections is only 65!

Hmm... I really don't know, I'm afraid.

 

[Angel Saenz-Badillos[MS]]

Dmitri,
Pools are per appdomain, if your appdomain gets unloaded you will create a
new pool the next time you reload your page This is perfectly normal
behavior, IIS will automatically unload appdomains under stress.

The other problem you are seeing is more than likely a connection leak in
your code. I am not sure if you read my other post in this thread but if you
are calling SqlConnection close on the finalizer for the page this is almost
certainly your problem.

Hope this helps,

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

Jon Skeet [C# MVP] <[email protected]> wrote in message

How are you determining that a new pool is being created? I don't know
the details, but it might be possible that something is noticing that
connections are leaking, and deciding to start a new pool in case
connections have been going stale.

.NET CLR Data performance counter tells me that the new pool is being
created. Nothing in documentation (unless I overlook something!)
suggests that this is something the pooler can do. The new pool can
only be created when the connection string has changed.

Also this happens even when I have a max pool size set to 100 and the
number of pooled connections is only 65!