Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection.

For performance reasons I've created an indexed view. As a result, updates to tables referenced in the view need to be done with ARITHABORT ON. However, the profiler shows that SqlClient is connecting with ARITHABORT OFF, so updates to those tables are failing.

Is there a central configuration setting to make SqlClient use ARITHABORT ON? The best I've been able to find is to manually execute that each time a connection is opened, but updating the existing code base to do this would be a fairly large task so I'm keen to find a better way.

share|improve this question
1  
Can't you use a for logon trigger? See msdn.microsoft.com/en-us/library/bb326598%28v=sql.100%29.aspx –  Colin 't Hart Mar 12 at 12:51
1  
@Colin'tHart, I'm mystified by the sudden burst of activity this 4-year-old question has received, and I currently don't have access to any machine with SQL Server to test the new answers. But yours seems like the best so far, so it would be great if you would post it as an answer rather than a comment. –  Peter Taylor Mar 12 at 13:20
    
Someone else has offered a bounty on this question. –  Colin 't Hart Mar 12 at 13:26
    
As you discovered in the documentation, "If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger." So the for logon trigger approach won't work. –  Colin 't Hart Mar 12 at 15:37
1  
@srutzky, I ended up refactoring, probably about a week after posting the question. And I no longer even work at the same company. Also, surprisingly, I wasn't using EF6 two years before it was released ;) –  Peter Taylor Mar 13 at 14:37

4 Answers 4

The not-so-good news is that I have done a lot of searching on this topic, only to find that over the years a lot of others have done a lot of searching on this topic, and there is no way to configure the behavior of SqlClient. Some MSDN documentation implies that it can be done via a ConnectionString, but there are no Keywords that would allow for altering these settings. Another document implies it can be changed via Client Network Configuration / Configuration Manager, but that does not seem possible either. Hence, and rather unfortunately, you will need to execute SET ARITHABORT ON; manually. Here are some ways to consider:

IF you are using Entity Framework 6 (or newer), you can try either:

  • Use Database.ExecuteSqlCommand: context.Database.ExecuteSqlCommand("SET ARITHABORT ON;");
    Ideally this would be executed once, after opening the DB connection, and not per each query.

  • Create an interceptor via either:

    This will allow you to modify the SQL before it is executed, in which case you can simply prefix it with: SET ARITHABORT ON;. The downside here is that it will be per each query, unless you store a local variable to capture the state of whether or not it has been executed and test for that each time (which really isn't that much extra work, but using ExecuteSqlCommand is probably easier).

Either of those will allow you to handle this in one spot without changing any existing code.

ELSE, you could create a wrapper method that does this, similar to:

public static SqlDataReader ExecuteReaderWithSetting(SqlCommand CommandToExec)
{
  CommandToExec.CommandText = "SET ARITHABORT ON;\n" + CommandToExec.CommandText;

  return CommandToExec.ExecuteReader();
}

and then just change the current _Reader = _Command.ExecuteReader(); references to be _Reader = ExecuteReaderWithSetting(_Command);.

Doing this also allows for the setting to be handled in a single location while requiring only minimal and simplistic code changes that can be mostly done via Find & Replace.

Better yet (Else Part 2), since this is a connection level setting, it doesn't need to be executed per each SqlCommand.Execute__() call. So instead of creating a wrapper for ExecuteReader(), create a wrapper for Connection.Open():

public static void OpenAndSetArithAbort(SqlConnection MyConnection)
{
  using (SqlCommand _Command = MyConnection.CreateCommand())
  {
    _Command.CommandType = CommandType.Text;
    _Command.CommandText = "SET ARITHABORT ON;";

    MyConnection.Open();

    _Command.ExecuteNonQuery();
  }

  return;
}

And then just replace the existing _Connection.Open(); references to be OpenAndSetArithAbort(_Connection);.

Both of the above ideas can be implemented in more OO style by creating a Class that extends either SqlCommand or SqlConnection.

Or Better yet (Else Part 3), you can create an event handler for the Connection StateChange and have it set the property when the connection changes from Closed to Open as follows:

protected static void OnStateChange(object sender, StateChangeEventArgs args)
{
    if (args.OriginalState == ConnectionState.Closed
        && args.CurrentState == ConnectionState.Open)
    {
        using (SqlCommand _Command = ((SqlConnection)sender).CreateCommand())
        {
            _Command.CommandType = CommandType.Text;
            _Command.CommandText = "SET ARITHABORT ON;";

            _Command.ExecuteNonQuery();
        }
    }
}

With that in place, you only need to add the following to each place where you create a SqlConnection instance:

_Connection.StateChange += new StateChangeEventHandler(OnStateChange);

No changes to existing code are needed. I have just tried this method in a small console app, testing by printing the result of SELECT SESSIONPROPERTY('ARITHABORT');. It returns 1, but if I disable the Event Handler, it returns 0.


For the sake of completeness, here are some things that do not work (either at all or not as effectively):

  • Logon Triggers: Triggers, even while running in the same session, and even if running within an explicitly started transaction, is still a sub-process and hence its settings (SET commands, local temporary tables, etc) are local to it and do not survive the end of that sub-process.
  • Adding SET ARITHABORT ON; to the beginning of each stored procedure:
    • this requires a lot of work for existing projects, especially as the number of stored procedures increases
    • this does not help ad hoc queries

UPDATE

I was under the impression that the following had been tested already by others, especially based on some of the comments. But my testing shows that these two methods do indeed work at the DB level, even when connecting via .NET SqlClient. They need to be tested and verified by others, just in case somehow what I am seeing is not accurate for other environments. If this works for others then I will move up to the top of the answer.

Server-wide

You can set the user options server configuration setting to be whatever it is currently bit-wise ORed with 64 (the value for ARITHABORT):

DECLARE @Options TABLE ([name] NVARCHAR(35), [minimum] INT, [maximum] INT,
                        [config_value] INT, [run_value] INT);
DECLARE @Value INT;

INSERT INTO @Options ([name], [minimum], [maximum], [config_value], [run_value])
  EXEC sp_configure 'user_options';

SELECT @Value = [config_value] | 64
FROM   @Options;

EXEC sp_configure 'user_options', @Value;
RECONFIGURE;

SELECT * FROM @Options; -- prior state
EXEC sp_configure 'user_options'; -- current state

Database-level

This can be set per-database via ALTER DATABASE SET:

USE [master];
GO
ALTER DATABASE [{database_name}] SET ARITHABORT ON WITH NO_WAIT;
GO
share|improve this answer
1  
I was trying to find some such hooks in the .Net libraries. Good find. –  Colin 't Hart Mar 12 at 16:18
2  
@srutzky: Your If Then ELSE ELSE2 ELSE3 is not valid syntax :-) but it is a comprehensive reply to OP question and it looks as if it is the solution to our problem. –  Henrik Staun Poulsen Mar 13 at 12:42
1  
@srutzky I'm curious if it works too. I hadn't tested mine, but assumed Henrik had verified when I posted mine. –  John Mar 18 at 21:33
    
