I have a stored procedure, that when run from Sql Server Management Studio (SSMS) takes 1 second to run. When invoked from my web application via ADO.Net, it takes > 30 seconds (web application gets a SQL timeout). Running SQL profiler shows that the following commands are executed prior to the execution of the stored procedure:

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

Running the above commands, and then my stored procedure in SSMS, and I can reproduce what is happening in my web application. The stored procedure is taking 34 seconds, or just passed the default SQL timeout in ADO.Net. Not that 29 seconds would be acceptable to me.

 

I know that the default isolation level is “read committed”, so I was pretty sure that wasn’t the problem, so I decided to start at the top and switch each on to off and vice-versa. Luckily I didn’t have to go far, as setting arithabort to on, returned my query execution time to 1 second, and turning it off resulted in > 30 seconds as a repeatable result.

 

Digging around on Google gave me a couple of posts (Greg Stark, Steven Smith) where people were having the exact same problem (it was weird how similar the posts were worded to each other and mine). As I thought, SQL server creates two separate query plans based on the arithabort setting, and the plan for arithabort OFF obviously sucks.  

 

I came up with 3 options from the posts:

1. In ado.net set Arithabort to On
2. Add With Recompile to my stored procedure(goes between your stored procedure paramaters and the as keyword at the top)
3. Set Arithabort to On at the server level

Option #1 seems like a pain, and no one who commented in the posts seems to be taking this route. That leaves options 2 and 3. It sounds like Arithabort ON is better then Off, but I am a little hesitant to set it to ON at the database level.

Well, only a little hesitant, as I just set it to on, and nothing blew up, so I think I’ll go with this setting over the long weekend. With the limited use our system gets over the holiday, it will be a good test.

comments powered by Disqus