Procedimento armazenado lento quando chamado da web, rápido do Management Studio

Eu tenho armazenado o procedimento que insanamente expira a cada vez que é chamado a partir do aplicativo da web.

Ativei o Sql Profiler e localizei as chamadas que expiraram e finalmente descobri essas coisas:

  1. Quando executadas as instruções de dentro do MS SQL Management Studio, com os mesmos argumentos (na verdade, copiei a chamada de procedimento do sql profile trace e executei): Termina em 5 ~ 6 segundos de média.
  2. Mas quando chamado de aplicativo da web, ele leva mais de 30 segundos (em rastreio), então minha página da Web realmente expira até lá.

Além do fato de que meu aplicativo web tem seu próprio usuário, tudo é igual (mesmo database, conexão, servidor etc). Também tentei executar a consulta diretamente no estúdio com o usuário do aplicativo da web e não leva mais que 6 seg.

Como faço para descobrir o que está acontecendo?

Eu estou supondo que não tem nada a ver com o fato de que usamos camadas BLL> DAL ou adaptadores de tabela como o rastreamento mostra claramente o atraso está no procedimento real. Isso é tudo que posso pensar.

EDIT Eu descobri neste link que o ADO.NET define ARITHABORT como true – o que é bom para a maior parte do tempo, mas às vezes isso acontece, e a alternativa sugerida é adicionar with recompile opção de with recompile ao proc armazenado. No meu caso, não está funcionando, mas suspeito que seja algo muito parecido com isso. Alguém sabe o que mais o ADO.NET faz ou onde posso encontrar a especificação?

Eu tive um problema semelhante surgir no passado, então estou ansioso para ver uma solução para esta questão. O comentário de Aaron Bertrand sobre o OP levou a Query a expirar quando executado a partir da Web, mas super rápido quando executado a partir do SSMS e, embora a questão não seja uma duplicata, a resposta pode muito bem se aplicar à sua situação.

Em essência, parece que o SQL Server pode ter um plano de execução em cache corrompido. Você está atingindo o plano ruim com seu servidor da Web, mas o SSMS está em um plano diferente, já que há uma configuração diferente no sinalizador ARITHABORT (que, de outra forma, não teria impacto em sua consulta / procedimento armazenado específico).

Veja ADO.NET chamando T-SQL Stored Procedure causa um SqlTimeoutException para outro exemplo, com uma explicação e resolução mais completas.

Eu também acho que as consultas estavam sendo executadas lentamente da web e rapidamente no SSMS e, finalmente, descobri que o problema era algo chamado de detecção de parâmetro.

A correção para mim foi alterar todos os parâmetros usados ​​no sproc para variables ​​locais.

por exemplo. mudança:

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

para:

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

Parece estranho, mas consertou meu problema.

Não para spam, mas como uma solução esperançosamente útil para outros, nosso sistema viu um alto grau de timeouts.

Eu tentei definir o procedimento armazenado para ser recompilado usando sp_recompile e isso resolveu o problema para um SP.

Em última análise, houve um número maior de DBCC DROPCLEANBUFFERS tempo limite, muitos dos quais nunca haviam feito isso antes, usando DBCC DROPCLEANBUFFERS e DBBC FREEPROCCACHE taxa de tempo limite de incidentes caiu significativamente – ainda há ocorrências isoladas, algumas nas quais suspeito que O plano de regeneração está demorando um pouco, e alguns em que os SPs estão genuinamente abaixo do desempenho e precisam de reavaliação.

Será que algumas outras chamadas de database feitas antes do aplicativo da Web chamar o SP estão mantendo uma transação aberta? Isso pode ser um motivo para esse SP aguardar quando chamado pelo aplicativo da web. Digo isolar a chamada no aplicativo da Web (colocá-lo em uma nova página) para garantir que alguma ação anterior no aplicativo da Web esteja causando esse problema.

Simplesmente recompilar o procedimento armazenado (function de tabela no meu caso) funcionou para mim

como o @Zane disse que poderia ser devido ao parâmetro sniffing. Eu experimentei o mesmo comportamento e eu dei uma olhada no plano de execução do procedimento e em todas as instruções do sp em uma linha (copiei todas as instruções do procedimento, declarei os parâmetros como variables ​​e associei os mesmos valores para a variável como os parâmetros tinham). No entanto, o plano de execução parecia completamente diferente. A execução do sp levou de 3 a 4 segundos e as declarações seguidas com exatamente os mesmos valores foram retornadas instantaneamente.

plano de execução

Depois de alguns googling eu encontrei uma leitura interessante sobre esse comportamento: lento no aplicativo, rápido no SSMS?

Ao compilar o procedimento, o SQL Server não sabe que o valor de @fromdate é alterado, mas compila o procedimento pressupondo que @fromdate tenha o valor NULL. Como todas as comparações com NULL produzem UNKNOWN, a consulta não pode retornar nenhuma linha, se @fromdate ainda tiver esse valor em tempo de execução. Se o SQL Server considerasse o valor de input como a verdade final, ele poderia construir um plano com apenas uma Varredura Constante que não acessasse a tabela (execute a consulta SELECT * FROM Orders WHERE OrderDate> NULL para ver um exemplo disso) . Mas o SQL Server deve gerar um plano que retorne o resultado correto, independentemente do valor que @fromdate tenha em tempo de execução. Por outro lado, não há obrigação de construir um plano que seja o melhor para todos os valores. Portanto, como a suposição é de que nenhuma linha será retornada, o SQL Server se instala para a Procura de índice.

O problema era que eu tinha parâmetros que poderiam ser deixados nulos e, se fossem passados ​​como nulos, seriam inicializados com um valor padrão.

 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 

Depois que eu mudei para

 create procedure dbo.procedure @dateTo datetime = null begin declare @to datetime = coalesce(@dateTo, getutcdate()) select foo from dbo.table where createdDate < @to end 

funcionou como um encanto novamente.