By iamserious


2011-07-05 15:46:50 8 Comments

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?

6 comments

@Clint 2016-09-06 13:51:58

Not to spam, but as a hopefully helpful solution for others, our system saw a high degree of timeouts.

I tried setting the stored procedure to be recompiled by using sp_recompile and this resolved the issue for the one SP.

Ultimately there were a larger number of SP's that were timing-out, many of which had never done so before, by using DBCC DROPCLEANBUFFERS and DBBC FREEPROCCACHE the incident rate of timeouts has plummeted significantly - there are still isolated occurrences, some where I suspect the plan regeneration is taking a while, and some where the SPs are genuinely under-performant and need re-evaluation.

@Steve 2016-10-24 13:01:37

Thanks for this. Marking the procedure for recompilation using the sp_recompile command worked for me when the DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE made no difference.

@TomPez 2016-08-12 07:38:27

like @Zane said it could be due to parameter sniffing. I experienced the same behaviour and I took a look at the execution plan of the procedure and all the statements of the sp in a row (copied all the statements form the procedure, declared the parameters as variables and asigned the same values for the variable as the parameters had). However the execution plan looked completely different. The sp execution took 3-4 seconds and the statements in a row with the exact same values was instantly returned.

executionplan

After some googling I found an interesting read about that behaviour: Slow in the Application, Fast in SSMS?

When compiling the procedure, SQL Server does not know that the value of @fromdate changes, but compiles the procedure under the assumption that @fromdate has the value NULL. Since all comparisons with NULL yield UNKNOWN, the query cannot return any rows at all, if @fromdate still has this value at run-time. If SQL Server would take the input value as the final truth, it could construct a plan with only a Constant Scan that does not access the table at all (run the query SELECT * FROM Orders WHERE OrderDate > NULL to see an example of this). But SQL Server must generate a plan which returns the correct result no matter what value @fromdate has at run-time. On the other hand, there is no obligation to build a plan which is the best for all values. Thus, since the assumption is that no rows will be returned, SQL Server settles for the Index Seek.

The problem was that I had parameters which could be left null and if they were passed as null the would be initialised with a default value.

create procedure dbo.procedure
    @dateTo datetime = null
begin
    if (@dateTo is null)
    begin
        select @dateTo  = GETUTCDATE()
    end

    select foo
    from dbo.table
    where createdDate < @dateTo
end

After I changed it to

create procedure dbo.procedure
    @dateTo datetime = null
begin
    declare @to datetime = coalesce(@dateTo, getutcdate())

    select foo
    from dbo.table
    where createdDate < @to
end 

it worked like a charm again.

@Guy Biber 2016-05-15 07:25:35

Simply recompiling the stored procedure (table function in my case) worked for me

@Zane 2012-12-11 15:29:01

I also experience that queries were running slowly from the web and fast in SSMS and I eventually found out that the problem was something called parameter sniffing.

The fix for me was to change all the parameters that are used in the sproc to local variables.

eg. change:

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
SELECT * FROM Table WHERE ID = @param1 

to:

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
DECLARE @param1a int
SET @param1a = @param1
SELECT * FROM Table WHERE ID = @param1a

Seems strange, but it fixed my problem.

@Lac Ho 2013-11-23 00:06:11

Wow, I had the same problem and did not believe this would work but it did.

@Lac Ho 2013-11-23 00:30:57

Zane, do you know if this will permanently fix the problem or it can come back? Thank you!

@Zane 2013-11-25 14:09:33

Since making this change I haven't had any problems with the speed of the stored procedure.

@HerrimanCoder 2015-06-26 02:47:18

Wow, it fixed my issue too! This HAS TO BE a bug in sql server. Why force sql writers to jump through this silly hoop when MSSQL could internally convert to local under the hood, for HUGE performance gains?

@lukebuehler 2015-09-16 21:46:34

Tried all the other tricks as well, but this worked.

@Albert 2015-11-10 19:35:09

Did it for me too. Thanks!

@Garland Pope 2016-03-22 17:37:39

Could it be that altering the proc causes a new execution plan to be created, so the solution is not really copying the input variable to a local variable but just forcing the generation of a new execution plan (as explained in the accepted solution)?

@Lukas 2016-05-06 21:26:17

I can't believe it! In my case it was two datetime fields. When passing in variables from Visual Studio, I made sure that the objects that were being passed in were DateTime objects, but it still ran slow. After doing the above trick, my data loading time from Visual Studio dropped from 2 minutes to 2 seconds.

@ole6ka 2016-05-25 18:06:42

I'm not believe, it's magic

@tfa 2016-06-01 19:42:28

Yes this solved it for me. Amazingly I always done this and forgot it on one procedure. Once changed to local variables it solved the issue. Thanks.

@Vaclav Elias 2016-09-01 12:43:34

This also worked for me on SQL Server 2012!!

@Steffen Mangold 2016-09-20 01:16:26

Here you can read why it worked: blogs.technet.microsoft.com/mdegre/2011/11/06/… its the same reason like in answere above.

@Vivek Raj 2017-06-22 08:47:55

@GarlandPope I Agree. In my case I added a Select 'Hello'; and altered the sp and it executed normally.

@Richard Bailey 2017-10-16 08:17:00

This did not work for me when making use of Azure SQL Server

@clweeks 2018-12-27 19:52:46

One more success story here! Web app seems to have stopped working overnight when OS updates were applied to server. Tried everything, both from systems and code side. I sort of ignored this solution for a while, trying everything else, but it seems so janky. When I was desperate enough, I buffered all the parameters and everything just started working. Thanks, @Zane !

@StriplingWarrior 2011-07-05 17:07:09

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.

@iamserious 2011-07-05 17:27:43

These are some interesting leads, will read more about them and bew back in a few.. thanks.

@iamserious 2011-07-06 08:16:38

Hi, Clearing the cache DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE did the trick! I am guessing the execution plan was somehow corrupt or not updated. I am dubious that it's a permanent fix, if it could get corrupt once, it could do it again. So I am still looking into the plausible causes. Since the web app is back to work again, I don't need to feel the pressure. Thanks much.

@Induster 2012-10-31 17:25:32

@iamserious - you nailed it thanks! After altering my stored procedure, I was having the timeout problem. Then I ran the two DBCC commands you mention above and it solved the issue.

@Brian MacKay 2013-10-31 18:45:18

Wow. That worked for me. Bizarre. Thank goodness it happened on a sproc that was actually very simple and quick from MSSMS, otherwise this would have taken longer.

@Troy Chard 2014-06-06 20:28:10

@iamserious: Clearing the cache worked for me too. Thanks!

@EmKay 2014-06-17 09:15:41

I had the same problem and I made it work by just making a small change in the SP.

@Dennis 2014-09-22 13:52:34

clearing cache worked for me too!

@Sirentec 2014-12-10 18:09:37

Cleaning the cache worked for me as well. DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE

@Mangist 2015-07-15 17:04:21

Does anyone know of a permanent fix for this? The DBCC FREEPROCCACHE command works, but the timeouts return after a day or two.

@StriplingWarrior 2015-07-15 17:52:22

@Mangist: Because this is a complicated issue, there's no silver bullet, but you could try using OPTIMIZE FOR or OPTION(Recompile) query hints on the queries that are causing problems. This article discusses the problem in depth. If Entity Framework is generating your queries, this post appears to provide a way to add a query hint to your queries.

@jnoreiga 2015-10-21 14:20:56

Instead of running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE which will clear ALL execution plans you can drop and recreate the problem stored procedure.

@Christopher Townsend 2016-07-18 14:09:54

@iamserious .. Thankyou so much, this has been irritating me for sooooooooooooo long. You are a super star!

@Tundey 2011-07-05 16:01:27

Could it be that some other DB calls made before the web application calls the SP is keeping a transaction open? That could be a reason for this SP to wait when called by the web application. I say isolate the call in the web application (put it on a new page) to ensure that some prior action in the web application is causing this issue.

@iamserious 2011-07-05 16:12:27

hm, good plan, will do that and get back, thanks.

@iamserious 2011-07-05 16:23:39

Hi @Tundey, I isolated the call and it's still taking approx 30 sec and times out. so it has to be something between the communication, I guess?

@Tundey 2011-07-05 16:27:44

then am stumped.

@iamserious 2011-07-05 16:35:19

thanks for your answer though :]

Related Questions

Sponsored Content

49 Answered Questions

7 Answered Questions

12 Answered Questions

[SOLVED] SQL Server: Query fast, but slow from procedure

1 Answered Questions

8 Answered Questions

[SOLVED] Calling stored procedure with return value

1 Answered Questions

Complex stored procedure not executing when called by sqlsrv_execute

6 Answered Questions

[SOLVED] Query runs fast, but runs slow in stored procedure

6 Answered Questions

2 Answered Questions

[SOLVED] SQL Server stored procedure no statements run when from RPC

2 Answered Questions

[SOLVED] Access Web service from Oracle stored procedure

Sponsored Content