SQL Server is a powerful database management system that offers various settings and configurations to optimize query performance. One such setting is ARITHABORT, which determines how SQL Server handles arithmetic errors during query execution.

Many people have written about the importance of setting ARITHABORT to ON in your connection settings. It is widely known that having ARITHABORT OFF can negatively impact query optimization and lead to performance issues. In fact, Microsoft’s own documentation stresses the importance of always setting ARITHABORT to ON.

So, what exactly is ARITHABORT and why is it important? ARITHABORT is a session-level setting that determines whether a query should be terminated when an overflow or divide-by-zero error occurs. When ARITHABORT is set to ON, SQL Server will terminate the query and return an error message. On the other hand, when ARITHABORT is set to OFF, SQL Server will return a NULL value instead of terminating the query.

Why is ARITHABORT OFF by default? Well, like many other settings in SQL Server, it has always been this way and Microsoft has chosen not to change it. However, it is recommended to change this setting to ON, especially when working with .NET applications.

Changing the ARITHABORT setting globally for all .NET connections can be a cumbersome task, especially if you have multiple applications or third-party applications where you don’t have control over the code. Fortunately, there is an easier way to change this setting.

To check the current value of ARITHABORT, you can run the following script:

EXEC sp_configure 'user_options';

This will display the current configuration values for the set options. If the config_value for ARITHABORT is 0, it means that none of the SET options are altered and .NET connections will use their own defaults.

To turn on the ARITHABORT setting at the server level, you can run the following script:

DECLARE @Value INT;
DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, [config_value] int, [run_value] int);

INSERT INTO @options ([name], [minimum], [maximum], [config_value], [run_value])
EXEC sp_configure 'user_options';

SELECT @Value = [config_value] | 64
FROM @options;

EXEC sp_configure 'user_options', @Value;
RECONFIGURE;

This script will enable ARITHABORT at the instance level, and all .NET clients will automatically start connecting with ARITHABORT ON. It is important to note that changing this setting globally can affect other types of connections, so it is recommended to test and monitor the changes on a non-production server.

If you need to change ARITHABORT back to OFF, you can use the following script:

DECLARE @Value INT;
DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, [config_value] int, [run_value] int);

INSERT INTO @options ([name], [minimum], [maximum], [config_value], [run_value])
EXEC sp_configure 'user_options';

SELECT @Value = [config_value] & ~64
FROM @options;

EXEC sp_configure 'user_options', @Value;
RECONFIGURE;

It is important to note that this script may not affect other types of connections, so it is recommended to test the changes with different types of connections.

By understanding and properly configuring the ARITHABORT setting in SQL Server, you can ensure better query performance and avoid potential issues caused by arithmetic errors. Remember to always set ARITHABORT to ON in your logon sessions to optimize your SQL Server environment.