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.
- 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:
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?