Connection Pooling, Dispose/Close/Using

[Guest]

I am developing on a website that is utilizing SQL Server 2000. Shortly
after deploying the site, we began having timeout issues due to the max
connections.

1st instinct was to diligently tidy up our connections. We did so by
encapsulating with the using statement. Ex:

using (SqlConnection myConnection = new

SqlConnectionConfigurationSettings.AppSettings["ConnectionString"]))
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOME_STUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}

The problem continued, obvious by monitoring in SQL Server(Management >
Current Activity > Process Info). Though my team's research indicated the
code would be handled the same, we closed and disposed the connection in the
finally block. Ex:

try
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOMESTUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
finally
{
myConnection.Close();
myConnection.Dispose();
myConnection = null;
}

To our amazement this worked and our users on the database have decreased to
the level of our expectation.

All I have read say these two methods are equals; What is the logic that
should be followed as to when to implement each technique? Are there
variables with in our server/database envronments that could cause this
difference?

Many thanks!

 

[Guest]

My preference is finally, as it gives me the opportunity to add a catch in
error conditions. Realize, however, that the try actually runs a bit slower,
as it evaluates each statement run to ensure there are no exceptions,
regardless of whether you catch or not. I find using best for cases where you
are fairly certain you will not end in exception. I am sure there are some
who will disagree.

To your example: Although you are allowing auto dispose, with using, you are
not closing your connection explicitly. This means you could, potentially,
have tied resources until the GC comes along. The object is marked as
disposed, but that does not mean it is cleaned up yet. If you want to go back
to your first method, explicitly close() the conn, and you should see an
improvement. According to what I have read of internals, the underlying
object is released when the object is closed. You are relying on the implicit
Dispose() to Close() your object in your first example. That is not good.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[bruce barker]

1) unlike C++, the setup for a try/catch is cheap, and there is no test per
line of (look at the il). a throw is expensive though.

2) for sql connections the following is best

SqlConnection myConnection =
new
SqlConnectionConfigurationSettings.AppSettings["ConnectionString"]))
try
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOMESTUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
finally
{
myConnection.Close();
}

you want to close a connection as soon as possible, as this will return the
unmanged connection back to the pool. also Close() handles releasing all
unmanged resources, so no need to call Dispose, you can leave this for the
GC. your leak must be a bug, because Displose should call Close.

-- bruce (sqlwork.com)

My preference is finally, as it gives me the opportunity to add a catch in
error conditions. Realize, however, that the try actually runs a bit slower,
as it evaluates each statement run to ensure there are no exceptions,
regardless of whether you catch or not. I find using best for cases where you
are fairly certain you will not end in exception. I am sure there are some
who will disagree.

To your example: Although you are allowing auto dispose, with using, you are
not closing your connection explicitly. This means you could, potentially,
have tied resources until the GC comes along. The object is marked as
disposed, but that does not mean it is cleaned up yet. If you want to go back
to your first method, explicitly close() the conn, and you should see an
improvement. According to what I have read of internals, the underlying
object is released when the object is closed. You are relying on the implicit
Dispose() to Close() your object in your first example. That is not good.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

I am developing on a website that is utilizing SQL Server 2000. Shortly
after deploying the site, we began having timeout issues due to the max
connections.

1st instinct was to diligently tidy up our connections. We did so by
encapsulating with the using statement. Ex:

using (SqlConnection myConnection = new

SqlConnectionConfigurationSettings.AppSettings["ConnectionString"]))
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOME_STUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}

The problem continued, obvious by monitoring in SQL Server(Management >
Current Activity > Process Info). Though my team's research indicated the
code would be handled the same, we closed and disposed the connection in the
finally block. Ex:

try
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOMESTUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
finally
{
myConnection.Close();
myConnection.Dispose();
myConnection = null;
}

To our amazement this worked and our users on the database have decreased to
the level of our expectation.

All I have read say these two methods are equals; What is the logic that
should be followed as to when to implement each technique? Are there
variables with in our server/database envronments that could cause this
difference?

Many thanks!

 

[Scott Allen]

Hi Pieson:

The behavior is odd, because the Dispose implementation in
SqlConnection will call Close if the connection is in an open state.

Perhaps one of the methods was missed in the first pass of clean up?

 

[Guest]

Thanks All for the prompt response!

The odd thing was that all of the using blocks we used were leaving
connections open. It makes me shy away from implementing that syntax. Is
there a performance degredation from calling Close() and Dispose() in the
finally block? Would this explicitly close the pool?

Pierson

Hi Pieson:

The behavior is odd, because the Dispose implementation in
SqlConnection will call Close if the connection is in an open state.

Perhaps one of the methods was missed in the first pass of clean up?

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Thu, 14 Oct 2004 14:17:04 -0700, Pierson C <Pierson

I am developing on a website that is utilizing SQL Server 2000. Shortly
after deploying the site, we began having timeout issues due to the max
connections.

1st instinct was to diligently tidy up our connections. We did so by
encapsulating with the using statement. Ex:

using (SqlConnection myConnection = new

SqlConnectionConfigurationSettings.AppSettings["ConnectionString"]))
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOME_STUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}

The problem continued, obvious by monitoring in SQL Server(Management >
Current Activity > Process Info). Though my team's research indicated the
code would be handled the same, we closed and disposed the connection in the
finally block. Ex:

try
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOMESTUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
finally
{
myConnection.Close();
myConnection.Dispose();
myConnection = null;
}

To our amazement this worked and our users on the database have decreased to
the level of our expectation.

All I have read say these two methods are equals; What is the logic that
should be followed as to when to implement each technique? Are there
variables with in our server/database envronments that could cause this
difference?

Many thanks!

 

[Kevin Spencer]

Using the finally block is really the only way to make certain that the code
is executed. The finally block is called regardless of whether an exception
occcurred or not. Calling both Close() and Dispose() is redundant. Dispose()
will close the Connection.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

Thanks All for the prompt response!

The odd thing was that all of the using blocks we used were leaving
connections open. It makes me shy away from implementing that syntax. Is
there a performance degredation from calling Close() and Dispose() in the
finally block? Would this explicitly close the pool?

Pierson

Hi Pieson:

The behavior is odd, because the Dispose implementation in
SqlConnection will call Close if the connection is in an open state.

Perhaps one of the methods was missed in the first pass of clean up?

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Thu, 14 Oct 2004 14:17:04 -0700, Pierson C <Pierson

I am developing on a website that is utilizing SQL Server 2000. Shortly
after deploying the site, we began having timeout issues due to the max
connections.

1st instinct was to diligently tidy up our connections. We did so by
encapsulating with the using statement. Ex:

using (SqlConnection myConnection = new

SqlConnectionConfigurationSettings.AppSettings["ConnectionString"]))
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOME_STUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}

The problem continued, obvious by monitoring in SQL Server(Management >
Current Activity > Process Info). Though my team's research indicated the
code would be handled the same, we closed and disposed the connection in the
finally block. Ex:

try
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOMESTUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
finally
{
myConnection.Close();
myConnection.Dispose();
myConnection = null;
}

To our amazement this worked and our users on the database have decreased to
the level of our expectation.

All I have read say these two methods are equals; What is the logic that
should be followed as to when to implement each technique? Are there
variables with in our server/database envronments that could cause this
difference?

Many thanks!

 

[Guest]

So it is true to say that the when implementing using command with
connections, within the block call Close() as soon as possible? This is
contrary to much of what I have read(the using block itself is an alternative
to explicitly calling Close), this would be the culprit of our site's screwy
behavior.

Pierson

My preference is finally, as it gives me the opportunity to add a catch in
error conditions. Realize, however, that the try actually runs a bit slower,
as it evaluates each statement run to ensure there are no exceptions,
regardless of whether you catch or not. I find using best for cases where you
are fairly certain you will not end in exception. I am sure there are some
who will disagree.

To your example: Although you are allowing auto dispose, with using, you are
not closing your connection explicitly. This means you could, potentially,
have tied resources until the GC comes along. The object is marked as
disposed, but that does not mean it is cleaned up yet. If you want to go back
to your first method, explicitly close() the conn, and you should see an
improvement. According to what I have read of internals, the underlying
object is released when the object is closed. You are relying on the implicit
Dispose() to Close() your object in your first example. That is not good.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

I am developing on a website that is utilizing SQL Server 2000. Shortly
after deploying the site, we began having timeout issues due to the max
connections.

