Stored procedure is slow when called from the Internet, quickly from Management Studio

Question

I have a stored procedure that gets madly played every time it is called from a web application.

I started Sql Profiler and tracked this timeout and finally found out about it:

• When executing statements from MS SQL Management Studio with the same arguments (in fact, I copied the procedure call from the sql profile profile and started it): it ends in 5-6 seconds.
• But when called from a web application, it takes more than 30 seconds (in a trace), so my web page is actually expiring by then.

Besides the fact that my web application has its own user, each thing is the same (the same database, connection, server, etc.), I also tried to run the request directly in the studio with the user of the web application and doesn’t take more than 6 seconds.

How to find out what is going on?

I assume that this has nothing to do with the fact that we use BLL> DAL levels or Table adapters, since the trace clearly shows that the delay is in the actual procedure. That’s all I can think of.

EDIT I found in this link that ADO.NET sets ARITHABORT to true - this is good for most of the time, but sometimes it happens, and the proposed work is to add the with recompile option to the saved process. In my case, this does not work, but I suspect it is very similar to this. Does anyone know what else ADO.NET is doing or where can I find the spec?

Answer 1

I had a similar problem in the past, so I really want to see a solution to this issue. Aaron Bertrand comments on the OP, causing the request time to be executed from the Internet, but superfast when executed from SSMS , and although the question is not a duplicate, the answer may very well relate to your situation.

Essentially, it looks like SQL Server might have a damaged caching execution plan. You click on a bad plan on your web server, but SSMS lands on a different plan, because there is another setting on the ARITHABORT flag (which would not otherwise affect your specific request / saved process).

See ADO.NET calling the T-SQL stored procedure throws a SqlTimeoutException for another example with a more complete explanation and resolution.

Answer 2

I also experience that requests started slowly from the Internet and worked quickly in SSMS, and I eventually found out that the problem was something called the sniffing parameter.

The fix for me was to change all the parameters that sproc uses for local variables.

eg. edit:

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

at

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

Seems strange, but he fixed my problem.

Answer 3

Could it be that some other database calls made before the web application calls the SP keep the transaction open? This may cause this SP to wait when invoking a web application. I say isolate the call in the web application (put it on a new page) to make sure that some of the previous steps in the web application are causing this problem.

Answer 4

Not spam, but, as we hope, a useful solution for others, our system saw high timeouts.

I tried reinstalling the stored procedure using sp_recompile , and this solved the problem for a single SP.

Ultimately there were more SPs that were timeout, many of which had never done this before using DBCC DROPCLEANBUFFERS and DBBC FREEPROCCACHE , the frequency of timeout incidents fell sharply - there are still individual cases, some where I suspect that the regeneration The plan takes some time, and some where the joint ventures are really not effective enough and need re-evaluation.

Answer 5

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

Answer 6

as @Zane said this could be due to parameterization. I experienced the same behavior, and I looked at the execution plan of the procedure and all the sp statements in the line (I copied all the instructions from the procedure, declared the parameters as variables and assigned the same values ​​for the variable as the parameters were). However, the execution plan looked completely different. Sp was executed for 3-4 seconds, and the statements in the line with the same values ​​were immediately returned.

After some googling, I found it interesting to read about this behavior: Slow application, fast in SSMS?

When compiling a procedure, SQL Server does not know that the @fromdate value changes, but compiles the procedure under the assumption that @fromdate is NULL. Since all comparisons with NULL give UNKNOWN, the query cannot return any rows at all if @fromdate still has this value at runtime. If SQL Server accepts the input value as final truth, it can build a plan with only a constant scan that does not access the table at all (run the SELECT * FROM Orders WHERE OrderDate> NULL query to see an example of this), but SQL Server should generate a plan, which returns the correct result no matter what @fromdate value has at runtime. On the other hand, there is no obligation to draw up a plan that is best for all values. Thus, since the assumption is that no rows will be returned, SQL Server sets up the index search.

The problem was that I had parameters that could be left blank, and if they were passed as null, they would be initialized 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 

he worked like a charm again.