Please clarify the behaviour of ARITHABORT on database with database compatibility level >= 90

Question

I would like to check if the ARITHABORT setting might be negatively affecting my database performance.

In the article https://learn.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql?view=sql-server-ver16 the following is stated:

When ANSI_WARNINGS has a value of ON and the database compatibility level is set to 90 or higher then ARITHABORT is implicitly ON regardless of its value setting. ... To view the current setting for SET ARITHABORT, run the following query:

DECLARE @ARITHABORT VARCHAR(3) = 'OFF';  
IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';  
SELECT @ARITHABORT AS ARITHABORT;  

My database has a compatibility level of 150 and my application (using Microsoft.Data.SqlClient) has enabled ANSI_WARNINGS.

However, when i run the test script above, it shows that ARITHABORT is OFF. In the XML of the query plans created by my application I can also see that ARITHABORT="false".

Would I get different query plans if I set ARITHABORT to ON or would it not make a difference since "ARITHABORT is implicitly ON"?

Answer 1

When the setting ANSI_WARNINGS is ON, the setting of ARITHABORT has no functional effect; you will get the same result no matter ARITHABORT is ON or OFF.

However, the script above does not reflect this; it reflects the actual setting of ARITHABORT:

SET ARITHABORT OFF
go
DECLARE @ARITHABORT VARCHAR(3) = 'OFF';  
IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';  
SELECT @ARITHABORT AS ARITHABORT;
go
SET ARITHABORT ON
go
DECLARE @ARITHABORT VARCHAR(3) = 'OFF';  
IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';  
SELECT @ARITHABORT AS ARITHABORT;

When it comes to performance, the setting of ARITHABORT can lead to some confusion. You have a query that runs in the application that is slow. You extract it and run it on SSMS, and now it's fast! This is because that although ARITHABORT has no functional impact when ANSI_WARNINGS is ON, it is still a cache key. SSMS by default runs with ARITHABORT ON, so the cache entry used by the application is not used and you get a new plan.

Yes, this is very confusing. And I mean very.

For a longer discussion about this, see my article Slow in the Application, Fast in SSMS?.