Eu preciso dar um sp_recompile em uma procedure após alterá-la?

25 de julho de 201011 de setembro de 2016Fabrício LimaAdministração de Banco de Dados, Virtual PASS BR

Fala Pessoal,

Você já ouviu alguém dizer que quando se altera uma procedure devemos dar um sp_recompile nessa SP?

Até essa semana eu acreditava realmente que isso era necessário. O argumento que me deram quando eu aprendi e que eu mesmo respondia para alguém quando era questionado sobre o motivo de executar a sp_recompile em uma procedure após alterá-la era o seguinte:

“Quando alteramos uma procedure devemos executar o sp_recompile nessa procedure para forçarmos uma recompilação do seu plano de execução que pode ter sido alterado com a manutenção que foi realizada nessa procedure.”

Realmente, faz todo o sentido, pois ao alterarmos uma procedure podemos incluir ou excluir uma query, o que com certeza altera o plano de execução dessa procedure. Entretanto, realizando a leitura do meu livro SQL Server 2008 Internals, eu li que a execução do comando ALTER PROCEDURE exclui o plano dessa procedure do cache. Na mesma hora veio a voz do Silvio Santos na minha cabeça: “Ma ma eu só acreditoooo… veeeendoo, hi hiiii”.Então subi minha Vm para realizar meus testes.

Inicialmente, criei uma tabela e populei a mesma com 1000 registros.

CREATE TABLE [dbo].[Venda] (
	[Id_Venda] INT IDENTITY(1,1),
	[Dt_Venda] DATETIME,
	[Vl_Venda] NUMERIC(15,2)
)

GO
INSERT INTO [dbo].[Venda] ([Dt_Venda], [Vl_Venda])
SELECT GETDATE(), CAST(1000000 * RAND() AS INT) % 1000
GO 1000

GO
CREATE CLUSTERED INDEX [SK01_Venda] ON [dbo].[Venda] ([Id_Venda])
CREATE NONCLUSTERED INDEX [SK02_Venda] ON [dbo].[Venda] ([Vl_Venda]) INCLUDE([Dt_Venda])

CREATE TABLE [dbo].[Venda] (

[Id_Venda] INT IDENTITY(1,1),

[Dt_Venda] DATETIME,

[Vl_Venda] NUMERIC(15,2)

)

INSERT INTO [dbo].[Venda] ([Dt_Venda], [Vl_Venda])

SELECT GETDATE(), CAST(1000000 * RAND() AS INT) % 1000

GO 1000

CREATE CLUSTERED INDEX [SK01_Venda] ON [dbo].[Venda] ([Id_Venda])

CREATE NONCLUSTERED INDEX [SK02_Venda] ON [dbo].[Venda] ([Vl_Venda]) INCLUDE([Dt_Venda])

Em seguida criei duas procedures para realizar os testes.

CREATE PROCEDURE [dbo].[stpConsulta_Vendas]
	@Vl_Venda NUMERIC(15, 2)
AS
BEGIN
	SELECT [Id_Venda], [Dt_Venda], [Vl_Venda]
	FROM [dbo].[Venda]
	WHERE [Vl_Venda] >= @Vl_Venda
END

GO
CREATE PROCEDURE [dbo].[stpConsulta_Vendas_2] 
	@Vl_Venda NUMERIC(15, 2)
AS
BEGIN
	EXEC stpConsulta_Vendas @Vl_Venda
END

CREATE PROCEDURE [dbo].[stpConsulta_Vendas]

@Vl_Venda NUMERIC(15, 2)

BEGIN

SELECT [Id_Venda], [Dt_Venda], [Vl_Venda]

FROM [dbo].[Venda]

WHERE [Vl_Venda] >= @Vl_Venda

END

CREATE PROCEDURE [dbo].[stpConsulta_Vendas_2]

@Vl_Venda NUMERIC(15, 2)

BEGIN

EXEC stpConsulta_Vendas @Vl_Venda

END

Com a query abaixo podemos ver quais os planos de execução estão em cache. Chamarei essa query de QUERY A para referenciar as execuções posteriores da mesma.

SELECT [Text], [Plan_Handle], [Size_in_bytes], [Usecounts]
FROM [sys].[dm_Exec_cached_plans] AS cp
CROSS APPLY [sys].[dm_exec_sql_text] ([plan_handle])
WHERE [Text] LIKE '%stpConsulta_Vendas%'			-- Para visualizar apenas o plano dessas procedures
      AND [Text] NOT LIKE '%dm_Exec_cached_plans%'	-- Para não aparecer essa propria query
      AND [Objtype] = 'Proc'						-- Procedures
ORDER BY [Size_in_bytes] DESC

SELECT [Text], [Plan_Handle], [Size_in_bytes], [Usecounts]

FROM [sys].[dm_Exec_cached_plans] AS cp

CROSS APPLY [sys].[dm_exec_sql_text] ([plan_handle])

WHERE [Text] LIKE '%stpConsulta_Vendas%' -- Para visualizar apenas o plano dessas procedures

AND [Text] NOT LIKE '%dm_Exec_cached_plans%' -- Para não aparecer essa propria query

AND [Objtype] = 'Proc' -- Procedures

ORDER BY [Size_in_bytes] DESC

Neste momento, essa query não retorna nenhum registro. Entretanto, após a execução do script abaixo.

EXEC [dbo].[stpConsulta_Vendas] 900.00
GO
EXEC [dbo].[stpConsulta_Vendas] 800.50
GO
EXEC [dbo].[stpConsulta_Vendas_2] 950.46
GO
EXEC [dbo].[stpConsulta_Vendas_2] 990.25

