Clever Geek Handbook

ARITHABORT OFF negatively affects performance

I fully understand that SQL queries from applications are typically used SET ARITHBAORT OFF

where SSMS (by default) uses SET ARITHBAORT ON

. I also believe it SET ARITHBAORT OFF

exists only for compatibility with source code, and indeed queries should be made from SET ARITHBAORT ON

.

I have a request that is being executed as part of a C # console application batch file. The context is prepared (by default) with SET ARITHBAORT OFF

and SET ANSI_WARNINGS ON

. The first 92 calls work fine and the 93rd is always blocked (each call uses different parameters). I was able to reproduce this in SSMS if I use SET ARITHBAORT OFF

before calling the stored procedure with parameters from the 93rd call.

So to my question (sorry for the background information so far) .... In the article

Further, when it comes to ARITHABORT, you should be aware that in SQL 2005 and later this setting has zero effect if ANSI_WARNINGS is enabled. So there is no reason to include it for this.

However, I am using SQL Server 2014 and I find that:

SET ARITHBAORT ON
SET ANSI_WARNINGS ON
EXEC mySP   -- Runs efficiently

works fine but

SET ARITHBAORT OFF
SET ANSI_WARNINGS ON
EXEC mySP   -- Runs indefinitely

works endlessly. So if SET ANSI_WARNINGS ON

it makes the parameter ARITHBAORT

irrelevant, why is my request being blocked? Thank.

+3


source to share


2 answers


OK. Therefore, the quoted expression I pulled from the Sommarsog article assuming the database level is 80 or higher.

I found this paragraph on MSDN link forARITHABORT

:

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, ARITHABORT must be explicitly set to ON .



This explains:

  • Why did I get the value on change ARITHABORT

    even though it ANSI_WARNINGS

    was set to ON

    . (Database level was 80).
  • Why did the database level change to fix the problem (because I changed it to 120).
+2


source


The article you posted is misleading. If ansi_warnings is ON and arithabort is off, you as a person know that it has nothing to do with your request. The sql server engine doesn't know if it matters or not, and will automatically force you to get a new execution plan instead of using your cache plan. This means that if you have problems with sniffing parameters and have a bad plan, you will never get that bad plan and use it with aritabeta enabled. This is what makes this parameter a great test to find the sniffing parameter. Use an unknown hint optimization for the sniffing parameter instead of changing the database compatibility level, which might affect other things.



0


source






More articles:


All Articles