Naive memory management questions.

[JohnSouth104]

Hi
I've an asp.net, c#, sql server website and the volumes are starting to
rise quickly. I've been asked to look at improving the memory usage of
the application. I've not looked at this before so these are probably
naive questions:

1. If I close the sql connection every time do I need to close or
dispose of the dataset, command or any other objects?

2. Should I use data adaptor instaed of dataset?

3. Are there any other objects that I should close or dispose, or just
reply on the garbage collector?

4. How can I check for memory leaks and what are the most likely
culprits?

Any suggestions to get me up to speed in this would be greatly
appreciated.

John South
Pangbourne
www.WhereCanWeGo.com

 

[Tasos Vogiatzoglou]

Some guidelines that I 'm trying to follow are these:

1. Use connection pooling. This way you can have a control on the
number of connections open without sacrificing much from your
performance

2. Fetch only the data you need.

3. Use a reader instead of a dataset whenever this is possible, ike
drop downs.

4. Try not to put objects in session or viewstate as this consumes
memory

5. In general you should dispose only objects that use unmanaged
resources (like connections). DataSets do not use unmanaged resources
and in fact they were designed to be used in a disconnected manner.

6. Use some kind of caching for data that is requested often and does
not change. This may sound stupid in situations like yours, but
consider that you can have only one copy of your data stored, without
having to fetch them from the server for every instance.

The garbage collector in general will not permit memory leaks ( only in
case of unmanaged resources that are not deallocated properly ) but it
can suffer from memory fragmentation. .NET garbage collector has
provisioning for that but a restart of the worker service will always
do good. There is a nice article about the garbage collector in MSDN

ms-help://MS.MSDNQTR.2005APR.1033/dndotnet/html/dotnetgcbasics.htm#dotnetgcbasics_topic3

Hope these will help.

 

[Scott Roberts]

Some guidelines that I 'm trying to follow are these:

1. Use connection pooling. This way you can have a control on the
number of connections open without sacrificing much from your
performance

Connection pooling is the default. No? If you're using SqlConnection I think
connection pooling happens automagically.

The garbage collector in general will not permit memory leaks ( only in
case of unmanaged resources that are not deallocated properly )

However, the garbage collector cannot prevent you from inadvertently
"hanging on" to objects you no longer need. Sounds silly, but can be a
problem in complex systems.

 

[Scott Roberts]

1. If I close the sql connection every time do I need to close or
dispose of the dataset, command or any other objects?

As Tasos recommended, use SqlDataReader instead of DataSet. And yes, you
should close it when finished.

2. Should I use data adaptor instaed of dataset?

SqlDataReader is fastest for forward-only read-only access.

4. How can I check for memory leaks and what are the most likely
culprits?

Most likely culprits, stuff you put into Session and forgot about. Also,
objects where you manually wired up some events.

http://www.red-gate.com/products/ANTS_Profiler/index.htm

http://www.red-gate.com/products/ants_load/index.htm

 

[Tasos Vogiatzoglou]

Connection pooling depends on the connection string you use. If you
want to setup connection pool you have to use the minPool, maxPool at
your connection string.

If there are objects that you do not need you should unreference them.
As a rule of thumb, you should create late, destroy early. If you
finished working with an object, dispose it.

Try also to minimize "elderly objects". .NET garbage collection is
using the notion of generations. Try to dispose objects as early as you
can and minimize references to older objects.

In general , if you don't use unmanaged resources, memory leaks should
be rare if not none. Just try not to create complicated object graphs
and if you have to create, use an appropriate dispose strategy.

 

[Jonathan Allen]

Connection pooling depends on the connection string you use. If you

want to setup connection pool you have to use the minPool, maxPool at
your connection string.

Can I have a reference for that?

 

[Tasos Vogiatzoglou]

MSDN Article for Connection pooling for SQL Server :

ms-help://MS.MSDNQTR.2005APR.1033/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.htm

 

[Scott Roberts]

Connection pooling depends on the connection string you use. If you
want to setup connection pool you have to use the minPool, maxPool at
your connection string.

The help topic for SqlConnection says:

"Note To deploy high-performance applications, you need to use connection
pooling. When you use the .NET Framework Data Provider for SQL Server, you
do not need to enable connection pooling because the provider manages this
automatically, although you can modify some settings. For more information
about using connection pooling with the .NET Framework Data Provider for SQL
Server, see Connection Pooling for the .NET Framework Data Provider for SQL
Server."

If you click the "Connection Pooling" link you get more text saying that it
happens automatically. The MinPool default is listed as "0" and the MaxPool
default is listed as "100".

If there are objects that you do not need you should unreference them.
As a rule of thumb, you should create late, destroy early. If you
finished working with an object, dispose it.

Right, but the non-intuitive (to me) referencing created as a result of
registering event handles sometimes prevents this.

 

[Scott Roberts]

MSDN Article for Connection pooling for SQL Server :

ms-help://MS.MSDNQTR.2005APR.1033/cpguide/html/cpconConnectionPoolingForSQLS
erverNETDataProvider.htm

I'm unable to get there. Could you post the pertinent text?

 

[Tasos Vogiatzoglou]

http://msdn.microsoft.com/library/d...nectionPoolingForSQLServerNETDataProvider.asp

Web Link ...