DataSet won't release memory?

[Lee Grissom]

Against SS2K. I have a problem and was wondering if you might know how to
solve it. I have "SqlDataAdapter.Fill(myDataSet)" running on a separate
thread. Then I call SqlCommand.Cancel and that aborts the Fill operation.
Then I call myDataSet.Clear and myDataSet.Free (Delphi syntax). But none of
the system memory gets released until the next time around when I call
myDataSet.Create or if I quit the application. This is a huge problem for
my end users. Of course, I don't understand why the memory is being
consumed so quickly (40MB/sec), so if anyone can explain why ADO.NET is
consuming 40MB/sec whereas Query Analyzer only consumes 4MB/sec... that
would be fantastic. This is a non-GUI assembly.

BTW, my test query is "select * from sysindexes, syscomments".

Many thanks,
Lee

 

[Lee Grissom]

But none of the system memory gets released until

the next time around when I call myDataSet.Create or if I quit the
application.

I found GC.Collect. Solves my problem. The other option is to make
myDataSet a local object. Man, I'm not used to working in a GC world yet.

if anyone can explain why ADO.NET is consuming
40MB/sec whereas Query Analyzer only consumes 4MB/sec... that would
be fantastic.

Perhaps b/c of the large XML structures underneath?

 

[William Ryan eMVP]

Lee:

Calling CG.Collect is ill advised in most situations, something seems fishy
here. The Dataset is a local cached copy of your database so it's able to
eat some resources but if calling collect is clearing up that much memory,
it seems like that isn't being referenced anymore. You really don't have to
use Create either very often, and in the majority of situations, datasets
aren't of much use if they are declared locally b/c you use them throughout
a session. Can you tell me a little more about how everything is being
used? I just mention it b/c on every count the situation is pretty much the
opposite of 'normal' usage, and i wouldn't want you to go down the wrong
road.

 

[Lee Grissom]

Calling CG.Collect is ill advised in most situations, something seems
fishy here. The Dataset is a local cached copy of your database so
it's able to eat some resources but if calling collect is clearing up
that much memory, it seems like that isn't being referenced anymore.
You really don't have to use Create either very often, and in the
majority of situations, datasets aren't of much use if they are
declared locally b/c you use them throughout a session. Can you tell
me a little more about how everything is being used? I just mention
it b/c on every count the situation is pretty much the opposite of
'normal' usage, and i wouldn't want you to go down the wrong road.

I don't call create very often. Only once per query... which depends on the
end user. ds (dataset) is a local object, and none of it's memory is being
freed (even if it consumes 1 GB of RAM). I called "Clear" and that does
nothing. Essentially the application is useless until I can figure out how
to get the memory released without calling GC.Collect. Otherwise I'll have
to ship it with GC.Collect. Yes, it's very fishy... it makes no sense to
me. The OS pops up a message that tells me the system is low on memory...
"yes I know that, I did that on purpose, thankyou"... but the object that
used that memory is gone (I can safely say it's out of scope), but the GC
isn't releasing it. Wow, strange stuff.

 

[William Ryan eMVP]

I don't call create very often. Only once per query... which depends on the
end user.

Once per user session or every time the user runs a query? If it's the
latter, that could definitely be a problem.
ds (dataset) is a local object, and none of it's memory is being

freed (even if it consumes 1 GB of RAM). I called "Clear" and that does
nothing.

It won't. Those reasources are still around until they are garbage
collected. However, if you were using X ram, and called select 3 times with
the same query, you'd be killing your ram in most circumstances.
Essentially the application is useless until I can figure out how

to get the memory released without calling GC.Collect.

It sounds like it. It really sounds like you are pulling over way more data
than necessary and possibly requerying for the same data. Under those
circumstances, it's necessarily going to be a memory hog.

Otherwise I'll have

to ship it with GC.Collect. Yes, it's very fishy... it makes no sense to
me. The OS pops up a message that tells me the system is low on memory...
"yes I know that, I did that on purpose, thankyou"... but the object that
used that memory is gone (I can safely say it's out of scope), but the GC
isn't releasing it.

GC is non-deterministic, you don't know when it's going to be called (unless
of course you call it yourself which , you are recommended against doing in
almost every piece of literature.
Wow, strange stuff.

How many rows are you pulling over at a time? Are you using
DataAdapter.Update---if not, how are you doing it? A DataReader may be a
more appropriate solution for some of these and it doesn't keep a local copy
of the data. If you are hitting the db constantly, or even regularly, it
may make more sense to use one.

I don't profess to be the definitive authority by any means, but I have a
fair amount of experience with ADO.NET, and I've never seen anything
approaching 1g of Ram usage or anything in that stratosphere. I'd look to
the table size first, if you have more than a few hundred rows cached at any
given time, you may want to rethink your selection strategy. At the most,
maybe 2-5000 and even that is pretty darned large.

If this thing is throwing out of memory exceptions, (depending on the
machine of course), that's scarry if it's doing it but other programs
aren't. ADO.NET does have a footprint if you are using disconnected
objects, but it's normally pretty small and manageable.

Let me know though, I'll do what I can to help.

Bill

 

[Lee Grissom]

Let me know though, I'll do what I can to help.

In MS Query Analyzer against SQL 2K: select * from sysindexes, syscomments.
Notice that the memory grows at approximately 4MB/sec. Also notice that it
completes to its end and you should still have lots of RAM available on your
computer. Now try the same query with an ADO.NET throw-away app using
SqlDataAdapter.Fill(dataset). Does system memory consumption grow by
40MB/sec? Is the query able to complete? Does the memory get released if
you call SqlAdapter.SelectCommand.Cancel? The answers to those questions
will help me determine my sanity.

 

[Bob Clegg]

Hi Lee,
You might like to try calling dispose on your dataset just before it goes
out of scope.
regards
Bob

 

[William Ryan eMVP]

Hi Lee, see comments inline

In MS Query Analyzer against SQL 2K: select * from sysindexes, syscomments.
Notice that the memory grows at approximately 4MB/sec.

I did a fill with two tables in one dataset on both tables, and I threw in
another Really large table to boot. my total ram was 23,496k and there was
NO incremental usage if the app was just sitting there. None. I pulled
over 100,000 k records with around 70 columns that we use for performance
testing. Ram usage went up with 314,000k with all three tables, but still,
NO incremental growth.
Also notice that it

completes to its end and you should still have lots of RAM available on your
computer. Now try the same query with an ADO.NET throw-away app using
SqlDataAdapter.Fill(dataset). Does system memory consumption grow by
40MB/sec?

No, it doesn't grow at all
Is the query able to complete?
Yes.
Does the memory get released if

you call SqlAdapter.SelectCommand.Cancel?

No, it finishes very quickly before i can even call cancel although I could
do it programmatically, but that's not going to do anything.
The answers to those questions

will help me determine my sanity.

Question : Do you have a Timer Control, a System.Threading.Timer or a
separate thread running.
I've thrown everything and it's mother at this and I can't remotely get it
to happen, unless, Unless I use a timer, or spin a thread that keeps
running.

So if nothing at all is happening in the program, it shouldn't keep growing
like this. That's the most likely culprit.

 

[Lee Grissom]

Hmmm, wow, okay, hmmm... thanks Bill. If you're seeing things look okay,
then there's something else going on that I just don't know about. My
assembly is pretty simple, but it's being called from another team, so maybe
there's something else going on that I'm unaware of. I appreciate your
time, thanks again.