Does ARITHABORT Setting Negatively Impact SQL Server Performance? – Interview Question of the Week #220

Question: Does ARITHABORT Setting Negatively Impact SQL Server Performance?

Answer: Yes.

Does ARITHABORT Setting Negatively Impact SQL Server Performance? - Interview Question of the Week #220 arithabort

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)

, , ,
Previous Post
How to Compile Queries and Not Execute Them? – Interview Question of the Week #219
Next Post
How to check the ANSI Compatibility of SQL Server Queries? – Interview Question of the Week #221

Related Posts

Leave a Reply

Menu