存储过程从 Web 调用时速度较慢，从 ManagementStudio 调用时速度较快

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.

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.

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.

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

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.

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.

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

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