Sql server disables implicit_transactions and other parameters
I am still looking into sql server and recently stumbled upon a select query in a stored procedure that was causing a very slow filling of a dataset in C #. At first I thought it was .NET related, but then it was prompted to introduce a stored procedure:
set implicit_transactions off
this seems to cure it, but I would like to know why also I have seen other options like:
- set nocount off
- install arithabort on
- set concat_null_yields_null to
- set ansi_nulls to
- set cursor_close_on_commit off
- set ansi_null_dflt_on to
- set ansi_padding to
- set ansi_warnings to
- set quoted_identifier on
Does anyone know where to find good information on what each one does and what is safe to use when I have a setup routine stored just to query data to view.
I should only note to stop normal use / not use discussion with stored procedures. These queries are complex selection commands used in several programs in several languages, this is the best place for them.
Edit: Got my answer without going through all the options completely, but found
SET INSULATION PICTURE LEVEL DO NOT MISS
Dramatically increased complex queries, I'm not worried about dirty reading in this case.
source to share
This is the page from SQL Server Books Online (BOL) you want. It explains all SET statements that can be used in a session. http://msdn.microsoft.com/en-us/library/ms190356.aspx
source to share
Oh, someone, somewhere is playing with the fire of the big time.
I've never had a production script where I had to enable implicit transactions. I always open transactions when I need them and commit them when I'm done. The problem with implicit transactions is that it is very easy to "leak" an open transaction, which can lead to dire problems. This parameter means that "please open the transaction for me when you first run the operator, if the transaction is not open, do not worry about committing it."
For example, look at the following examples:
set implicit_transactions on
go
select top 10 * from sysobjects
and
set implicit_transactions off
go
begin tran
select top 10 * from sysobjects
They both do the same thing, however it's pretty clear in the second statement that someone forgot to complete the transaction. It can get very difficult to track if you have this assignment in an obscure place.
The best place to get documentation for all given statements is the old version of the reliable SQL servers on the internet . This, along with a little experimentation in the query parser, is usually all it takes to understand most of the settings.
I highly recommend you find out who is setting up implicit transactions, find out why they are doing it, and remove the setting if not required. In addition, you must confirm that whoever uses this parameter is committing their implicitly open transactions.
What is probably going on is that you had an open transaction that blocked part of your saved process, and somewhere you have a timeout that occurs by raising an error and processing code when that timeout happens with your stored proc keeps working. I guess the delay is usually 30 seconds.
source to share
One thing to look out for is what is being passed from client to server using the profiler.
We had a weird situation where the default SET arguments for the ADO connection were causing the SP to age from time to time from the client, which we resolved by looking at what the server was getting from the client, complete with default SET arguments versus with what was sent when SSMS was executed. We then forced the client to transmit the same SET instructions as sent by SSMS.
This might be a shutdown, but it is a useful technique to use when the SP is running in a timely manner on the server but not from the client.
source to share