Diagnosing SqlCeConnection memory-load issue

Discussion:

(too old to reply)

2010-01-14 16:17:01 UTC

Hi.

I am using SQl server CE 3.0 in Windows CE 5.0, and the DB read and writes
are working good, except that recently I have noticed that there maybe a
memory load issue. The memory load of the system goes up, and wont go back
down. I have a static database-Manager object that has a SqlCeConnection
variable for the lifetime of the object which happens to be the lifetime of
my executable that runs 24/7 on this device I am working on. So obviously the
SqlCeConnection variable is static too, and the associated connection is
opened in the constructor of the object, and stays open for the lifetime of
the object/executable. Then I have getter/setter methods of this object that
actually read/write to the database against that connection like so:

1)string table = "some new user specified table (different every call)";
2)SqlCeCommand cmd;
3)cmd = conn2.CreateCommand();
4)cmd.CommandText = "somegetter query (different every call)";
5)cmd.ExecuteNonQuery();

Other objects in my executable call this method over and over as need
arises(an event happens and needs to get logged to the DB, the user changes a
setpoint, etc...). I have noticed the problem so far with this setter mthod.
So I am not sure about getters. So What I have noticed is that if I leave the
code as is and crank up the frequency(100ms) of calling this method, the
memory load just keeps going up until the system becomes unresponsive, and
the same thing happens with lower frequency calls but it takes a lot longer
for the system to become unresponsive. Memory load does not go back down,
even if I stop the calls. However if I make the SqlCeConnection connetion
object creation/open/close/destruction part of this method instead of the
constructor then I do not see the momory load go up at all, no matter what
the frequency. It is almost as if "conn2.CreateCommand();" keeps making the
SqlCeConnection conn2 object fatter, which was obvioiusly not my intention or
desire. Am I doing some unkosher coding here that hopefully is obvious to
someone else? Should one expect the SqlCeConnection object to keep getting
fatter in this fashion? Should I perhaps do the "conn2.CreateCommand();" in
the constructor too and just keep changing the text on that SqlCeCommand
object instead?

The documentation for SqlCeConnection.CreateCommand Method says:
"Creates and returns a SqlCeCommand object associated with the
SqlCeConnection." I originally thought this would just return a pointer to
the same object over and over, and now I am afraid it keeps doing "new"
something in there.

I monitor the memory load with "Windows CE Remote Performance Monitor" tool.

Laxmi Narsimha Rao Oruganti [MSFT]

2010-01-19 11:27:22 UTC

Permalink

Can you not dispose the command objects?

Thanks,
Laxmi

Post by z
Hi.
I am using SQl server CE 3.0 in Windows CE 5.0, and the DB read and writes
are working good, except that recently I have noticed that there maybe a
memory load issue. The memory load of the system goes up, and wont go back
down. I have a static database-Manager object that has a SqlCeConnection
variable for the lifetime of the object which happens to be the lifetime of
my executable that runs 24/7 on this device I am working on. So obviously the
SqlCeConnection variable is static too, and the associated connection is
opened in the constructor of the object, and stays open for the lifetime of
the object/executable. Then I have getter/setter methods of this object that
1)string table = "some new user specified table (different every call)";
2)SqlCeCommand cmd;
3)cmd = conn2.CreateCommand();
4)cmd.CommandText = "somegetter query (different every call)";
5)cmd.ExecuteNonQuery();
Other objects in my executable call this method over and over as need
arises(an event happens and needs to get logged to the DB, the user changes a
setpoint, etc...). I have noticed the problem so far with this setter mthod.
So I am not sure about getters. So What I have noticed is that if I leave the
code as is and crank up the frequency(100ms) of calling this method, the
memory load just keeps going up until the system becomes unresponsive, and
the same thing happens with lower frequency calls but it takes a lot longer
for the system to become unresponsive. Memory load does not go back down,
even if I stop the calls. However if I make the SqlCeConnection connetion
object creation/open/close/destruction part of this method instead of the
constructor then I do not see the momory load go up at all, no matter what
the frequency. It is almost as if "conn2.CreateCommand();" keeps making the
SqlCeConnection conn2 object fatter, which was obvioiusly not my intention or
desire. Am I doing some unkosher coding here that hopefully is obvious to
someone else? Should one expect the SqlCeConnection object to keep getting
fatter in this fashion? Should I perhaps do the "conn2.CreateCommand();" in
the constructor too and just keep changing the text on that SqlCeCommand
object instead?
"Creates and returns a SqlCeCommand object associated with the
SqlCeConnection." I originally thought this would just return a pointer to
the same object over and over, and now I am afraid it keeps doing "new"
something in there.
I monitor the memory load with "Windows CE Remote Performance Monitor" tool.

