SET ARITHABORT ON;

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » SET ARITHABORT ON;

SET ARITHABORT ON;

Rate Topic

Display Mode

Topic Options

Author

Message

MartJ

Posted Wednesday, October 31, 2012 7:16 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:20 PM
Points: 19, Visits: 95

Hi All

The SET ARITHABORT ON; is on as default for SSMS but not for all other methods of connection ie if connecting through ADO .net then the default is off. For this reason a different query plan can be obtained when executing a procedure through a .net connection rather than directly in SSMS...or at least that is what I'm led to believe.

So my question: Is the above statement true and if it is why is it true and why would it cause a faster execution in SSMS than .net?

All I can ascertain is setting it on prevents calculations that could include devide by zero and include rubbish answers....does it just spend ages calculating something that doesn't has no real answer? Does it perhaps mean that nulls or zero's are being passed into calculations that shouldn't be?

So far I've looked:
http: //sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx
http://dba.stackexchange.com/questions/9840/why-would-set-arithabort-on-dramatically-speed-up-a-query
http://msdn.microsoft.com/en-us/library/ms190306.aspx

and finally: http://www.sommarskog.se/query-plan-mysteries.html Now this seems good, but I still can't find out why it would speed things up so any help in explaining, really, what it does and how it works and how the exection plan is altered would be absolutely fantastic!

Thanks in advance. BigGrin

Post #1379294

Dave Ballantyne

Posted Wednesday, October 31, 2012 7:33 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

It is certainly true that two plans will be built ?
Are the plans built using exactly the same input parameters ?
If not that could explain the difference, plan a gets built using the value of 'X' as a predicate where plan b uses 'Y'.
That can make a big difference.

Im not aware that artihabort on itself will make the optimizer make a different choice in producing the plan.

Are you able to provide the plans for both queries ?

If you chop and change the arithabort setting in SSMS does the query 'magically' go faster and slower ?
What if you clear the statement cache before each execution ?

Clear Sky SQL
My Blog
Kent user group

Post #1379301

F. van Ruyven

Posted Wednesday, October 31, 2012 7:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 3:54 AM
Points: 277, Visits: 886

Does this answer your question?

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

Oh, sorry. Just saw you already read that.

Post #1379304

MartJ

Posted Wednesday, October 31, 2012 8:29 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:20 PM
Points: 19, Visits: 95

Thanks Dave,

As you mention the different paramaters would cause a difference, that makes sense. The query executed in SSMS was about 10 seconds faster than through the .net programme. As the previous post mentions, the SET ARITHABORT ON; setting is the difference between the default running through .net and SSMS. - It does seem to be an interesting item.

If I'm honest the whole query is massivly complicated (IF...IF...ELSE etc, UNIONS galore and subqueries everywhere....plus the odd function thrown in here and there) so I'm breaking it down so each of the key parts can have a proper plan and variables won't be included in the results plans. I think this will resolve the slow running anyway, along wtih some analysis of stats and indexes, but it doesn't really answer my ever deepening need for an understanding of why SET ARITHABORT ON; has the potential to increase query performance.

I've already made quite a few adjustments to the indexes and stats and the query is now working much better with SET ARITHABORT ON; or SET ARITHABORT OFF; but this still leaves the question open on why it would be faster wtih it set as on???

Thanks again!

Post #1379354

Dave Ballantyne

Posted Wednesday, October 31, 2012 9:11 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

Is there a large result set ?

SSMS can be slow to consume the results, this could cause you to be interpretting the metrics wrong.
Turn on 'Discard Results' in options and reconfirm the timings.

If you put arithabort on in the proc itself , do the times correlate or is the still a difference ?

Dave

Clear Sky SQL
My Blog
Kent user group

Post #1379374

MartJ

Posted Wednesday, October 31, 2012 10:17 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:20 PM
Points: 19, Visits: 95

The results set is only 16 records with 12 fields but it's all aggregated data, which is the only reason I can see the ARITHABORT setting causing an issue either way. When run with it off it's slower than with it on and the report in question 'home made' in ASP.net

When re-reading http://www.sommarskog.se/query-plan-mysteries.html it mentions that the ARITHABORT could cause paramater sniffing issues:

In practice, the most important SET option is ARITHABORT, because the default for this option is different in an application and in SQL Server Management Studio. This explains why you can spot a slow query in your application, and then run it at good speed in SSMS. The application uses a plan which was compiled for a different set of sniffed parameter values than the actual values, whereas when you run the query in SSMS, it is likely that there is no plan for ARITHABORT ON in the cache, so SQL Server will build a plan that fits with your current parameter values.

You have also understood that you can verify that this is the case by running this command in your query window:

SET ARITHABORT OFF
and with great likelihood, you will now get the slow behaviour of the application also in SSMS. If this happens, you know that you have a performance problem related to parameter sniffing.

So, in theory, my initial thought to split out the query so each IF has it's own proc (as mentioned by Grant at SQL in the city London Wink

)may well resolve the problem as each part will have it's own plan..... but I'm still in a quest for knowledge about this one :)

Thanks again for you help Dave!

Post #1379419

« Prev Topic | Next Topic »

Permissions