3

I am fully aware that SQL Queries from applications typically use SET ARITHBAORT OFF where as SSMS (by default) uses SET ARITHBAORT ON. I also believe that SET ARITHBAORT OFF is only there for legacy compatibility and really queries should be run with SET ARITHBAORT ON.

I've got a query that runs as part of a C# Console App batch file. The context is prepared (by default) with SET ARITHBAORT OFF and SET ANSI_WARNINGS ON. The first 92 calls execute fine and the 93rd always locks up (each call uses different parameters). I've been able to reproduce this in SSMS if I use SET ARITHBAORT OFF prior to calling the Stored Procedure with the parameters from the 93rd call.

So on to my question (sorry for the background info so far).... The Erland Sommarskog article states:

Next, when it comes to ARITHABORT, you should know that in SQL 2005 and later versions, this setting has zero impact as long as ANSI_WARNINGS is ON. Thus, there is no reason to turn it on for the sake of the matter.

However, I am using SQL Server 2014 and I find that:

SET ARITHBAORT ON
SET ANSI_WARNINGS ON
EXEC mySP   -- Runs efficiently

runs fine but

SET ARITHBAORT OFF
SET ANSI_WARNINGS ON
EXEC mySP   -- Runs indefinitely

runs indefinitely. So if SET ANSI_WARNINGS ON makes the ARITHBAORT option irrelevant, why do does my query lock up? Thanks.

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

Chris Walsh
  • 3,423
  • 2
  • 42
  • 62
  • I'm not sure I follow your line of reasoning since in your last example, you seem to be *also* setting `ANSI_WARNINGS` `OFF`, so what effects it has when it's on are irrelevant. – Damien_The_Unbeliever Jul 09 '15 at 09:22
  • Sorry, that was a typo. I'll correct it, I AM keeping `SET ANSI_WARNINGS ON` in both cases. – Chris Walsh Jul 09 '15 at 09:24
  • 1
    OK. I've just discovered another hidden variable that has affected my query. Although I am running SQL Server 2014, the database was in "SQL Server 2008 (100)" compatibility mode (as per our live database). When I switch it to "SQL Server 2014 (120)", both queries now run in the same order of time. So could the statement in the article also be conditional to the compatibility level AFTER SQL Server 2005? – Chris Walsh Jul 09 '15 at 09:27
  • @ChrisWalsh - Different cached plans. Parameter sniffing. If you were to slightly change the text of the query on the 93rd call and run it with `ARITHBAORT OFF` you will probably find it runs equally fast as a new plan is generated that is good for those parameters. – Martin Smith Jul 09 '15 at 09:35
  • @MartinSmith, I'm aware that different cached plans at at play here and that 'slightly changing the text' can cause the query to run fast (due to last plan being thrown out), however.. I've already tried that and ran the 93rd call on its own (skipping first 92) and it still ran indefinitely! Having set Compatibility Level to 120 both work now and the both still work when I set Compatibility Level to 100. Could changing the Compatibility Level 120 have had some irreversable effect on the databse structure? – Chris Walsh Jul 09 '15 at 09:42
  • I'm going to restore the database again and try these options through again. Cheers. – Chris Walsh Jul 09 '15 at 09:43
  • 1
    regarding the effects of changing compatibility level, SQL 2014 includes a new query optimiser - but this is only used when the compatibility level is 120 (https://msdn.microsoft.com/en-us/library/bb510680.aspx). At 110 or below, the old optimister is used. This might explain what you're seeing. – Ed Harper Jul 09 '15 at 10:55

2 Answers2

2

OK. So the quoted statement that I've pulled from the Sommarskog article is under the condition that the Database Level is 80 or higher.

I found this paragraph in the MSDN reference for ARITHABORT:

Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must be explicitly set to ON.

This explains:

  1. Why I got a difference when changing ARITHABORT even though ANSI_WARNINGS was set to ON. (Database Level was 80)
  2. Why changing the Database Level appeared to fix the problem (because I changed it to 120)
Chris Walsh
  • 3,423
  • 2
  • 42
  • 62
0

The article you posted is misleading. If ansi_warnings is ON and arithabort is off you as a human knows this makes no difference to your query. The sql server engine does not know whether or not this makes a difference and will automatically force getting a new execution plan and not using your cached plan. This means if you are having parameter sniffing issues and have a bad plan you will never get that bad plan and use it with the arithabort being on. This is what makes that setting a great test to find parameter sniffing. Use the optimize for unknown hint for parameter sniffing instead of changing your database compatibility level which may effect other things.

N. Rhoades
  • 1
  • 2
  • Thanks for your feedback. I'm going to need a bit of time to digest your points but I think you've got some very good points that I need to get my head around. Thanks. – Chris Walsh Jan 09 '16 at 00:53