4

I am using ASP.NET with MVC3 with a SQL server database. From time to time, one of our stored procedures become incredibly slow when called by the web platform, but stay as fast when called directly in SSMS.

The solution to this problem is to write "SET ARITHABORT ON" at the beginning of the stored procedure, which magically works.

However, I want this to stop since I don't what is causing procedures to randomly start being incredibly slow on the web and causing timeouts.

Is there a way to resolve the problem definitely?

Thanks in advance, Eric

Improve this question
2
  • 1
    Are you using Entity Framework? And if so, do you make use of Lazy Loading? If so, you need to have "Multiple Active Result Sets = true" in your connection string. This is a common issue when using EF. If you aren't using Lazy Loading, then you can probably get rid of the "MARS = true" in the connection string if it is currently there.
    – Solomon Rutzky
    Commented Feb 3, 2015 at 18:44
  • 1
    If I had a nickel for every time this question was asked - sommarskog.se/query-plan-mysteries.html
    – Aaron Bertrand
    Commented Feb 3, 2015 at 20:03

1 Answer 1

Reset to default
2

Changing ARITHABORT inside the procedure causes it to recompile:

http://support.microsoft.com/kb/243586

So most likely you're dealing with parameter sniffing. If you don't figure other ways to fix this, adding "... option (recompile)" into the end of the statement you're having trouble should solve the issue. That of course increases CPU usage (slightly) because it has to do a new plan for that statement every time.

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.