sql server set implicit_transactions off and other options

Question

I am still learning sql server somewhat and recently came across a select query in a stored procedure which was causing a very slow fill of a dataset in c#. At first I thought this was to do with .NET but then found a suggestion to put in the stored procedure:

set implicit_transactions off

this seems to cure it but I would like to know why also I have seen other options such as:

• set nocount off
• set arithabort on
• set concat_null_yields_null on
• set ansi_nulls on
• set cursor_close_on_commit off
• set ansi_null_dflt_on on
• set ansi_padding on
• set ansi_warnings on
• set quoted_identifier on

Does anyone know where to find good info on what each of these does and what is safe to use when I have stored procedures setup just to query of data for viewing.

I should note just to stop the usual use/don't use stored procedures debate these queries are complex select statements used on multiple programs in multiple languages it is the best place for them.

Edit: Got my answer didn't end up fully reviewing all the options but did find

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Sped up the complex queries dramatically, I am not worried about the dirty read in this instance.

Answer 1

This is the page out of SQL Server Books Online (BOL) that you want. It explains all the SET statements that can be used in a session. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql

Answer 2

Ouch, someone, somewhere is playing with fire big-time.

I have never had a production scenario where I had to enable implicit transactions. I always open transactions when I need them and commit them when I am done. The problem with implicit transactions is its really easy to "leak" an open transaction which can lead to horrible issues. What this setting means is "please open a transaction for me the first time I run a statement if there is no transaction open, don't worry about committing it".

For example have a 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 exact same thing, however in the second statement its pretty clear someone forgot to commit the transaction. This can get very complicated to track down if you have this set in an obscure place.

The best place to get documentation for all the set statements is the old trusty sql server books online. It together with a bit of experimentation in query analyzer are usually all that is required to get a grasp of most settings.

I would strongly recommend you find out who is setting up implicit transactions, find out why they are doing it, and remove the setting if its not really required. Also, you must confirm that whoever uses this setting commits their implicitly open transactions.

What was probably going on is that you had an open transaction that was blocking a bit of your your stored proc, and somewhere you have a timeout that is occurring, raising an error and being handled in code, when that timeout happens your stored proc continues running. My guess is that the delay is usually 30 seconds exactly.

Answer 3

One thing that may be worth a look at is what is passed from the client to the server by using the profiler.

We had an odd situation where the default SET arguments for the ADO connection were causing an SP to take ages to run from the client which we resolved by looking at exactly what the server was receiving from the client, complete with default SET arguments compared to what was sent when executing from SSMS. We then made the client pass the same SET statements as those sent by SSMS.

This may be way off track but it is a useful method to use when the SP executes in a timely fashion on the server but not from the client.

Answer 4

I think you need to look deeper into your stored procedure. I don't think that SET IMPLICIT_TRANSACTIONS is really going to be what's sped up your procedure, I think it's probably a coincidence.