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.
Best Answer
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 theSQL_*
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 toLatin1_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 valueARITHABORT ON
SETUP
I tested with both SQLCMD (which uses ODBC) and LINQPad (which uses .NET SqlClient):
(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:
TEST 1: Before
SQLCMD returns:
LINQPad returns:
CHANGE DEFAULT CONNECTION OPTION:
The following T-SQL enables
ARITHABORT
without removing any other options that might be set, and without changing anything ifARITHABORT
is already set in the bitmasked value.TEST 2: After
SQLCMD returns:
LINQPad returns:
Conclusion
Given that:
ARITHABORT OFF
ARITHABORT ON
OFF
ARITHABORT
, thus they accept the default settingI 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.