Post reply

SET ARITHABORT ON;

  • October 31, 2012 at 7:16 am

    #100134

    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

  • October 31, 2012 at 7:33 am

    #1554589

    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[/url]

  • October 31, 2012 at 7:39 am

    #1554592

    Does this answer your question?

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

    Oh, sorry. Just saw you already read that.

  • October 31, 2012 at 8:29 am

    #1554634

    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!

  • October 31, 2012 at 9:11 am

    #1554651

    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[/url]

  • October 31, 2012 at 10:17 am

    #1554690

    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 🙂

    Thanks again for you help Dave!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply