0

A few days ago I happen to have stumbled on a lot of cases in a new DB I've been given where ARITHABORT set to OFF literally devastate performances. Being my first time facing this specific problem I tried to read as much as possible about it, but I still have some hard time building a definitive picture.

The evidence:

  • ARITHABORT is one of the parameter that is officially documented by MS to be used in the choice of the plan to use
  • Plans created with ARITHABORT set to ON and set to OFF are clearly different
  • All versions of SQL Server ships with ARITHABORT set to ON
  • Microsoft recommends to keep it always set to ON
  • .NET always set it to OFF, which can, and do, leads to some horrible performances

Aside from wondering why .NET always set it to OFF, the matter would look quite settled, and thus I'd proceed to explain it to my tech leader, and that we need to set it back to ON every time we open a connection to the server.

But then, it seems there are quite a few voices advocating the contrary, one especially is linked in the second answer to this question:

Why would SET ARITHABORT ON dramatically speed up a query?

I've tried to go through the entire document, but he lost me quite quickly, especially in the part where he says that ARITHABORT is un-influent when compatibility lever is set at 80 or more, which is not clearly the case. And he lost me even more when, like many others, seems quite firm on stating that Microsoft documentation is wrong about the importance of the ARITHABORT parameter. On top of that, he suggests that if the SP is a lot slower when run from code and it's extremely faster when run from SSMS, the fault is in the SSMS and we should instead correct this one so that it set ARITHABORT to off.

Quite honestly...I'm a little lost. Is there some definitive evidence about the entire stuff?

Should we always force .Net to use ARITHABORT set to on, accordingly to with what the company who produce SQL Server says, or what?

  • 1
    .NET doesn't set ARITHABORT OFF. It is actually unspecified and defaults to ON for all databases in compatibility level 90 (SQL 2005) and later. – Dan Guzman May 4 '18 at 15:05
  • Are you referring to Ben Hoffman's answer? – RDFozz May 4 '18 at 15:16
  • Also, it may help to specify which version & edition of SQL Server you're using. – RDFozz May 4 '18 at 15:18
  • Please see the discussion and options in my answer here: Make SqlClient default to ARITHABORT ON. This question is possibly a duplicate of that one. – Solomon Rutzky May 4 '18 at 15:33
  • It's not so much that ARITHABORT OFF makes a query worse, it's just that SQL Server has to generate a new plan if the current query has it OFF and the existing plan in the cache had it ON. All kinds of things can lead to those two plans being very different, and some of which will actually wipe the old plan (so comparing before and after these things becomes tougher), including changes in data, table structure, indexes, and updating statistics. – Aaron Bertrand May 4 '18 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.