2010-01-26 17:00:01 UTC

Permalink

Thanks Laxmi. Your solution works as well and actually is less intrusive for
a version change than my solution of destroying associated Connection object.
I guess I am still trying to understand why the SqlCeCommand object is not
getting garbage-collected across the calls to the method; seems like I
shouldn't have to do this Dispose at the end of the method.

Post by Laxmi Narsimha Rao Oruganti [MSFT]
Can you not dispose the command objects?
Thanks,
Laxmi

Laxmi Narsimha Rao Oruganti [MSFT]

2010-01-28 13:11:15 UTC

Permalink

There are multiple paths here to narrow down the issue.

1) The issue could be that SQL CE engine unloads and loads for every
connection close and open and hence keeping the connection open has become a
practice. However, can you not open a connection to some dummy database,
but have the connection to the real database closed and opened again and
again?

2) Whether it is SQL CE or any other software, on a limited resource
environment, it is better that applications dispose the objects. It is even
more important if the objects have a native handles carried with them. I am
not sure why this is being called out as less intrusive.

It may not be right thing to expect a .NET FX kind of application to work as
is on a .NET CF where the hardware environment is entirely different. It is
also important for you to realize that .NET CF GC is entirely re-architected
and is not same as .NET FX GC.

Thanks,
Laxmi

Post by z
Thanks Laxmi. Your solution works as well and actually is less intrusive for
a version change than my solution of destroying associated Connection object.
I guess I am still trying to understand why the SqlCeCommand object is not
getting garbage-collected across the calls to the method; seems like I
shouldn't have to do this Dispose at the end of the method.

Post by Laxmi Narsimha Rao Oruganti [MSFT]
Can you not dispose the command objects?
Thanks,
Laxmi

Post by z
Hi.
I am using SQl server CE 3.0 in Windows CE 5.0, and the DB read and writes
are working good, except that recently I have noticed that there maybe a
memory load issue. The memory load of the system goes up, and wont go back
down. I have a static database-Manager object that has a
SqlCeConnection
variable for the lifetime of the object which happens to be the
lifetime
of
my executable that runs 24/7 on this device I am working on. So
obviously
the
SqlCeConnection variable is static too, and the associated connection is
opened in the constructor of the object, and stays open for the
lifetime
of
the object/executable. Then I have getter/setter methods of this object that
1)string table = "some new user specified table (different every call)";
2)SqlCeCommand cmd;
3)cmd = conn2.CreateCommand();
4)cmd.CommandText = "somegetter query (different every call)";
5)cmd.ExecuteNonQuery();
Other objects in my executable call this method over and over as need
arises(an event happens and needs to get logged to the DB, the user changes a
setpoint, etc...). I have noticed the problem so far with this setter mthod.
So I am not sure about getters. So What I have noticed is that if I
leave
the
code as is and crank up the frequency(100ms) of calling this method, the
memory load just keeps going up until the system becomes unresponsive, and
the same thing happens with lower frequency calls but it takes a lot longer
for the system to become unresponsive. Memory load does not go back down,
even if I stop the calls. However if I make the SqlCeConnection connetion
object creation/open/close/destruction part of this method instead of the
constructor then I do not see the momory load go up at all, no matter what
the frequency. It is almost as if "conn2.CreateCommand();" keeps making the
SqlCeConnection conn2 object fatter, which was obvioiusly not my
intention
or
desire. Am I doing some unkosher coding here that hopefully is obvious to
someone else? Should one expect the SqlCeConnection object to keep getting
fatter in this fashion? Should I perhaps do the
"conn2.CreateCommand();"
in
the constructor too and just keep changing the text on that
SqlCeCommand
object instead?
"Creates and returns a SqlCeCommand object associated with the
SqlCeConnection." I originally thought this would just return a pointer to
the same object over and over, and now I am afraid it keeps doing "new"
something in there.
I monitor the memory load with "Windows CE Remote Performance Monitor" tool.

2010-02-02 14:06:01 UTC

