Tuesday, February 12, 2008

Arithabort Option Affects Stored Procedure Performance

by gstark via Gregg Stark on SQL Server on 2/12/2008 9:34:50 PM

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.