ARITHABORT OFF adversely affecting performance

Question

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.

http://www.sommarskog.se/query-plan-mysteries.html

Answer 1

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:

1. Why I got a difference when changing ARITHABORT even though ANSI_WARNINGS was set to ON. (Database Level was 80)
2. Why changing the Database Level appeared to fix the problem (because I changed it to 120)

Answer 2

The article you posted is misleading. If ansi_warnings is ON and arithabort is off you as a human knows this makes no difference to your query. The sql server engine does not know whether or not this makes a difference and will automatically force getting a new execution plan and not using your cached plan. This means if you are having parameter sniffing issues and have a bad plan you will never get that bad plan and use it with the arithabort being on. This is what makes that setting a great test to find parameter sniffing. Use the optimize for unknown hint for parameter sniffing instead of changing your database compatibility level which may effect other things.