SQL recompilando toda vez stored procedure

Question

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 ?

Answer 1

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.

Answer 2

Vide (with recompile) :

http://technet.microsoft.com/pt-br/library/ms190439.aspx

Answer 3

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]

Answer 4

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?

Answer 5

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]

Answer 6

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.

---

- :)

Answer 7

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/