My apologies if this is not the most appropriate group for this, and
please feel free to redirect me.
I have written a webservice for a client. It's relatively straight
forward, with a published method taking an array of items, and writing
each item to a database. The database is an SQL Server 2005. The
program is implemented in C# using VS 2005.
The method structure is also pretty straightforward (abbreviated to
just the relevant parts):
public errorObject[] ListMethod(objectList[] newObjects)
{
SqlConnection conn = new
SqlConnection(WebConfigurationManager.ConnectionSt rings["ConnectionStringName"].ConnectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO table
(dataelemnts) VALUES (@custPhoneNumb, @timeStart, @callRecordID)");
cmd.Connection = conn;
try
{
conn.Open();
}
catch (SqlException ex)
{
// log exception
}
for (int ix = 0; ix < newObjects.GetLength(0); ix++)
{
// process objects
cmd.Parameters.Clear();
cmd.Parameters.Add("@param1", SqlDbType.Char,
dataElement1.Length).Value = dataElement1
cmd.Parameters.Add("@timeStart",
SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@callRecordID",
SqlDbType.VarChar).Value = customer.callRecordID;
try
{
err = false;
if (cmd.ExecuteNonQuery() != 1)
{
err = true;
}
}
catch (SqlException ex)
{
// log exception
}
}
try
{
conn.Close();
}
catch (SqlException ex)
{
// log exception
}
}
There are no returns or other branches that would short-circuit the
execution path without going through the try conn.Close().
After a couple months in operation, the client has observed a huge
number of open database processes (using the Activity Monitor) with
the user id that only this webservice uses. Each process is in a
"Sleeping" status, and shows a "Command" Column of "AWAITING COMMAND"
and an application of ".Net SqlClient DataProvider". This seems to be
(eventually) running into an open connection or some other resource
limitation of the server and is impacting performance.
Can anyone give me a clue as to what is happening and where these open
processes are coming from? When they first contacted me, my first
thought was I just needed to make sure I was putting in appropriate
conn.Close() calls, but on reviewing the code (it's been 3 months
since I looked at it), I see they are already there. My understanding
is that once you call conn.Close, that connection is done until you
call conn.Open() to start a new one. Is there some garbage that it
leaves behind that somehow needs to be cleaned up? Also, to insure
thread safety, I allocated the conn object within the Method call.
which means that it shoujld be garbage collected when it goes out of
scope at the end of the method, right? I've thought of adding a
conn.Dispose() at the end, but I'm not convinced that would have any
affect other than perhaps a slight performance hit. I've also thought
of making the conn object a static member object so there's only one
of them, but I'm concerned that multiple simultaneous calls to the
method might end up walking all over each other's DB interactions.
There is probably a group more centered on Database issues that this
post would be more appropriate for, but I couldn't find it. So if
anyone has any help or suggestions, I would be most appreciative.