@John I hadn't seen your comment here as I was in the middle of posting on your answer so that both you and Henrik would get a notification :) –  srutzky Mar 18 at 21:38

I am NOT an expert here but you can try something like below.

String sConnectionstring;
sConnectionstring = "Initial Catalog=Pubs;Integrated Security=true;Data Source=DCC2516";

SqlConnection Conn = new SqlConnection(sConnectionstring);

SqlCommand blah = new SqlCommand("SET ARITHABORT ON", Conn);
blah.ExecuteNonQuery();


SqlCommand cmd = new SqlCommand();
// Int32 rowsAffected;

cmd.CommandText = "dbo.xmltext_import";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = Conn;
Conn.Open();
//Console.Write ("Connection is open");
//rowsAffected = 
cmd.ExecuteNonQuery();
Conn.Close();

Ref: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d9e3e8ba-4948-4419-bb6b-dd5208bd7547/

share|improve this answer
    
Yes, that's what I meant by manually executing it. The thing is that the codebase I'm working with has accrued quite a bit of technical debt when it comes to the DB access layer, so I'll have to refactor a few hundred methods to do it this way. –  Peter Taylor May 3 '11 at 19:45

Option 1

Aside from Sankar's solution, setting the arithmetic abort setting at the server level for all connections will work:

EXEC sys.sp_configure N'user options', N'64'
GO
RECONFIGURE WITH OVERRIDE
GO

As of SQL 2014 it's recommended to be on for all connections:

You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.

So this would seem to be the ideal solution.

Option 2

If option 1 is not viable and you use stored procedures for most of your SQL calls (which you should, see Stored Procedures vs. Inline SQL) then simply enable the option in each relevant stored procedure:

CREATE PROCEDURE ...
AS 
BEGIN
   SET ARITHABORT ON
   SELECT ...
END
GO

I believe the best real solution here is to simply edit your code, as it's wrong and any other fix is merely a workaround.

share|improve this answer
    
I do not think it helps setting it on for the SQL Server, when the .net connection starts off with set ArithAbort off. I was hoping for something that could be done on the .net / C# side. I put up the bounty, because I'd seen the recommendation. –  Henrik Staun Poulsen Mar 10 at 6:51
    
The .net/C# side is what Sankar covered, so these are pretty much the only options. –  John Mar 10 at 13:08
    
@HenrikStaunPoulsen Regarding the C# side, are you using Entity Framework by any chance? –  srutzky Mar 12 at 15:41
1  
@srutzky; I'm waiting for my colleagues to come back with an answer. I'm not much of a .net coder myself. –  Henrik Staun Poulsen Mar 19 at 7:28
1  
@HenrikStaunPoulsen I believe John meant to reference your ID in that last comment ;-). Also, are you sure that connections from .NET issue a SET ARITHABORT OFF? The documentation does not say that it does (same for NUMERIC_ROUNDABORT) while it does state that the other options are explicitly set when making the connection. It also states that ARITHABORT is implied as ON if ANSI_WARNINGS is ON, and ANSI_WARNINGS is explicitly set to ON (which Martin also mentioned in a comment). My testing on an Indexed View confirms that it just works, even if not doing any of the recommended fixes. –  srutzky Mar 20 at 19:25

There is no setting to force SqlClient to always set ARITHABORT on, you have to set this as you describe.

Interestingly from the Microsoft documentation for SET ARITHABORT: -

You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.

And yet the .Net connection is hard-coded to set this off by default?

As another point, you have to be very careful when diagnosing performance issues with this setting. Different set options will result in different query plans for the same query. Your .Net code could experience a performance issue (SET ARITHABORT OFF) and yet when you run the same TSQL query in SSMS (SET ARITHABORT ON by default) it could be fine. This is because the .Net query plan will not be reused and a new plan generated. This could potentially eliminate a parameter sniffing problem for example and give much better performance.

share|improve this answer
1  
hi Andy, Yes, me too; I find it a strange default. –  Henrik Staun Poulsen Mar 12 at 14:44
1  
@HenrikStaunPoulsen - Unless you are stuck using 2000 (or 2000 compatibility level) it doesn't make any difference though. It is implied by ANSI_WARNINGS on in later versions and things like indexed views work fine. –  Martin Smith Mar 14 at 21:12
    
@Martin Smith; That comment was a super thing to start of on a Monday. Thank you very much. –  Henrik Staun Poulsen Mar 16 at 10:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.