Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 28 '11 at 12:26

This question came from our site for professional and enthusiast programmers.

4 Answers 4

up vote 20 down vote accepted

A little dated, but for anyone ending up here with a similar problem...

I had the same problem. For me it turned out to be parameter sniffing, which at first I didn't understand enough to care about. I added a 'set arithabort on' which fixed the problem but then it came back. Then I read:

http://www.sommarskog.se/query-plan-mysteries.html

It cleared -everything- up. Because I was using Linq to SQL and had limited options to fix the issue, I ended up using a query plan guide (see end of link) to force the query plan I wanted.

share|improve this answer

.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.

share|improve this answer
    
I agree with half of this answer. Am very sceptical about the numerical calculation claim though! –  Martin Smith Jul 18 '10 at 9:31
1  
@Martin: I think I was unclear. I was just saying the ARITHABORT ON makes SQL Server will error out on any div/0 or arithmetic overflow error. When it is off it keeps going and for whatever reason can cause all kinds of horrible problems. –  RandomBen Jul 19 '10 at 13:27
    
@Ben - Yes sorry I didn't want to particularly attack your answer I was just pointing out that it would be very easy to change a SET option get a better plan and misdiagnose this as being the Option itself that is at fault. I'm not convinced the guy in your link hasn't done this. –  Martin Smith Jul 19 '10 at 13:37
    
@Martin - Not a problem, I didn't think you were attacking me. The other discussion I linked could be a little unclear. I was just trying to give supporting evidence. –  RandomBen Jul 19 '10 at 14:08
    
@Martin In retrospect I believe that you are correct. –  Jonathan Allen Jul 27 '11 at 17:06

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 can end up getting the exact same bad plan from the cache.

This plan reuse can happen 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) 

In order for this sharing pf plans to actually occur all plan cache keys must be the same. As well as arithabort itself some other examples are the executing users need the same default schema (if the query relies on implicit name resolution) and the connections need the same language set.

A fuller list of plan cache keys here

share|improve this answer
    
I think you may be right that plan caching may obfuscate the exact contribution of this option, but I recently ran across almost exactly this same problem (i.e. a query [stored procedure] that runs orders-of-magnitude faster in SSMS versus .NET SqlClient Data provider) and I did notice that subsequent executions were faster (which supports your plan caching hypothesis), but still significantly slower than executing the stored procedure (with the same parameter values) via SSMS. Has anyone systematically tested the performance of these options? –  Kenny Evitt Sep 14 '10 at 19:09

I know I'm late to this party, but for future visitors, Martin is exactly correct. We ran into this same issue--an SP was running very slowly for .NET clients, while it was blazing fast for SSMS. In exploring and resolving the issue, we did the systematic testing that Kenny Evitt asks about in his comment to Martin's question.

Using a variant of Martin's query, I looked for the SP in the procedure cache and found two of them. Looking at the plans, it was in fact the case that one had ARITHABORT ON and one had ARITHABORT OFF. The ARITHABORT OFF version had an index seek while the ARITHABORT ON version used an index scan for that same output. Given the parameters involved, the index seek would have required a lookup on tens of millions of records for the output.

I cleared the two procedures from the cache and had the .NET client run the SP again, using the same parameters (which featured a wide date range for a customer with lots of activity). The SP returned instantly. The cached plan used the same index scan that was previously featured in the ARITHABORT ON plan--but this time the plan was for ARITHABORT OFF. We ran the SP with the same parameters in SSMS, and again got results instantly. Now we saw that a second plan was cached, for ARITHABORT ON, with the index scan.

We then cleared the cache, ran the SP in SSMS with a narrow date range and got an instant result. We found that the resulting cached plan had an index seek, for the same output was previously handled with a scan (which was also a seek in the original plan with ARITHABORT OFF). Again from SSMS, we ran the SP, this time with the same wide date range, and saw the same terrible performance we had in the original .NET request.

In short, the disparity had nothing to do with the actual value of ARITHABORT--with it on or off, from either client, we could get acceptable or terrible performance: All that mattered was the parameter values used in compiling and caching the plan.

While MSDN indicates that ARITHABORT OFF itself can have a negative impact on query optimization, our testing confirms that Martin is correct--the cause was parameter sniffing and the resulting plan not being optimal for all ranges of parameters.

share|improve this answer
1  
Wonder what that phrase Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues. means. Whether they are just talking about inability to use indexes on computed columns and views (if ANSI_WARNINGS is also off) or if it does indeed have some other effect. –  Martin Smith Jul 9 '13 at 7:22
    
I'm not sure. I wonder if it's simply the case that someone at MSDN ran into a similar situation, set ARTIHABORT to ON, saw the performance improvement, and jumped to same conclusions others have. As far as indexed views and computed columns, I'm unclear. At one point it states that the SET options must have specific values if an INSERT, UPDATE, or DELETE operation modifies data values stored therein. Elsewhere they state that the optimizer will ignore indexes for "any query" that references said indexed view or computed column. Are both true, or is it really "any query modifying data"? –  mdoyle Jul 9 '13 at 19:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.