Question: Does ARITHABORT Setting Negatively Impact SQL Server Performance?
Answer: Yes.
Let us understand the answer a bit more in detail. When the ARITHABORT is set to ON in your logon sessions, you are in safe hand but due to any reasons, if you turn that to OFF, it will negatively impact query optimizations. Honestly, I have seen at many client’s spaces when this setting is set to ON, it absolutely gives the poorest performance of the server.
The default settings for the SQL Server Management Studio (SSMS) for ARITHABORT is ON. If the setting was set to off, it will build a very different execution plan and that will eventually lead to unexpected query performance.
If you are the one who has experience situation where your query runs extremely slow on production and runs faster on SQL Server Management Studio (SSMS), trust me there are three such settings which could have done this. One of such setting is ARITHABORT. (I do talk about such three settings in my Comprehensive Database Performance Health Check.
I always make sure when I am called to troubleshoot slow running performance queries, I always check this setting with SSMS and application. If they are different, there you go – you know the problem.
Here is the script to turn off this setting:
SET ARITHABORT ON
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
