Readers of this blog post are aware of SQL Server Expert Tim Cartwright, who has previously written an enhanced version of How to Generate Random Password script. Here is another very interesting find by Tim on the subject of ARITHABORT ON setting. It is one of the most interesting blog post I have read in a while.
Many people have blogged in the past how having ARITHABORT OFF in your connection settings can adversely affect your query performance and produce bad plans. Erland Sommarskog has a very well written article: Slow in the Application, Fast in SSMS?. My co-worker Daniel Berber was mentioned in this article alerting Erland to the face that ARITHABORT should always be on. Tara Kizer talks about it in this article: Troubleshooting Parameter Sniffing Issues the Right Way: Part 2.
Even Microsoft’s own documentation stresses this should always be ON. Quote from MSDN:
You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.
The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.
We could try to edit all of our applications and work with our developers to turn ARTIHABORT on every time they connect but that would be incredibly troublesome. It would also be pretty much impossible for any 3rd party applications where we do not have control over the code. Fortunately for us, there is an easier way.
WARNING: Before continuing, be very aware that you are changing a server-wide setting that affects all of the .Net connections to that server. Like any global setting you should test and monitor to verify that nothing breaks preferably on a non-production server. I don’t think there is a chance that this will happen, but I still need to warn you. Also, be very careful about setting the other settings globally as you can cause some serious problems with queries. Especially with a setting like NUMERIC_ROUNDABORT.
Here is the script which checks the settings of various set options.
/* Author: Tim Cartwright Purpose: Allows you to check the server, and client SET options https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option?view=sql-server-2017 1 DISABLE_DEF_CNST_CHK Controls interim or deferred constraint checking. 2 IMPLICIT_TRANSACTIONS For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections. 4 CURSOR_CLOSE_ON_COMMIT Controls behavior of cursors after a commit operation has been performed. 8 ANSI_WARNINGS Controls truncation and NULL in aggregate warnings. 16 ANSI_PADDING Controls padding of fixed-length variables. 32 ANSI_NULLS Controls NULL handling when using equality operators. 64 ARITHABORT Terminates a query when an overflow or divide-by-zero error occurs during query execution. 128 ARITHIGNORE Returns NULL when an overflow or divide-by-zero error occurs during a query. 256 QUOTED_IDENTIFIER Differentiates between single and double quotation marks when evaluating an expression. 512 NOCOUNT Turns off the message returned at the end of each statement that states how many rows were affected. 1024 ANSI_NULL_DFLT_ON Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls. 2048 ANSI_NULL_DFLT_OFF Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls. 4096 CONCAT_NULL_YIELDS_NULL Returns NULL when concatenating a NULL value with a string. 8192 NUMERIC_ROUNDABORT Generates an error when a loss of precision occurs in an expression. 16384 XACT_ABORT Rolls back a transaction if a Transact-SQL statement raises a run-time error. */ DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, [config_value] int, [run_value] int) DECLARE @optionsCheck TABLE([id] int NOT NULL IDENTITY, [setting_name] varchar(128)) DECLARE @current_value INT; INSERT INTO @options ([name], [minimum], [maximum], [config_value], [run_value]) EXEC sp_configure 'user_options'; SELECT @current_value = [config_value] FROM @options; --SELECT name, minimum, maximum, config_value, run_value FROM @options --SELECT @current_value INSERT INTO @optionsCheck ([setting_name]) VALUES ('DISABLE_DEF_CNST_CHK'), ('IMPLICIT_TRANSACTIONS'), ('CURSOR_CLOSE_ON_COMMIT'), ('ANSI_WARNINGS'), ('ANSI_PADDING'), ('ANSI_NULLS'), ('ARITHABORT'), ('ARITHIGNORE'), ('QUOTED_IDENTIFIER'), ('NOCOUNT'), ('ANSI_NULL_DFLT_ON'), ('ANSI_NULL_DFLT_OFF'), ('CONCAT_NULL_YIELDS_NULL'), ('NUMERIC_ROUNDABORT'), ('XACT_ABORT') SELECT fn.[value], oc.[setting_name], [server_option] = CASE WHEN (@current_value & fn.[value]) = fn.[value] THEN 'ON' ELSE '-' END, [client_option] = CASE WHEN (@@options & fn.[value]) = fn.[value] THEN 'ON' ELSE '-' END FROM @optionsCheck oc CROSS APPLY ( SELECT [value] = CASE WHEN oc.id > 1 THEN POWER(2, oc.id - 1) ELSE 1 END ) fn
Checking the current value of ARITHABORT
Most people believe that SSMS has ARITHABORT ON by default, and all connections made from .net always have set it to OFF. Well as it turns out that is not quite true. The .net SQL server driver connects to the SQL Server and queries what the server settings are and uses those for the connection session. How do I see what my current settings are? You can run EXEC sp_configure ‘user_options’; and you should see results like this:
The config_value and the run_value show the currently set values for the set options which are a bitmask. Bit masking is outside of the scope of this article, but suffice it to say that it allows SQL Server to store many true-false values with one integer value.
A config_value of 0 means that basically none of the SET options are altered, and .net connections will use their own defaults. We can verify that the ARTIHABORT setting is off on the server by running this script:
/* TEST FOR ARITHABORT ON */ 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 [setting] = 'ARITHABORT ' + CASE WHEN ([config_value] & 64) = 64 THEN 'ON' ELSE 'OFF' END FROM @options; GO
Before we turn it on, we can check to see what setting applications are using when connecting by running the below PowerShell script from Windows PowerShell ISE (Change the ServerInstance):
Clear-Host Invoke-Sqlcmd2 -ServerInstance "(local)" -Database "master" -Query "SELECT [Setting] = 'ARITHABORT ' + CASE WHEN (@@options & 64) = 64 THEN 'ON' ELSE 'OFF' END"
We should see this as a result:
Why is it OFF by default? Well, that is because like most other bad settings in SQL Server it has always been this way so Microsoft will not change it. Similar in nature to MAX DOP, COP, and other server settings that every good dba knows they need to change when standing up a new server.
Turning on ARITHABORT
Now that we have verified that the setting is OFF, and our .net connections are also using ARITHABORT OFF let’s turn it on at the server and try out PowerShell test again. To turn on the ARITHABORT setting run this script:
/* SET ARITHABORT ON */ -- NOTE: By enabling this at the instance level all .net clients will automatically start connecting with using SET ARITHABORT ON DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, [config_value] int, [run_value] int); DECLARE @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; SELECT * FROM @options; -- prior state EXEC sp_configure 'user_options'; -- current state GO
The top grid shows the old configured values, and the bottom the new values. So now we can run our PowerShell test again and we should get:
So by changing the setting at the server level, our PowerShell, and .net connections are also now connecting with the recommended value of ARITHABORT ON automatically and without having to edit the applications!
Turning on ARITHABORT
So, how do we turn ARITHABORT back to OFF? If you really need to change it back for whatever reason you can use this script:
/* SET ARITHABORT OFF */ DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, [config_value] int, [run_value] int); DECLARE @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; SELECT * FROM @options; -- prior state EXEC sp_configure 'user_options'; -- current state
NOTE: This has not been tested with all drivers so it may not affect other types of connections. I have tested .net connections, and PowerShell. I am curious to find out if other types of connections are positively affected.
Reference: Pinal Dave (https://blog.SQLAuthority.com)
I have seen one SP which was written by other developer where he set ARITHABORT to ON in his SP, Just like NOCOUNT ON; like below –
CREATE PROCEDURE [dbo].[TestSP]
SET NOCOUNT ON;
SET ARITHABORT ON; <– What is the benefit to add this line in SP?
— OTHER BUSINESS LOGIC
So what is the benefit of it? Is it kind of best practice or what?
There is no benefit, that doesn’t work. SQL would have already chosen how it is going to handle the execution of the sp. You need to set this instance-wide, as described above, but be aware that the app/protocol that is establishing the connection to SQL can potentially override this.
I seem ran into parameter sniffing issue and searched for a solution. I have a table-valued function which is a 3-layer nested function(Top function FUNC1 called FUNC2 in it, FUNC2 called FUNC3 in it). I use it to calculate the revenue share in certain period. The page (on a ASP.net 4.7.2 web application) that calls this function is always time out recently, if I set the StartDate and EndDate parameter older than this year. However I can run the same sql script in Visual Studio from the same server much faster.(~4 min -> 4 sec)
Some people suggested SET ARITHABORT ON. So I tried your script to set it instance-wide but it didn’t work. Furthermore, it caused queries from both asp.net app and Visual Studio timed out. Any suggestion?
Using SET ARITHABORT inside an sp DOES work.
I had an sp that was running faster than 1 second from SSMS, but when called from Ajax it was taking 10 seconds or more. The quick solution was to add the line SET ARITHABORT ON inside the sp.