Stored procedure slow when called from web, fast from Management Studio

asp.net sql-server-2008 stored-procedures
iamserious picture iamserious · Jul 5, 2011 · Viewed 69.1k times · Source

I have stored procedure that insanely times out every single time it's called from the web application.

I fired up the Sql Profiler and traced the calls that time out and finally found out these things:

  1. When executed the statements from within the MS SQL Management Studio, with same arguments (in fact, I copied the procedure call from sql profile trace and ran it): It finishes in 5~6 seconds avg.
  2. But when called from web application, it takes in excess of 30 seconds (in trace) so my webpage actually times out by then.

Apart from the fact that my web application has its own user, every thing is same (same database, connection, server etc) I also tried running the query directly in the studio with the web application's user and it doesn't take more than 6 sec.

How do I find out what is happening?

I am assuming it has nothing to do with the fact that we use BLL > DAL layers or Table adapters as the trace clearly shows the delay is in the actual procedure. That is all I can think of.

EDIT I found out in this link that ADO.NET sets ARITHABORT to true - which is good for most of the time but sometime this happens, and the suggested work-around is to add with recompile option to the stored proc. In my case, it's not working but I suspect it's something very similar to this. Anyone knows what else ADO.NET does or where I can find the spec?

Answer

StriplingWarrior picture StriplingWarrior · Jul 5, 2011

I've had a similar issue arise in the past, so I'm eager to see a resolution to this question. Aaron Bertrand's comment on the OP led to Query times out when executed from web, but super-fast when executed from SSMS, and while the question is not a duplicate, the answer may very well apply to your situation.

In essence, it sounds like SQL Server may have a corrupt cached execution plan. You're hitting the bad plan with your web server, but SSMS lands on a different plan since there is a different setting on the ARITHABORT flag (which would otherwise have no impact on your particular query/stored proc).

See ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException for another example, with a more complete explanation and resolution.

Related questions

The EXECUTE permission is denied on the user-defined table types?

I have a question about User-Defined Table Types in SQL Server 2008. For the need of one of the ASP.NET application we defined our own table-types on SQL Server 2008 to use them as parameters in the stored procedures (when executing …

Read More
Read data from SqlDataReader

I have a SQL Server 2008 database and I am working on it in the backend. I am working on asp.net/C# SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { //how do I read strings here???? } I know that the reader …

Read More
Format of the initialization string does not conform to specification starting at index 0

I have an ASP.NET application which runs fine on my local development machine. When I run this application online, it shows the following error: Format of the initialization string does not conform to specification starting at index 0 Why is …

Read More