Início > Administração de BD > Eu preciso dar um sp_recompile em uma procedure após alterá-la?

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

Fala Pessoal,

Você já houviu 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 eu houvi quando 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 Venda(
      Id_Venda INT IDENTITY(1,1),
      Dt_Venda DATETIME,
      Vl_Venda NUMERIC(15,2)
      )

GO
INSERT INTO Venda(Dt_Venda,Vl_Venda)
SELECT GETDATE(),CAST(1000000*RAND() AS INT)%1000
GO 1000
GO
CREATE CLUSTERED INDEX SK01_Venda ON Venda(Id_Venda)
CREATE NONCLUSTERED INDEX SK02_Venda ON Venda(Vl_Venda)include(Dt_Venda)

Em seguida criei duas procedures para realizar os testes.

CREATE PROCEDURE stpConsulta_Vendas @Vl_Venda NUMERIC(15,2)
AS
SELECT Id_Venda,Dt_Venda,Vl_Venda
FROM Venda
WHERE Vl_Venda >= @Vl_Venda

GO

CREATE PROCEDURE stpConsulta_Vendas_2 @Vl_Venda NUMERIC(15,2)
AS
EXEC stpConsulta_Vendas @Vl_Venda

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

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

Executando a QUERY A novamente temos o seguinte resultado.

FIGURA 1

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 stpConsulta_Vendas @Vl_Venda NUMERIC(15,2)
AS
SELECT Id_Venda,Dt_Venda,Vl_Venda
FROM Venda
WHERE Vl_Venda >= @Vl_Venda

Executando a Query A, temos o seguinte resultado.

FIGURA 2

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.

Abraços,

Fabrício França Lima

Anúncios
  1. Felipe
    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..

  2. Fabrício
    26 de julho de 2010 às 10:53

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

  3. Alexandre
    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

  4. Fabrício
    6 de agosto de 2010 às 8: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

  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: