sp_reset_connection needed?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All,

By default, that SP is ran after every call I make to the database. I can prevent it from by adding "Connection Reset=FALSE" to my connection string.

What is recommended?

Thanks
 
This SP is used to clear the state of the connection when you inherit it
from the connection pool. If you know FOR CERTAIN that there are no residual
objects tied to the previous connection owner (which is likely a previous
instance of your own process/ASP page etc.), it's safe to turn off this
feature.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

JL said:
All,

By default, that SP is ran after every call I make to the database. I can
Click to expand...
prevent it from by adding "Connection Reset=FALSE" to my connection string.
 
If you know FOR CERTAIN that there are no residual objects tied to the previous connection owner <snip>

How would this manifest itself? Could you provide a simple example?

JL
 
John,
First of all, if you are not using Sql Server 7 there is _no benefit_ to
turning off sp_reset_connection. We have worked very hard to make this as
efficient as possible in Sql Server 2000 and Yukon. It is highly recommended
that you do not turn this off, in fact we seriously considered taking this
property out altogether. The types of issues that have surfaced when people
turn this off to get "better performance" are scary.

The only reason we decided to leave this feature in is because there is a
performance benefit to avoiding this call when using Sql Server 7. If you
are using Sql Server 7 and you can _absolutely guarantee_ that you will not
do anything that will change the state of the connection you may want to
tweak this for applications that require the highest performance. The most
common way to modify connection state is to do things like change database
and begin transaction. Most of the really bad cases I have seen involve
connections that are closed with a transaction active, with sp_reset the
transaction would roll back before you reuse the connection, without it you
can get a connection that will roll back all the changes you are trying to
do!
 
Thank you, Angel. Information straight from the source. =)

JL

Angel Saenz-Badillos said:
John,
First of all, if you are not using Sql Server 7 there is _no benefit_ to
turning off sp_reset_connection. We have worked very hard to make this as
efficient as possible in Sql Server 2000 and Yukon. It is highly recommended
that you do not turn this off, in fact we seriously considered taking this
property out altogether. The types of issues that have surfaced when people
turn this off to get "better performance" are scary.

The only reason we decided to leave this feature in is because there is a
performance benefit to avoiding this call when using Sql Server 7. If you
are using Sql Server 7 and you can _absolutely guarantee_ that you will not
do anything that will change the state of the connection you may want to
tweak this for applications that require the highest performance. The most
common way to modify connection state is to do things like change database
and begin transaction. Most of the really bad cases I have seen involve
connections that are closed with a transaction active, with sp_reset the
transaction would roll back before you reuse the connection, without it you
can get a connection that will roll back all the changes you are trying to
do!
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.


previous connection owner said:
How would this manifest itself? Could you provide a simple example?

JL
Click to expand...
Click to expand...
 
Back
Top