Memory leak when using OleDb

  • Thread starter Thread starter Arild Bakken
  • Start date Start date
A

Arild Bakken

Hi,

I'm facing a problem where using the System.Data.OleDb library to read
binary data from a SQL Server 2000 database. The sample code:


OleDbConnection objConn = new OleDbConnection("Provider=SQLOLEDB;Data
Source=SQLSERVERNAME;Initial Catalog=pubs;Integrated Security=SSPI");
objConn.Open();

OleDbCommand objCmd = new OleDbCommand("SELECT logo, pr_info FROM pub_info
WHERE pub_id = ?", objConn);
objCmd.CommandType = CommandType.Text;
objCmd.Parameters.Add("@pubid", 736);

OleDbDataReader objReader = objCmd.ExecuteReader();
objReader.Read();
objReader.Close();

objCmd.Dispose();
objConn.Close();
objConn.Dispose();


Running this in a loop, say 2000 times you'll see that the memory of the
process keeps growing and growing (on my computer it passed 128MB). The data
retrieved is pretty small (643 byte logo and 64KB info) and running the same
type of code against a database with, say a 5MB word document will
dramatically eat up the memory. I tested that, and the process ate 10MB of
memory every second (it carried out two requests per second) so it would
seem the entire blob of data retrieved was leaked...

If, however, I update the code to use the System.Data.SqlClient library
instead with its SqlConnection, SqlCommand and SqlDataReader objects (and
modify the select statement and connection string) then the leak is gone.

Does anyone have any idea what I'm missing here? Now, I could change all my
code to use SqlClient instead, but I really can't believe that there is such
a tremendous leak in the OleDb libraries of .NET nor in the OleDb native
libraries so I'm guessing I've missed something on the Close() / Dispose()
requirements - and if that's the case I might end up with the same problem
with the SqlClient libraries later...

BTW - this is .NET 1.1 and sample has been run on Win2K SP4 with all
patches, and on WinXP SP1 with all patches.

Any tips as very welcome.


Regards,

Arild
 
Arild Bakken said:
Hi,

I'm facing a problem where using the System.Data.OleDb library to read
binary data from a SQL Server 2000 database. The sample code:


OleDbConnection objConn = new OleDbConnection("Provider=SQLOLEDB;Data
Source=SQLSERVERNAME;Initial Catalog=pubs;Integrated Security=SSPI");
objConn.Open();

OleDbCommand objCmd = new OleDbCommand("SELECT logo, pr_info FROM pub_info
WHERE pub_id = ?", objConn);
objCmd.CommandType = CommandType.Text;
objCmd.Parameters.Add("@pubid", 736);

OleDbDataReader objReader = objCmd.ExecuteReader();
objReader.Read();
objReader.Close();

objCmd.Dispose();
objConn.Close();
objConn.Dispose();


Running this in a loop, say 2000 times you'll see that the memory of the
process keeps growing and growing (on my computer it passed 128MB). The
data retrieved is pretty small (643 byte logo and 64KB info) and running
the same type of code against a database with, say a 5MB word document
will dramatically eat up the memory. I tested that, and the process ate
10MB of memory every second (it carried out two requests per second) so it
would seem the entire blob of data retrieved was leaked...

If, however, I update the code to use the System.Data.SqlClient library
instead with its SqlConnection, SqlCommand and SqlDataReader objects (and
modify the select statement and connection string) then the leak is gone.

Does anyone have any idea what I'm missing here? Now, I could change all
my code to use SqlClient instead, but I really can't believe that there is
such a tremendous leak in the OleDb libraries of .NET nor in the OleDb
native libraries so I'm guessing I've missed something on the Close() /
Dispose() requirements - and if that's the case I might end up with the
same problem with the SqlClient libraries later...

BTW - this is .NET 1.1 and sample has been run on Win2K SP4 with all
patches, and on WinXP SP1 with all patches.

Any tips as very welcome.
Click to expand...

There is a persistent difficulty with COM interop (OleDB is COM). .NET code
accesses COM objects throught Runtime Callable Wrappers. These wrappers are
finalizable .NET objects that hold the interface pointers to COM components.
When the Runtime Callable Wrapper is Finalized it will decrement the
reference count for the COM component. When the reference count for the COM
pmponent goes to 0, it will be destroyed and its memory freed.

The problem comes in with the Garbage Collector. These Runtime Callable
Wrappers are very small objects, and thousands of them can be created
without the Garbage Collector noticing that a ton of memory is being
allocated.

This memory will be released, eventually, after a full GC. This is better
in .NET 2.0, but I suspect that's the reason the memory footprint of your
application is higher using the OleDb provider than the SqlClient.

David
 
Would putting those wrapper declarations into "using" blocks encourage the
GC to be a bit more aggressive with collection?
 
Probably not. That doesn't affect how the objects are disposed and
dereferenced.

Is it a real problem? The main reason that the code is using a lot of
memory is that you are creating a lot of connections and very few other
objects. In a real situation you would create a lot of other objects
that would cause garbage collections to occur more frequently.
 
Well... the problem I have is in a huge application that creates a lot of
other objects and uses a lot of memory. And I can see that the memory does
sometimes "go down a bit", but that is only a couple of megabytes, and then
it startes growing again. The problem is that even after running for 6-8
hours the huge memory chunks are still not released.


Arild
 
Then you should use a profiler to see what it is that really uses up the
memory. Even if your test code does use a lot of memory, it's not
certain that the live application uses memory for the same reason.
 
We have done some research, and can see that .NET CLR memory total committed
bytes performance counter shows about 80MB (varies a bit up and down) of
memory after running for a while, and the rest of the 600MB must then be
unmanaged. The unmanaged memory increases constantly as we access the parts
of the application that load binary images from the SQL Server.

I would like to look further into this, do you have any tips as what to look
for? Tools to use?


Arild
 
Back
Top