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

user_363674 0
Viewed 78176

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?

sql-server-2008 stored-procedures asp.net
8 Answers

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
user_21863640 
--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
user_97871730
Related