Bom dia!
Pessoal, Mas uma vez conto com apoio de vocês.
Eu pequei um caso que vem me tirando o sono e desejo muito entender a fundo o motivo e ter esse fato como uma lição para o futuro e conhecimento para quem ainda não enfrentou essa situação.
Tenho uma Procedure a qual é executada por diversas vezes ao dia com 3 tipos de retorno conforme o parâmentro.
Exemplo: Exec Sp_ModeloCaro 'celta','preto','2015' - Sp_ModeloCaro 'corolla','branco','2015' - Sp_ModeloCaro 'fox','branco','2015'.
Enfim uma Procedure com parâmtros distintos fixo e Plano de Execução, Servidor de Aplicação XXX executa esta Exec Sp_ModeloCaro 'celta','preto','2015' e Servidor de Aplicação YYYY executa estas Sp_ModeloCaro 'corolla','branco','2015' - Sp_ModeloCaro
'fox','branco','2015'.
Estas execuções executava em 2 Segundos e ocorreu o seguinte:
23h exec 2 segundos, 23h35 exec 2 segundos, 23h44 exec 1minuto.
Quando executava via Management Studio do SQL Server 2 segundos.
No Profiler estava retornando aplicação CPU 60559 - Reads 22002227 - Duration 60556
Quando executava via Management Studio CPU 1654 - Reads 702441 - Duration 1664
Executei Sp_recompile na procedure nenhum efeito.
Criamos uma outra procedure com a mesma funcionalidade foi rápida.
Renomeamos a procedure foi rápida, voltamos para o nome Original foi lenta.
Foi colocado With Recompile dentro do código voltou executar em 2 segundos.
Durante a investigação só vi o seguinte: Todos os dias realizamos Reorg, Rebuild, Recompile e Update Statistics no Plano de Manutenção Noturna.
Neste dia exatamente às 23h44 esta Procedure bloqueou o Plano de Manutenção por mais de 10 segundos, previsão de término do Bloqueio era de 12 minutos.
Wait: LCK_M_SCH_M - Total_elapsed_time: 729938 - CPU_TIME:287650 - MemoryUsage:3 - Logical_Reads:206489937
Tivemos um tipo de travamento na placa de rede, pois no eHealth motra um buraco neste horário, o pessoal de redes não soube explicar.
Tivemos por 5 segundos Time out na chamada do Mirror e não houve Failover do Cluster.
Bom o que poderia ter ocorrido é possível que este bloqueio tenha impactado no Plano de Execução desta Procedure?
O problema na Placa teria alguma relação ou simplesmente uma conhecidencia
Porque os Custos subiram tanto?
Li algumas coisas referente parameter sniffing, o qual informa que procedures com execução de multiplos parâmetros, mas qual seria o
catilho? Para vim executando por muito tempo bem e quando menos espera isso ocorre.
Uma outra questão o With Recompile não é indicado para OLTP, devido sua rotatividade de execução com novos Plano de Execução a cada execução,
então necessito buscar alternativas para evitar um novo problema inesperado.
Enfim necessito entender o gatilho que evidenciou este problema.
Obrigada.
