Why would SET ARITHABORT ON dramatically speed up a query?

Question

The query is a single select containing a lot of grouping levels and aggragate operations. With SET ARITHABORT ON is takes less than a second, otherwise it takes several minutes. We have seen this behavior on SQL Server 2000 and 2008.

Answer 1

.NET applications connect with the option disabled by default, but it's enabled by default in Management Studio. The result is that the server actually caches 2 separate execution plans for most/all procedures. This affects how the server performs numerical calculations and as such you can get wildly different results depending on the procedure. This is really only one of 2 common ways a proc can get fed a terrible execution plan, the other being parameter sniffing.

Take a look at http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx for a little more discussion on it.

Answer 2

I would argue that this was almost certainly parameter sniffing.

It is often stated that SET OPTIONS can affect performance in this way but I have yet to see a single authoritative source for this claim except for the case where you are using indexed Views / persisted computed columns.

In this case (for SQL2005+ and unless your database is in SQL2000 compatibility mode). If you have both ARITHABORT and ANSI_WARNINGS OFF then you will find the index not being used so may have a scan rather than the desired seek (and some overhead as the persisted calculation result can not be used). ADO.NET seems to default to having ANSI_WARNINGS ON from a quick test I just did.

The claim in Ben's answer that "the way the server performs numerical calculations" can add minutes to a result that would otherwise take less than a second just doesn't seem credible to me. I think what tends to happen is that upon investigating a performance performance problem Profiler is used to identify the offending query. This is pasted into management studio and run and returns results instantly. The only apparent difference between connections is the ARITH_ABORT option.

A quick test in a management studio window shows that when SET ARITHABORT OFF is turned on and the query is run that the performance problem recurs so that is apparently case closed. Indeed this seems to be the troubleshooting methodology used in the Gregg Stark link.

However that ignores the fact that with that option set you will end up getting the exact same bad plan from the cache. Even if you are logged in as a different user than the application connection uses.

I tested this by executing a test query first from a web application then from management studio with SET ARITHABORT OFF and could see the usecounts going up from the below query.

SELECT usecounts, cacheobjtype, objtype, text ,query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle)