Fabrício Lima

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.

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.