Otdfbt

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? Any impact on replication by chance? AG considerations? To me... since SSMS has this set ON by default, I see no harm in setting this ON server-wide for all connections. Am I approaching this issue correctly? At the end of the day, I just need this SQL Server to perform above all else.

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.