EXEC [dbo].[stpConsulta_Vendas] 900.00

EXEC [dbo].[stpConsulta_Vendas] 800.50

EXEC [dbo].[stpConsulta_Vendas_2] 950.46

EXEC [dbo].[stpConsulta_Vendas_2] 990.25

Executando a QUERY A novamente temos o seguinte resultado.

Podemos claramente verificar no valor da coluna Usecounts que o mesmo plano de execução da stpConsulta_Vendas foi utilizado 4 vezes, sendo duas chamadas diretas e duas chamadas de dentro da stpConsulta_Vendas_2. Já o plano de execução da stpConsulta_Vendas_2 foi utilizado as duas vezes que foi chamado explicitamente.

Agora que é a hora da verdade, vamos alterar nossa procedure e ver se o planos de execução será eliminado do cache.

ALTER PROCEDURE [dbo].[stpConsulta_Vendas]
	@Vl_Venda NUMERIC(15, 2)
AS
BEGIN
	SELECT [Id_Venda], [Dt_Venda], [Vl_Venda]
	FROM [Venda]
	WHERE [Vl_Venda] >= @Vl_Venda
END

ALTER PROCEDURE [dbo].[stpConsulta_Vendas]

@Vl_Venda NUMERIC(15, 2)

BEGIN

SELECT [Id_Venda], [Dt_Venda], [Vl_Venda]

FROM [Venda]

WHERE [Vl_Venda] >= @Vl_Venda

END

Executando a Query A, temos o seguinte resultado.

Logo, o plano de execução da procedure stpConsulta_Vendas que havia sido executado 4 vezes foi removido do cache. Com isso, confirmamos o fato de que quando alteramos uma procedure, o plano de execução dessa procedure já é excluído do cache automaticamente, não necessitando assim de executarmos o comando sp_recompile para essa procedure.

Aproveitando, vamos executar o comando sp_recompile para a stoConsulta_Vendas_2 para visualizarmos que o plano de execução dessa procedure também será excluído do cache.

exec sp_recompile stpConsulta_Vendas_2

Executando novamente a QUERY A é possível identificar que o plano de execução dessa procedure também foi retirado do cache.

Em suma, a partir de agora nunca mais executarei um sp_recompile após alterar uma procedure. Vivendo e aprendendo.

Gostou desse Post?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

Relacionado

8 thoughts on “Eu preciso dar um sp_recompile em uma procedure após alterá-la?”

Felipe disse:

26 de julho de 2010 às 10:52

Fabricio,Ótima dica, também estou lendo o SQL Server 2008 Internals e estou gostando muito!.Dicas valiosas como essas, são indispensáveis!Abraço..

Responder
Fabrício disse:

26 de julho de 2010 às 10:53

Valeu Felipe… Qualquer descoberta lá nos seus estudos poste para nós também.Abraços

Responder
Alexandre disse:

5 de agosto de 2010 às 18:56

Fabricio,Muito legal seu post, serviu inclusive como referencia para um treinamento que ministrei de SQL Server 2008 em Natal/RN. Realmente o SQL Server 2008 Internals abre a cabeça da gente para a "verdade" com informações verdadeiras e não as suposições muitos livros e blogs apresentam.Aproveita que voce está lendo o Internals, marca um treinamento com a Kellen Delaney. 🙂 Depois é tirar o MCM e correr para o abraço.Saudações,Alexandre Lopes

Responder
Fabrício disse:

6 de agosto de 2010 às 08:54

Valeu Alexandre… Realmente, ele acaba com muitos mitos que eu acreditava…. E olha que eu só li uns 3 capitulos dele até agora…Ja combinei com minha amiga Kellen o curso… rsabraços

Responder
César Carvalho disse:

2 de maio de 2016 às 11:42

No 2005 já tive de recompilar uma procedure que foi trocado os parâmetros de entrada e a procedure continuava a receber os parâmetros velhos, acho que na versão 2008 em diante devem ter melhorado, evitando assim a necessidade de recompilar os objetos.

Responder
1. fabricio disse:
  
  2 de maio de 2016 às 11:56
  
  Olá César,
  
  Não sei qual foi o seu caso, mas quando você da um alter procedure, a procedure é recompilada mesmo na versão do SQL 2005.
  
  Abraços.
  
  Responder
Raphael disse:

26 de julho de 2019 às 11:15

Boa tarde Fabrício, gostaria de tirar uma duvida sobre recompile, eu tenho uma instância com SQL server 2008 R2 e com databases com nível de compatibilidade SQL 2005, por algum motivo no passado foi criado um Job para ficar fazendo recompile de varias procedures de hora em hora que por sua vez minimizou os problemas que tínhamos no passado! Agora mudamos o nível de compatibilidade dos databases de 2005 para 2008 R2, e ainda sim mantivemos o Job de recompile sendo executado, porem após a mudança de nível de compatibilidade começamos a ter problemas de performance, será que com o banco em nível correto de acordo com a instância que é 2008 R2 o recompile constante esta gerando este problema de performance ? será que agora eu deveria parar de fazer o recompile já sabendo que todos os databases estão no nível do SQL 2008 R2, será que o recompile influencia diretamente na performance ?

Responder
1. Fabrício Lima disse:
  
  28 de julho de 2019 às 14:46
  
  Olá,
  
  Conforme respondi no grupo do whatsapp, seu problema parece ser a mudança de versão do otimizador do SQL Server e não o recompile.
  
  Para confirmar teria que comparar os planos das procedures envolvidas nas duas versões.
  
  espero ter ajudado
  
  Responder