Permalink

I called your method less intrusive, as-in less code change is required in my
case. Keeping one connection in the CE application was a design primitive
that allowed me to kill the connection from a central point and not have to
worry about multiple open connecions if I need to move the DB file.

I just want to mention that the static build-up that this thread refers to
has only been observed when the application using the .net connection and
command objects was running within the context of the Visual Studio debugger.
I actually did not see this buildup behaviour when the machine is running the
exe without the debugger attached. So I am not sure if the garbage-collector
is acting differenly or what. I put the dispose change in there anyways,
because it did in fact help when under the debugger.
A big Gotchya for me.

Post by Laxmi Narsimha Rao Oruganti [MSFT]
There are multiple paths here to narrow down the issue.
1) The issue could be that SQL CE engine unloads and loads for every
connection close and open and hence keeping the connection open has become a
practice. However, can you not open a connection to some dummy database,
but have the connection to the real database closed and opened again and
again?
2) Whether it is SQL CE or any other software, on a limited resource
environment, it is better that applications dispose the objects. It is even
more important if the objects have a native handles carried with them. I am
not sure why this is being called out as less intrusive.
It may not be right thing to expect a .NET FX kind of application to work as
is on a .NET CF where the hardware environment is entirely different. It is
also important for you to realize that .NET CF GC is entirely re-architected
and is not same as .NET FX GC.
Thanks,
Laxmi

Post by Laxmi Narsimha Rao Oruganti [MSFT]
Can you not dispose the command objects?
Thanks,
Laxmi

Post by z
Hi.
I am using SQl server CE 3.0 in Windows CE 5.0, and the DB read and writes
are working good, except that recently I have noticed that there maybe a
memory load issue. The memory load of the system goes up, and wont go back
down. I have a static database-Manager object that has a
SqlCeConnection
variable for the lifetime of the object which happens to be the
lifetime
of
my executable that runs 24/7 on this device I am working on. So
obviously
the
SqlCeConnection variable is static too, and the associated connection is
opened in the constructor of the object, and stays open for the
lifetime
of
the object/executable. Then I have getter/setter methods of this object that
1)string table = "some new user specified table (different every call)";
2)SqlCeCommand cmd;
3)cmd = conn2.CreateCommand();
4)cmd.CommandText = "somegetter query (different every call)";
5)cmd.ExecuteNonQuery();
Other objects in my executable call this method over and over as need
arises(an event happens and needs to get logged to the DB, the user changes a
setpoint, etc...). I have noticed the problem so far with this setter mthod.
So I am not sure about getters. So What I have noticed is that if I
leave
the
code as is and crank up the frequency(100ms) of calling this method, the
memory load just keeps going up until the system becomes unresponsive, and
the same thing happens with lower frequency calls but it takes a lot longer
for the system to become unresponsive. Memory load does not go back down,
even if I stop the calls. However if I make the SqlCeConnection connetion
object creation/open/close/destruction part of this method instead of the
constructor then I do not see the momory load go up at all, no matter what
the frequency. It is almost as if "conn2.CreateCommand();" keeps making the
SqlCeConnection conn2 object fatter, which was obvioiusly not my
intention
or
desire. Am I doing some unkosher coding here that hopefully is obvious to
someone else? Should one expect the SqlCeConnection object to keep getting
fatter in this fashion? Should I perhaps do the
"conn2.CreateCommand();"
in
the constructor too and just keep changing the text on that SqlCeCommand
object instead?
"Creates and returns a SqlCeCommand object associated with the
SqlCeConnection." I originally thought this would just return a pointer to
the same object over and over, and now I am afraid it keeps doing "new"
something in there.
I monitor the memory load with "Windows CE Remote Performance Monitor" tool.

Jin

2010-01-29 05:34:13 UTC

Permalink

Another option is to reuse the SqlCeCommand object as I do in my
project:

// Called to get the shared SqlCeCommand which is
reinitialized for Text mode.
static public SqlCeCommand GetCommandText_Shared(string
qry_string)
{
SqlCeCommand cmd = DbCommon.VoD_CommandText;
cmd.CommandType = CommandType.Text;
cmd.CommandText = qry_string;
cmd.IndexName = null;
cmd.Parameters.Clear();

return cmd;
}

Here, the DbCommon.VoD_CommandText is a property that returns a static
value which is initialized during DB open.

- Jin