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
0

The sql engine is generating you a bad plan. As a starting point make sure your regular maintenance is getting fresh statistics and you don't have fragmented indexes so the engine is trying to work with valid numbers. If the basics are solid you are more than likely having parameter sniffing issues. Put simply sometimes the engine is getting a good plan and sometimes it is making a very bad estimate based on a parameter which gives you a different execution plan done in a terrible way resulting in poor performance.

Arithabort setting does not create this performance issue as you would think looking at your fast execution. .NET using the setting off and SSMS using the setting on just guarantees you won't use the same plan and it will come up with a new plan probably the correct one. But if you just turn arithabort on for .NET you will probably run into the same bad estimate issue. Rather what you want to do is leave arithabort off and then try a sample execution of the query with the setting OFF from SSMS to make it mimic running from code and setting ON to compare to help you discover when you are having a problem. You may or may not be able to figure out based on the table data where the issue lies for instance we had a table with a pseudo-PK that was 0 half the time and had a value the rest of the time so queries had trouble using it as the key is should have been.

The best way to deal with this once you discover this is a problem is the add the I found a parameter sniffing issue specific hint optimize for unknown. You should be able to search for that and find the syntax that fits your case.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.