none
SQL recompilando toda vez stored procedure RRS feed

  • Pergunta

  • Estou observando meu database com com uma consulta igual ao sp_WhoIsActive  [codigo sp_WhoisActive]

    E vejo que minhas stored procedure estão toda hora sendo criadas, pois aparecem assim

    CREATE PROCEDURE [dbo].[stp_SIGA_2007_COLUNAS] @IDP int, @in...
    Será que existe algum problema em meu MS-SQL que faz a stored-procedure ser recompilada toda vez ? Ou eu estou apenas me confundindo e não esta sendo criada toda vez não ?



    • Editado ASPX_BR sexta-feira, 9 de agosto de 2013 20:26
    sexta-feira, 9 de agosto de 2013 20:25

Respostas

  • Eu também utilizo a sp_whoisactive em produção e quando mostra o CREATE procedure não quer dizer que está criando a procedure. Isso é simplesmente o código completo da procedure que está sendo executada.

    Se a procedure tivesse sendo excluída e recriada, daria vários erros no caso dela ser executada simultaneamente por várias pessoas, concorda? Se quiser comprovar isso, basta criar uma DDL trigger e ver quando a procedure é criada.

    Sobre a recompilação, como é uma procedure e não tem WITH recompile no código, ela não deve estar sendo recompilada. Para confirmar, basta utilizar a query abaixo:

    -- 2) Verificar as querys que estão no cache
    SELECT  text,plan_handle, cp.size_in_bytes,usecounts--,*
    FROM sys.dm_Exec_cached_plans AS cp
          CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    WHERE text not like '%dm_exec_sql_text%' --para não aparecer essa propria query
        and text not like '%dm_Exec_cached_plans%' --para não aparecer essa propria query]
        and text like '%stp_SIGA_2007_COLUNAS%'
    ORDER BY cp.size_in_bytes DESC

    Observe na coluna usecounts a quantidade de vezes que esse plano foi reutilizado em cache.

    Agora rode um "sp_recompile stp_SIGA_2007_COLUNAS" e observe que esse valor é reiniciado pois você forçou uma recompilação da procedure.

    Dessa forma, se sua procedure tivesse sendo recompilada toda vez, o valor do usecounts não cresceria da forma como deve estar crescendo.

    Espero que não tenha ficado confusa a explicação.


    Fabrício França Lima | MCP, MCTS, MCITP | Twitter: @fabriciodba | Consultoria Remota SQL Server: http://fabriciolima.net/blog/consultoria-remota/

    • Sugerido como Resposta Fabricio França Lima sexta-feira, 16 de agosto de 2013 01:48
    • Marcado como Resposta ASPX_BR sexta-feira, 16 de agosto de 2013 18:55
    sexta-feira, 16 de agosto de 2013 00:29

Todas as Respostas

  • Leonardo, se esta aparecendo create procedure no comando "SP_who2 active" por exemplo é por que esta realmente criando a procedure, veja qual o SPID que esta fazendo isto, se é abaixo de 50 é do proprio SQL, caso acima algum usuario ou alguma rotina é que esta criando ela, veja qual o usuario que esta rodando a instrução, pode ser que voce tenha algum job ou mesmo na aplicação que faça alguma coisa que fique dando drop e recriando...

    Alexandre Matayosi Conde Mauricio.

    sexta-feira, 9 de agosto de 2013 20:53
  • Leonardo,

    Por acaso não esta definido no código fonte da sua Stored Procedure que a mesma deve ser recompilida sempre antes de ser executada?

    Verifique se a opção With Recompile não esta definida.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 9 de agosto de 2013 23:19
  • Pedro Antonio Galvão Junior:

    Não tem nada no código que faça a compilação, foi a primeira coisa que fui verificar.


    CeiltonLM
    Sim já sei como recompilar manualmente o que eu quero é que ela pare de recompilar, acredito que isso perde perfomance e não mantem um cache da sp.

    Alexandre Matayosi Conde Mauricio

    os spid são todos altos, acima de 2000 deve ser o cliente através do website. Porém será que o sp_whoisactive que esta mostrando como a storedprocedure é? digo, na verdade esta apenas executando e aparece como create stored procedure..etc.. Fazendo eu pensar que toda vez esta sendo criada a sp ?

    ex:

    <?query --
    /*
    esta stored procedure retorna:
    O tempo em que o atendente do chat nao respondeu "ping"
    */
    
    CREATE PROCEDURE [dbo].[stp_CHAT_RECUPERA_ATENDIMENTOS_PARA_ATENDENTE]
    
    @vid_cliente int,
    @status_chat int
    
    AS
    set nocount on
    if  (@status_chat = 0)
    SELECT     (SELECT     TOP (1) bool_EXIBIDO_PESSOA2
                           FROM          tbl_...

    essa stored procedure fi eu mesmo que criei e quem chama ela é uma page asp, que fui verificar o código esta perfeito.

    Será que o MS SQL por algum motivo do cão esta recriando toda vez ? ou só estou vendo errado?

    sábado, 10 de agosto de 2013 18:03
  • Leonardo,

    Faça o seguinte, tente limpar o Cache de Transações do seu SQL Server, ou então, reinicialize o seu SQL Server e verifique se isso para de ocorrer.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quarta-feira, 14 de agosto de 2013 15:03
  • Leonardo, tem algum SET além do NOCOUNT ou algum exec()?

    Se der, vale a pena dar uma lida neste link no Stack Overflow, resposta do EBarr. A partir dali dá pra montar um plano de investigação mais apurado.


    - :)

    quarta-feira, 14 de agosto de 2013 15:18
  • Eu também utilizo a sp_whoisactive em produção e quando mostra o CREATE procedure não quer dizer que está criando a procedure. Isso é simplesmente o código completo da procedure que está sendo executada.

    Se a procedure tivesse sendo excluída e recriada, daria vários erros no caso dela ser executada simultaneamente por várias pessoas, concorda? Se quiser comprovar isso, basta criar uma DDL trigger e ver quando a procedure é criada.

    Sobre a recompilação, como é uma procedure e não tem WITH recompile no código, ela não deve estar sendo recompilada. Para confirmar, basta utilizar a query abaixo:

    -- 2) Verificar as querys que estão no cache
    SELECT  text,plan_handle, cp.size_in_bytes,usecounts--,*
    FROM sys.dm_Exec_cached_plans AS cp
          CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    WHERE text not like '%dm_exec_sql_text%' --para não aparecer essa propria query
        and text not like '%dm_Exec_cached_plans%' --para não aparecer essa propria query]
        and text like '%stp_SIGA_2007_COLUNAS%'
    ORDER BY cp.size_in_bytes DESC

    Observe na coluna usecounts a quantidade de vezes que esse plano foi reutilizado em cache.

    Agora rode um "sp_recompile stp_SIGA_2007_COLUNAS" e observe que esse valor é reiniciado pois você forçou uma recompilação da procedure.

    Dessa forma, se sua procedure tivesse sendo recompilada toda vez, o valor do usecounts não cresceria da forma como deve estar crescendo.

    Espero que não tenha ficado confusa a explicação.


    Fabrício França Lima | MCP, MCTS, MCITP | Twitter: @fabriciodba | Consultoria Remota SQL Server: http://fabriciolima.net/blog/consultoria-remota/

    • Sugerido como Resposta Fabricio França Lima sexta-feira, 16 de agosto de 2013 01:48
    • Marcado como Resposta ASPX_BR sexta-feira, 16 de agosto de 2013 18:55
    sexta-feira, 16 de agosto de 2013 00:29