1st instinct was to diligently tidy up our connections. We did so by
encapsulating with the using statement. Ex:

using (SqlConnection myConnection = new

SqlConnectionConfigurationSettings.AppSettings["ConnectionString"]))
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOME_STUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}

The problem continued, obvious by monitoring in SQL Server(Management >
Current Activity > Process Info). Though my team's research indicated the
code would be handled the same, we closed and disposed the connection in the
finally block. Ex:

try
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOMESTUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
finally
{
myConnection.Close();
myConnection.Dispose();
myConnection = null;
}

To our amazement this worked and our users on the database have decreased to
the level of our expectation.

All I have read say these two methods are equals; What is the logic that
should be followed as to when to implement each technique? Are there
variables with in our server/database envronments that could cause this
difference?

Many thanks!

 

[Scott Allen]

It would not close the pool but it would 'Close' the connection, or
return the connection to the free pool depending on the settings
(pooled by default), and this is the behavior you want. I don't
understand how it could not be working for you with the using clause

--.
Scott
http://www.OdeToCode.com/blogs/scott/

 

[Sami Vaaraniemi]

SqlConnection.Dispose simply clears the connection string and calls Close.
Anyone can verify this by taking a look at the code with Reflector or some
other similar tool.

These two are functionally equivalent:

using (SqlConnection myConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOME_STUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}

and

try
{
SqlConnection myConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOMESTUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
finally
{
myConnection.Dispose();
}

Calling SqlConnection.Close in the finally block is redundant. Setting the
myConnection reference to null is unnecessary and has no effect in practice.

Your observation of connections leaks with the 'using' statement is very
strange and is contrary to MS documentation
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/h
tml/adonetbest.asp) and to my experience. As far as I know, the
'using'-statement is still the recommended way of ensuring that your
SqlConnections get cleaned up properly (at least for C# users, VB.NET coders
have no option but to use try/finally). What actually happens in the clean
up depends on connection settings. If connection pooling is on, the physical
connection is returned to the connection pool, otherwise it is closed.

Without seeing it with my own eyes, I'd say something else must be happening
somewhere that is causing the leaks.

Regards,
Sami

So it is true to say that the when implementing using command with
connections, within the block call Close() as soon as possible? This is
contrary to much of what I have read(the using block itself is an alternative
to explicitly calling Close), this would be the culprit of our site's screwy
behavior.

Pierson

My preference is finally, as it gives me the opportunity to add a catch in
error conditions. Realize, however, that the try actually runs a bit slower,
as it evaluates each statement run to ensure there are no exceptions,
regardless of whether you catch or not. I find using best for cases where you
are fairly certain you will not end in exception. I am sure there are some
who will disagree.

To your example: Although you are allowing auto dispose, with using, you are
not closing your connection explicitly. This means you could, potentially,
have tied resources until the GC comes along. The object is marked as
disposed, but that does not mean it is cleaned up yet. If you want to go back
to your first method, explicitly close() the conn, and you should see an
improvement. According to what I have read of internals, the underlying
object is released when the object is closed. You are relying on the implicit
Dispose() to Close() your object in your first example. That is not good.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

I am developing on a website that is utilizing SQL Server 2000. Shortly
after deploying the site, we began having timeout issues due to the max
connections.

1st instinct was to diligently tidy up our connections. We did so by
encapsulating with the using statement. Ex:

using (SqlConnection myConnection = new

SqlConnectionConfigurationSettings.AppSettings["ConnectionString"]))
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOME_STUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}

The problem continued, obvious by monitoring in SQL Server(Management
Current Activity > Process Info). Though my team's research indicated the
code would be handled the same, we closed and disposed the connection in the
finally block. Ex:

try
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "sp_DELETE_SOMESTUFF";
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
finally
{
myConnection.Close();
myConnection.Dispose();
myConnection = null;
}

To our amazement this worked and our users on the database have decreased to
the level of our expectation.

All I have read say these two methods are equals; What is the logic that
should be followed as to when to implement each technique? Are there
variables with in our server/database envronments that could cause this
difference?

Many thanks!

 

[speedy]

Finally block

You should never call Close or dispose in finally block, finally block is for unmanaged object only.

seach for connection pooling using sql server 2005 on the msdn