Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SET ARITHABORT ON;


SET ARITHABORT ON;

Author
Message
MartJ
MartJ
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 231
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.:-D
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
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
F. van Ruyven
F. van Ruyven
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 1574
Does this answer your question?

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

Oh, sorry. Just saw you already read that.
MartJ
MartJ
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 231
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!
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
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
MartJ
MartJ
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 231
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 ;-) )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 Smile

Thanks again for you help Dave!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search