8

So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of SET ARITHABORT OFF. With that said, I've read various articles that debate the best way to implement this. For me, I just want an easy way because SQL Server is suffering and my query tuning hasn't fully transcended across the app (and obviously adding the SET in a sp DOES NOT WORK).

In Erland Sommarskog's brilliant article about the topic, he basically suggests taking the safe approach by altering the app to issue SET ARITHABORT ON for the connection. However, in this answer from a dba.stackexchange question, Solomon Rutzky offers both an instance-wide and database-wide approach.

What ramifications am I missing here with setting this instance-wide? As I see it ... since SSMS has this set ON by default, I see no harm in setting this ON server-wide for all connections. At the end of the day, I just need this SQL Server to perform above all else.

  • Reading over much of my answer that you linked to in the question, it now seems that it's OFF by default, some clients specifically turn it ON, but EF / SqlClient do not touch it, which means it remains as OFF. – Solomon Rutzky May 10 at 18:15
  • 1
    No EF, but you raise an interesting point with how EF can override the instance-wide setting. So, if a connection can override this, obviously the most reliable place to have this set is from within the connection that is established to SQL Server, if at all possible... – Eric Swiggum May 10 at 20:06
10

There are some defaults that exist merely because nobody really knows what the effect of changing them would be. For example, the default instance-level collation when installing on a system that uses "US English" as the OS language is SQL_Latin1_General_CP1_CI_AS. This makes no sense since the SQL_* collations are for pre-SQL Server 2000 compatibility. Starting in SQL Server 2000 you could actually choose a Windows collation, and so the default for US English systems should have been changed to Latin1_General_CI_AS. BUT, I guess nobody at Microsoft really knows what the impact will be to all of the various potential sub-systems and system stored procedures, etc.

So, I am not aware of any specific negative impact of setting it to ON as either a database default or even instance-wide. At the same time, I have not tested it. But even if I had tested it, I might still not use the same code paths as your application, so this is something that you really need to test in your environment. Set it to ON at the instance level in your Dev and QA environments and see how that works for a month or two. Then enable it in Staging / UAT. If all continues to go well for several weeks, roll that config change to Production. The key is to give as much time as possible for testing various code paths that are not hit daily. Some are hit weekly or months or annually. Some code paths are only hit by support, or some ad hoc report or maintenance proc that someone created years ago and never told you about and only gets used at random intervals (nah, that never happens ;-).

So, I did some testing on an instance that still has the default "user options" setting as I have never changed it.

Please note:

  • @@OPTIONS / 'user options' is a bitmasked value
  • 64 is the bit for ARITHABORT ON

SETUP

I tested with both SQLCMD (which uses ODBC) and LINQPad (which uses .NET SqlClient):

SQLCMD -W -S (local) ^
-Q"SELECT CONCAT(DB_NAME(), N': ', @@OPTIONS & 64, N' (', ses.[client_interface_name], N')') FROM sys.dm_exec_sessions ses WHERE ses.[session_id] = @@SPID;"
echo .

(the ^ is the DOS line continuation character; the . on the last line is just to force the extra line to make it easier to copy-and-paste)

In LINQPad:

using (SqlConnection connection =
    new SqlConnection(@"Server=(local);Trusted_Connection=true;Database=tempdb;"))
{
  using (SqlCommand command = connection.CreateCommand())
  {
    command.CommandText = @"SELECT @RetVal =
CONCAT(DB_NAME(), N': ', @@OPTIONS & 64, N' (', ses.[client_interface_name], N')')
FROM  sys.dm_exec_sessions ses
WHERE ses.[session_id] = @@SPID;";
    SqlParameter paramRetVal = new SqlParameter("@RetVal", SqlDbType.NVarChar, 500);
    paramRetVal.Direction = ParameterDirection.Output;
    command.Parameters.Add(paramRetVal);

    connection.Open();
    command.ExecuteNonQuery();

    Console.WriteLine(paramRetVal.Value.ToString());
  }
}

TEST 1: Before

SQLCMD returns:

master: 0 (ODBC)

LINQPad returns:

