locked
Aumento de Tempo de Execução Procedure RRS feed

  • Pergunta

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


    • Editado Fabianafdb quarta-feira, 15 de fevereiro de 2017 00:05
    terça-feira, 14 de fevereiro de 2017 10:36

Respostas

  • Fabiana,

    Vamos por partes:

    Em relação ao parameter sniffing este é um tipo de comportamento ou situação que o SQL Server utiliza quando uma Stored Procedure ou até mesmo transação trabalha com uso de variáveis como parâmetros de passagem de valores e neste caso o plano de execução acaba tendo a necessidade de reconhecer e parametrizar este valor como um elemento interno do plano de execução.

    Neste caso o uso do comando Option em conjunto com a opção Optimize vai ajudar o SQL Server a entender que este valor que esta sendo passado por uma variável faz parte da transação que esta sendo executada, algo similar a isto:

    OPTION (OPTIMIZE FOR (@Local = 'LocalA'))

    No que diz respeito ao uso da With Recompile, justamente é uma instrução muito conhecida e fortemente indicado para força o Database Engine a atualizar o plano de execução sempre que instrução for ser executada, mas sinceramente falando eu não vejo a necessidade de utilizar esta instrução em ambiente OLTP.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    domingo, 19 de fevereiro de 2017 00:55
    Moderador