I am fully aware that SQL Queries from applications typically use SET ARITHBAORT OFF where as SSMS (by default) uses SET ARITHBAORT ON. I also believe that SET ARITHBAORT OFF is only there for legacy compatibility and really queries should be run with SET ARITHBAORT ON.
I've got a query that runs as part of a C# Console App batch file. The context is prepared (by default) with SET ARITHBAORT OFF and SET ANSI_WARNINGS ON. The first 92 calls execute fine and the 93rd always locks up (each call uses different parameters). I've been able to reproduce this in SSMS if I use SET ARITHBAORT OFF prior to calling the Stored Procedure with the parameters from the 93rd call.
So on to my question (sorry for the background info so far).... The Erland Sommarskog article states:
Next, when it comes to ARITHABORT, you should know that in SQL 2005 and later versions, this setting has zero impact as long as ANSI_WARNINGS is ON. Thus, there is no reason to turn it on for the sake of the matter.
However, I am using SQL Server 2014 and I find that:
SET ARITHBAORT ON
SET ANSI_WARNINGS ON
EXEC mySP -- Runs efficiently
runs fine but
SET ARITHBAORT OFF
SET ANSI_WARNINGS ON
EXEC mySP -- Runs indefinitely
runs indefinitely. So if SET ANSI_WARNINGS ON makes the ARITHBAORT option irrelevant, why do does my query lock up?
Thanks.
OK. So the quoted statement that I've pulled from the Sommarskog article is under the condition that the Database Level is 80 or higher.
I found this paragraph in the MSDN reference for ARITHABORT:
Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must be explicitly set to ON.
This explains:
ARITHABORT even though ANSI_WARNINGS was set to ON. (Database Level was 80)