tempdb: 0 (.Net SqlClient Data Provider)

CHANGE DEFAULT CONNECTION OPTION:

The following T-SQL enables ARITHABORT without removing any other options that might be set, and without changing anything if ARITHABORT is already set in the bitmasked value.

DECLARE @UserOptions INT;

-- Get current bitmasked value and ensure ARITHABORT is enabled:
SELECT @UserOptions = CONVERT(INT, cnf.[value_in_use]) | 64 -- enable "ARITHABORT"
FROM   sys.configurations cnf
WHERE  cnf.[configuration_id] = 1534 -- user options

-- Apply new default connection options:
EXEC sys.sp_configure N'user options', @UserOptions;
RECONFIGURE;

TEST 2: After

SQLCMD returns:

master: 64 (ODBC)

LINQPad returns:

tempdb: 64 (.Net SqlClient Data Provider)

Conclusion

Given that:

  1. There does not seem to be any benefit to having ARITHABORT OFF
  2. There is benefit to having ARITHABORT ON
  3. The default connection setting (unless overridden by the connection) = OFF
  4. It does not appear that either ODBC or OLEDB / .NET SqlClient attempt to set ARITHABORT, thus they accept the default setting

I would suggest changing the instance-wide default connection options (as shown above). This would be less obtrusive than updating the application. I would only update the app if you find a problem with changing the instance-wide setting.

P.S. I did a simple test with changing tempdb and not changing the instance-wide setting and it did not seem to work.

  • 1
    Huh, after reading this Q&A with Paul White on the subject, it appears that passing SET ANSI_WARNINGS ON, implicitly sets ARITHABORT to ON. HOWEVER, if SET ARITHABORT OFF is also passed in the connection, even with ANSI_WARNINGS overriding it, SQL server will still "act" like ARITHABORT is OFF, as in choosing strange plans. I find this... mind-boggling. So without a doubt, this needs to be set in the connection AND SET ARITHABORT OFF can't even exist. Case closed in my eyes... sqlservercentral.com/forums/topic/… – Eric Swiggum May 12 at 4:35
  • @EricSwiggum Interesting thread you found. However, I don't see how you conclude from that info that it needs to be set in the connection. If nothing is currently overriding it, then we know that SET ARITHABORT OFF is not present. So why worry about it being present? the only instance we have seen where the default is overridden is SSMS setting it to ON (which is a good thing). Either way, I have updated my answer with testing and what I see as the most logical recommendation (per existing info). – Solomon Rutzky May 12 at 16:46
  • 1
    I agree, enable the instance-wide setting as default. But ultimately the connection controls what is set. What about the case of shared instances where various technologies/protocols are connecting to SQL? I mean, do I have to run around and yell "SET ARITHABORT ON" to development like a mad man? or at the very least, promote the absence of ARITHABORT OFF if possible.. I also find it odd that I haven't seen this come to a head till now, my 6th year as a full-time DBA. or maybe it is a case where I haven't looked into it hard enough. Back me up Paul or Erland, LOL, what's up with this? – Eric Swiggum May 12 at 18:37
  • @EricSwiggum 1) You wouldn't need to run around yelling anything if you change the instance-level default. 2) You would only need to promote the absence of ARITHABORT OFF if you find any actual incidences of it. So far there are likely none. 3) Since this affects query plans, it could be that the tables never had enough data to cause SQL Server to have certain choices to consider, where now there are more choices, and some are bad. Or perhaps there are other transient factors, such as out of date stats, etc. Not entirely sure. – Solomon Rutzky May 12 at 19:44
  • @EricSwiggum I'm not disagreeing with you there. I think this is very similar to what I mentioned at the start of my answer (i.e. the default collation for US English systems): Microsoft's fear of legacy systems getting errors upon upgrading (i.e. backwards compatibility guarantee) does more harm than good as it actually increases the install-base / scope of the non-ideal scenario. This makes it an ever increasing pull to remain in the past, and an ever-decreasing chance of things getting better. These are cases where it's better to rip the band-aid off and just get the pain over with now. – Solomon Rutzky May 13 at 4:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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