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

Question: Does ARITHABORT negatively affect SQL Server performance? Different ARITHABORT settings can lead an application and SSMS to use separate cached plans. Check the difference, but don’t assume OFF always makes a query slow.

I’ve encountered this during client performance investigations: a query crawls in the application, then runs quickly in SSMS. Before congratulating ourselves on a fix, we need to reproduce the application’s connection settings, parameters and execution context.

SSMS normally uses ARITHABORT ON. The setting is a plan-cache key, so a different value can mean a different cached plan. That difference is a clue. It isn’t proof that the OFF setting directly caused the bad plan, and turning it on doesn’t guarantee the best plan for every parameter.

Read the setting in the connection that runs the query

SELECT CASE WHEN (64 & @@OPTIONS) = 64 THEN 'ON' ELSE 'OFF' END AS ARITHABORT;
SET ARITHABORT ON;
SELECT CASE WHEN (64 & @@OPTIONS) = 64 THEN 'ON' ELSE 'OFF' END AS ARITHABORT;

The first query reports the current session’s explicit setting. The SET statement affects that connection; it doesn’t change every application connection. To investigate an application, record the setting from its actual connection and reproduce it in your separate test session.

ARITHABORT also concerns arithmetic errors. With ANSI_WARNINGS ON and database compatibility level 90 or higher, ARITHABORT is implicitly ON for arithmetic-error behavior even when its explicit session setting is OFF. Microsoft still documents ARITHABORT as a cache key, which explains why the plan comparison matters even when the calculation behaves the same way.

I discuss these application-versus-SSMS differences in my Comprehensive Database Performance Health Check. My next step is to compare the actual plans and parameter values. A session setting can explain why we’re looking at two plans; the plans explain where the work went.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.