SQL Server stored procedures fast in SQL but slow when called by ASP.NET

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

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.

StackExchange Database Administrators Q#90970, answer score: 2