June 28, 2011 at 4:04 am
I'm in the middle of investigating an issue whereby .NET applications are taking over 30 seconds to run a stored procedure, but SSMS can complete it in under a second. I still have a lot to do on this (investigating indexes, settings, execution plans, etc.), however maybe someone can point me in the right direction as to why the following are happening.
I know that SSMS is setting ARITHABORT ON when I create a new query, and changing the setting between ON and OFF before running the procedure manually is reproducing the issues seen in the .NET application (all .NET applications rely on the default database setting of OFF).
I am on SQL Server 2008 SP2 (not RC2) and all databases have Arithmetic Abort set to FALSE by default (even new databases - just tested this). However some documentation and forums I've been reading seems to indicate that this should be set to ON. Which is correct, and (with the exception of queries aborting if the hit a div by zero error) what would be the impact/harm of switching the problem databases so that arithmetic abort was on by default instead? We don't use indexed views and only have a small handful of computed columns.
The issues are being mainly reported by remote offices, however they run the app via Terminal Services so essentially the app is being run on the local network - so I don't believe that it is the connection speed that is the issue here, especially as i can reproduce the problem locally.
One thing I have seen which has confused me, however, is that when I turn the io and time statistics on for the query, I get radically different results as indicated below. both of these were run from SSMS with the same connection, the only different was the SET ARITHABORT ON or OFF before running the procedure.
SET ARITHABORT ON
Takes 23 seconds to return 2 recordsets and 4 records
Procedure 1
Table 1 - scans 1, logical reads, 27
Table 2- scans 0, logical reads 3
Procedure 2
Table 1 - scans 5, logical reads 63248, physical reads 1568, read-aheads 61613
Table 2 - scans 1, logical reads 3
Worktable - scans 0, logical reads 0
SET ARITHABORT OFF
Takes 1minute 36 seconds to return 2 recordsets and 4 records
Procedure 1
Table 1 - scans 5, logical reads 169301, physical reads 4105, read-aheads 167766
Table 2 - scans 0, logical reads 3
Worktable - scans 0, logical reads 0
Procedure 2
Table 1 - scans 1, logical reads 584157, physical reads 52, read-aheads 15675
Table 2 - scans 1, logical reads 3
Worktable - scans 0, logical reads 0
I have run each one several times to ensure that the results are taking cached data into account.
I'm aware that this could be the results of two execution plans, one for each setting, and am investigating this at the moment, but a second opinion is always helpful.
I'm checking the procedures at the moment to see if the queries can be imporoved, indexes added, etc., but any advice on the arithabort settings, or anything I can check, would be useful as the .NET apps are timing out. I can't even get the execution plans up on the production server to see what they are as every time I try, all other applications start to time out! Is there any way I cen see what the stored execution plans for a procedure are? I've tried looking at sys.dm_exec_cached_plans but can't figure out what belongs to what.
Thanks in advance.
Colin
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
June 30, 2011 at 4:15 am
ARITHABORT should be set ON in all modern development work. Many new features require that setting, for example XML methods, indexed views (as you mentioned), and indexed computed columns. Some queries will fail if connection settings are incorrect, or you may see reduced performance for example if an index on a computed column cannot be used, and the computed column values must be re-evaluated at runtime.
The database and server default settings for things like ARITHABORT only come into play if the client does not specify a setting. They default to OFF only for backward-compatibility reasons. Almost all client libraries set things like ARITHABORT when they connect. See http://msdn.microsoft.com/en-us/library/ms175088.aspx for full details.
You say: "all .NET applications rely on the default database setting of OFF". Can you expand on what you mean by this, because that runs counter to my experience.
There are any number of ways to obtain query plans - both before and after execution. Using sys.dm_exec_cached_plans will show the before-execution cached plan. You can use sys.dm_exec_procedure_stats to obtain a plan_handle for that view. You might also use Profiler.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 4:46 am
Many thanks.
This has been one helluva learning curve for me over the past few days digging into all of this and I'm finally getting to a stage where I can see things clearly.
What I had missed in the profier was that the settings were captured in the connection, I had presumed they were adopting the setting at database level, which was clearly wrong.
I'm now able, with the help of the dm views to get to the cached plans and can now see what is happening, where the issues might lie, and what to do about them. I can now clearly see that settings are being set by the client, and as far as I can make out, wrongly (ARITHABORT to OFF in this case as the client application is not setting this itself).
I've pretty much determined that this setting is giving me grief from a performance point of view, and also that it is masking other issues such as the way procedures have been coded and are being called (my thanks go to another poster somewhere else on giving me a heads up on this) - so the extra effort trying to understand all of this is paying off as I can now start making recommendations to them as to what needs to be done (getting them to follow it is going to be the fun part :hehe:).
I still have quite a way to go with this, but it is proving to be a fascinating experience, if not infuriating at times.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
June 30, 2011 at 5:01 am
No worries. If you feel like posting a plan at some stage, feel free - I always enjoy looking at those.
By the way, 4GB RAM is really pretty tight - no doubt money is always an issue, but adding memory will help reduce all those read-ahead reads and stuff.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 8:16 am
Okay, one last question regarding the settings (ARITHABORT and the likes).
I have now checked the connections between the applications and the databases and can't quite believe what I am seeing.
I would expect older technologies like ODBC to set ARITHABORT to False, but the connections coming from the newly developed .NET apps, ColdFusion 9 apps, ASP.NET and even SQL Reporting Services are setting it to False as soon as they connect.
The in-house developed applications aren't going to be too much of a problem as we can change the code and make the setting change ourselves. ColdFusion it appears we have to send the command ourselves before every bespoke query, though we can put it inside the stored procedures ourselves.
But I'm surprised to see SSRS making the same setting. Am i missing something here, or if we need to override a setting do we have to make it every time a connection is made?
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
June 30, 2011 at 8:25 am
That's covered in the link I mentioned earlier. There's a special note:
Some of the SQL Server utilities set one or more of the ANSI settings to OFF to maintain compatibility with earlier versions of the utilities.
It also says:
Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher
The OLEDB and ODBC drivers usually set ANSI_WARNINGS ON...is that what you are seeing?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 8:53 am
Hmm. Just checked.
When a .NET application connects, this is what is sent through with the connection.
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level serializable
SSMS is sending
set quoted_identifier on
set arithabort on
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
and SSRS is sending
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
The ARITHABORT setting differs here, the ANSI WARNINGS don't. So is the ARITHABORT a red herring here and the ANSI setting overriding it (all databases are at level 100, we have nothing legacy fortunately)? If this is the case then I can emilinate it from my list and leave the developers in peace.
I can see that I have two execution plans for the same procedure, one runs like a dog and I can see this is the result of a clustered index scan (the procedure code needs a rework anyway). The other plan handles it a different way using an index lookup and is far better at what it does (this appears to have been created in our initial investigations running it through SSMS judging by the creation date and execution counts). I can invoke the bad plan by setting the arithabort off in SSMS and thereby simulate the performance issue that the .NET app is getting (30+ seconds to run the procedure as opposed to 1 second maximum). Switching the setting invokes the good plan. I'm at a complete loss as to why it decided to use a clustered index scan in the first plan, but I don't know what parameters it was being passed when the plan compiled.
This may just finally be a case of bad code, bad parameters and a bad plan.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
June 30, 2011 at 8:59 am
Actually, I've just answered this myself.
If I set ANSI WARNINGS ON, no matter what I set ARITHABORT to, a divide by zero error
Set it OFF and ARITHABORT affects the outcome as to whether it just displays a message and continues or errors.
Just annoying now that the mere presence of the command is bu8ilding two execution plans depending on its setting - makes tracing issues just that bit more confusing. :hehe::crazy:
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
June 30, 2011 at 10:45 am
Yes different plans have to be cached for different SET options (see is_cache_key in sys.dm_exec_plan_attributes) because they can produce different results, as you've seen 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 1, 2011 at 1:36 am
Excellent, thanks!
That was the final part of the puzzle. I am definitely dealing with a bad execution plan here. At least I can now concentrate now on finding out why the plan went awry.
Many many thanks for your help, I owe you one.
Colin
:w00t::w00t:
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
February 28, 2012 at 8:39 am
I had exactly the same problem while executing SSRS report which was setting ARITHABORT to OFF and my stored procedure was slow.
When I tried to SET ARITHABORT ON, stored procedure was fast.
As I realized that I have to do something with two different execution plans, I tried to old known parameter sniffing workaround and that helped me...
It is about not to use input parameters in the body of the stored procedure, except that the equivalent ones:
CREATE PROCEDURE dbo.sp_name
(
@p1 INT,
@p2 INT,
@p3 ...
...
)
AS
DECLARE
@_p1 INT = @p1,
@_p2 INT = @p2,
@_p3 ...
...
and further only using @_p variables
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply