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

Question

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?

Subjoin

This could be related to how much data is being returned? – Hying

@Barry: No, as I run the same procedure (copied from trace too - meaning same parameters) in management studio, it runs within 6 seconds. – Subjoin

@Jayantha: The point is NOT that the sp is slow, but SOMETHING between ado.net and sql is. I dont see how the sp would make any difference. – Subjoin

Does the SP return a lot of data, for example image/text/varchar(max) columns? The amount of data to consume on the client would be huge which will take a lot of time. SSMS cuts off these resultsets in a more efficient matter. – Ireneirenic

Possible duplicate of #2248612 – Ethelda

@Aaron, I have tried messing with the ARITHABORT options AND WITH RECOMPILE but they did not have any effects, as I have mentioned in my question. – Subjoin

Does this answer your question? Query times out when executed from web, but super-fast when executed from SSMS – Spiderwort

Answer 1

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.

Surfeit

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

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. – Subjoin

@Subjoin - 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. – Shiest

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. – Pomfrey

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

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

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

@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. – Surfeit

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

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

Answer 2

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.

Polytonality

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

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

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

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? – Cleistogamy

Did it for me too. Thanks! – Wallen

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)? – Inkerman

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. – Garnet

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. – Corcyra

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

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

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

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, @Polytonality ! – Khoury

Yeah, that's really not the answer. You'd get the same changes simply by recompiling the sproc. You should at least run it for a week and make sure it's actually fixed. – Halftrack

Same here. Long running query was taking 5-6 sec in Studio and forever in code (often timing out ~ 3 min more). Truly magical – Bouchard

This workk!!! with this change I haven´t need to set SET ARITHABORT ON, because I have 2 execution time, now I have 1 – Horsepowerhour

Answer 3

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 DBCC 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.

Chantress

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. – Evalynevan

Answer 4

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.

Runlet

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? – Subjoin

Answer 5

You can target specific cached execution plans via:

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%your troublesome SP or function name etc%'

And then remove only the execution plans causing issues via, for example:

DBCC FREEPROCCACHE (0x050006003FCA862F40A19A93010000000000000000000000)

I've now got a job running every 5 minutes that looks for slow running procedures or functions and automatically clears down those execution plans if it finds any:

if exists (
    SELECT cpu_time, *
    FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
    --order by req.total_elapsed_time desc
    WHERE ([text] LIKE N'%your troublesome SP or function name etc%')
    and cpu_time > 8000
)
begin

    SELECT cp.plan_handle, st.[text]
    into #results
    FROM sys.dm_exec_cached_plans AS cp 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE [text] LIKE N'%your troublesome SP or function name etc%'
    delete #results where text like 'SELECT cp.plan_handle%'
    --select * from #results

    declare @handle varbinary(max)
    declare @handleconverted varchar(max)
    declare @sql varchar(1000)

    DECLARE db_cursor CURSOR FOR  
    select plan_handle from #results

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @handle

    WHILE @@FETCH_STATUS = 0   
    BEGIN   

        --e.g. DBCC FREEPROCCACHE (0x050006003FCA862F40A19A93010000000000000000000000)
        print @handle
        set @handleconverted = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@handle"))', 'VARCHAR(MAX)')
        print @handleconverted
        set @sql = 'DBCC FREEPROCCACHE (' + @handleconverted + ')'
        print 'DELETING: ' + @sql
        EXEC(@sql)

        FETCH NEXT FROM db_cursor INTO @handle
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor

    drop table #results

end

Haversack

Answer 6

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

Yecies

Answer 7

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.

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.

Stetson

Answer 8

--BEFORE
CREATE PROCEDURE [dbo].[SP_DEMO]
( 
    @ToUserId bigint=null
 )
AS
BEGIN
SELECT * FROM tbl_Logins WHERE LoginId = @ToUserId 
END
--AFTER CHANGING TO IT WORKING FINE
CREATE PROCEDURE [dbo].[SP_DEMO]
( 
    @ToUserId bigint=null
 )
AS
BEGIN
DECLARE @Toid bigint=null
SET @Toid=@ToUserId
SELECT * FROM tbl_Logins WHERE LoginId = @Toid 
END

Zedekiah