Welcome to SqlAdvice Sign in | Join | Help

Arithabort Option Affects Stored Procedure Performance

I have noticed that at times I will have a stored procedure start to take an unusual amount of time to complete.  In trying to debug this I grab the SQL that is being called from the application and paste it into SQL Server Management Studio only to have it come back in a second.  So I try again from the application, and it takes well over a minute.  After hunting around and pulling out my hair as to why this would be I discovered that the connection from the application has the Arithabort option off and in SQL Server Management Studio, by default the Arithabort option is on.  So I went ahead and ran a set Arithabort off and then ran the stored procedure within SQL Server Management Studio and sure enough it ran forever.  From what I can tell having this option off was causing the query optimizer to pick some horrible query plan.  I know SQL Server caches query plans by the connection options, but this just doesn't make much sense to me.  The best fix I have come up with for this is to compile the proc with the "with recompile" option.  I don't really like this option as SQL Server won't cache the query plan, but I haven't found any other solutions that work consistently.  You can do this as such.

Create Procedure MyStoredProcedure
  @MyParameter varchar(10)
with Recompile
as
   select 1

I am interested in what others have found for a solution to this problem or any other insight into this as it is quite annoying and if you haven't run into it before it can be quite time consuming and annoying.

Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Sponsor
Published Tuesday, February 12, 2008 4:34 PM by gstark

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

Tuesday, February 12, 2008 11:53 PM by ssmith

# re: Arithabort Option Affects Stored Procedure Performance

If you use the compile option, you no longer need to SET ARITHABORT ON/OFF in the sproc?

Wednesday, February 13, 2008 8:43 AM by gstark

# re: Arithabort Option Affects Stored Procedure Performance

Yes, it should be safe to remove that.  

Friday, February 15, 2008 4:36 PM by Steven Smith

# Stored Procedure Performance Varies Between ADO.NET and Management Studio

I ran into this very annoying issue earlier this week, that my buddy and SQL guru Gregg Stark was able

Friday, February 15, 2008 5:16 PM by Teemu

# re: Arithabort Option Affects Stored Procedure Performance

If you have ARITHABORT on, only then can indexed views and indexes on computed columns be used by the optimizer?

Friday, March 28, 2008 4:42 PM by jessica

# re: Arithabort Option Affects Stored Procedure Performance

does having the ARITHABORT on in the database level affect performance in any way?

Monday, March 31, 2008 9:18 AM by ved

# re: Arithabort Option Affects Stored Procedure Performance

ved triwari

Monday, April 21, 2008 4:57 PM by RayG

# re: Arithabort Option Affects Stored Procedure Performance

Apparently ADO runs with ARITHBORT OFF by default.  Alternatives appear to be:

1- Execute SPs as Text from ASP and preface teh SP statement with SET ARITHABORT ON.  Problem with that is not being able to use the command Object of type SP.

2- SET ARITHABORT ON after teh connection is established by adding this command to the appropriate connection event.  Problem with this is an extra round trip to the server for every connection (huge in an ASP app).

3- SET SQL Server default connection to SET Arithabort on at the server level.  Problem here is that I don't know the full repercussions of doing this at the server level but my feeling is that things will overall be better withthe option set this way.  We have this change in our staging environment for a while and will see.

Leave a Comment

(required) 
required 
(required) 
Enter the code you see below