{"id":296,"date":"2010-07-25T10:48:17","date_gmt":"2010-07-25T13:48:17","guid":{"rendered":"http:\/\/fabriciodba.wordpress.com\/2010\/07\/25\/eu-preciso-dar-um-sp_recompile-em-uma-procedure-apos-altera-la"},"modified":"2016-09-11T13:39:41","modified_gmt":"2016-09-11T16:39:41","slug":"eu-preciso-dar-um-sp_recompile-em-uma-procedure-apos-altera-la","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2010\/07\/25\/eu-preciso-dar-um-sp_recompile-em-uma-procedure-apos-altera-la\/","title":{"rendered":"Eu preciso dar um sp_recompile em uma procedure ap\u00f3s alter\u00e1-la?"},"content":{"rendered":"
\n

Fala Pessoal,<\/p>\n

Voc\u00ea j\u00e1 ouviu algu\u00e9m dizer que quando se altera uma procedure devemos dar um sp_recompile nessa SP?<\/p>\n

At\u00e9 essa semana eu acreditava realmente que isso era necess\u00e1rio. O argumento que me deram quando eu aprendi e que eu mesmo respondia para algu\u00e9m quando era questionado sobre o motivo de executar a sp_recompile em uma procedure ap\u00f3s alter\u00e1-la era o seguinte:<\/p>\n

\u201cQuando alteramos uma procedure devemos executar o sp_recompile nessa procedure para for\u00e7armos uma recompila\u00e7\u00e3o do seu plano de execu\u00e7\u00e3o que pode ter sido alterado com a manuten\u00e7\u00e3o que foi realizada nessa procedure.\u201d<\/p>\n

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\u00e7\u00e3o dessa procedure. Entretanto, realizando a leitura do meu livro SQL Server 2008 Internals, eu li que a execu\u00e7\u00e3o do comando ALTER PROCEDURE exclui o plano dessa procedure do cache. Na mesma hora veio a voz do Silvio Santos na minha cabe\u00e7a: \u201cMa ma eu s\u00f3 acreditoooo\u2026 veeeendoo, hi hiiii\u201d.Ent\u00e3o subi minha Vm para realizar meus testes.<\/p>\n

Inicialmente, criei uma tabela e populei a mesma com 1000 registros.<\/p>\n

\r\nCREATE TABLE [dbo].[Venda] (\r\n\t[Id_Venda] INT IDENTITY(1,1),\r\n\t[Dt_Venda] DATETIME,\r\n\t[Vl_Venda] NUMERIC(15,2)\r\n)\r\n\r\nGO\r\nINSERT INTO [dbo].[Venda] ([Dt_Venda], [Vl_Venda])\r\nSELECT GETDATE(), CAST(1000000 * RAND() AS INT) % 1000\r\nGO 1000\r\n\r\nGO\r\nCREATE CLUSTERED INDEX [SK01_Venda] ON [dbo].[Venda] ([Id_Venda])\r\nCREATE NONCLUSTERED INDEX [SK02_Venda] ON [dbo].[Venda] ([Vl_Venda]) INCLUDE([Dt_Venda])\r\n<\/pre>\n
Em seguida criei duas procedures para realizar os testes.<\/p>\n
\r\nCREATE PROCEDURE [dbo].[stpConsulta_Vendas]\r\n\t@Vl_Venda NUMERIC(15, 2)\r\nAS\r\nBEGIN\r\n\tSELECT [Id_Venda], [Dt_Venda], [Vl_Venda]\r\n\tFROM [dbo].[Venda]\r\n\tWHERE [Vl_Venda] >= @Vl_Venda\r\nEND\r\n\r\nGO\r\nCREATE PROCEDURE [dbo].[stpConsulta_Vendas_2] \r\n\t@Vl_Venda NUMERIC(15, 2)\r\nAS\r\nBEGIN\r\n\tEXEC stpConsulta_Vendas @Vl_Venda\r\nEND\r\n<\/pre>\n
Com a query abaixo podemos ver quais os planos de execu\u00e7\u00e3o est\u00e3o em cache. Chamarei essa query de QUERY A para referenciar as execu\u00e7\u00f5es posteriores da mesma.<\/p>\n
\r\nSELECT [Text], [Plan_Handle], [Size_in_bytes], [Usecounts]\r\nFROM [sys].[dm_Exec_cached_plans] AS cp\r\nCROSS APPLY [sys].[dm_exec_sql_text] ([plan_handle])\r\nWHERE [Text] LIKE '%stpConsulta_Vendas%'\t\t\t-- Para visualizar apenas o plano dessas procedures\r\n      AND [Text] NOT LIKE '%dm_Exec_cached_plans%'\t-- Para n\u00e3o aparecer essa propria query\r\n      AND [Objtype] = 'Proc'\t\t\t\t\t\t-- Procedures\r\nORDER BY [Size_in_bytes] DESC\r\n<\/pre>\n
Neste momento, essa query n\u00e3o retorna nenhum registro. Entretanto, ap\u00f3s a execu\u00e7\u00e3o do script abaixo.<\/p>\n
\r\nEXEC [dbo].[stpConsulta_Vendas] 900.00\r\nGO\r\nEXEC [dbo].[stpConsulta_Vendas] 800.50\r\nGO\r\nEXEC [dbo].[stpConsulta_Vendas_2] 950.46\r\nGO\r\nEXEC [dbo].[stpConsulta_Vendas_2] 990.25\r\n<\/pre>\n
Executando a QUERY A novamente temos o seguinte resultado.<\/p>\n
\"FIGURA<\/a><\/p>\n
Podemos claramente verificar no valor da coluna Usecounts que o mesmo plano de execu\u00e7\u00e3o da stpConsulta_Vendas foi utilizado 4 vezes, sendo duas chamadas diretas e duas chamadas de dentro da stpConsulta_Vendas_2. J\u00e1 o plano de execu\u00e7\u00e3o da stpConsulta_Vendas_2 foi utilizado as duas vezes que foi chamado explicitamente.<\/p>\n
Agora que \u00e9 a hora da verdade, vamos alterar nossa procedure e ver se o planos de execu\u00e7\u00e3o ser\u00e1 eliminado do cache.<\/p>\n
\r\nALTER PROCEDURE [dbo].[stpConsulta_Vendas]\r\n\t@Vl_Venda NUMERIC(15, 2)\r\nAS\r\nBEGIN\r\n\tSELECT [Id_Venda], [Dt_Venda], [Vl_Venda]\r\n\tFROM [Venda]\r\n\tWHERE [Vl_Venda] >= @Vl_Venda\r\nEND\r\n<\/pre>\n
Executando a Query A, temos o seguinte resultado.<\/p>\n
\"FIGURA<\/a><\/p>\n<\/div>\n
Logo, o plano de execu\u00e7\u00e3o 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\u00e7\u00e3o dessa procedure j\u00e1 \u00e9 exclu\u00eddo do cache automaticamente, n\u00e3o necessitando assim de executarmos o comando sp_recompile para essa procedure.<\/p>\n
Aproveitando, vamos executar o comando sp_recompile para a stoConsulta_Vendas_2 para visualizarmos que o plano de execu\u00e7\u00e3o dessa procedure tamb\u00e9m ser\u00e1 exclu\u00eddo do cache.<\/p>\n
exec sp_recompile stpConsulta_Vendas_2<\/p>\n
Executando novamente a QUERY A \u00e9 poss\u00edvel identificar que o plano de execu\u00e7\u00e3o dessa procedure tamb\u00e9m foi retirado do cache.<\/p>\n
Em suma, a partir de agora nunca mais executarei um sp_recompile ap\u00f3s alterar uma procedure. Vivendo e aprendendo.<\/p>\n
 
\nGostou desse Post?<\/b><\/p>\n
Cadastre seu e-mail para receber novos Posts e curta minha P\u00e1gina no Facebook<\/a> para receber Dicas de Leituras e Eventos sobre SQL Server.<\/p>\n
Abra\u00e7os,<\/p>\n
Fabr\u00edcio Lima<\/p>\n
MCITP \u2013 Database Administrator<\/p>\n
Consultor e Instrutor SQL Server<\/p>\n
Trabalha com SQL Server desde 2006<\/p>\n","protected":false},"excerpt":{"rendered":"
Fala Pessoal, Voc\u00ea j\u00e1 ouviu algu\u00e9m dizer que quando se altera uma procedure devemos dar um sp_recompile nessa SP? At\u00e9 essa semana eu acreditava realmente que isso era necess\u00e1rio. O argumento que me deram quando eu aprendi e que eu mesmo respondia para algu\u00e9m quando era questionado sobre o motivo de executar a sp_recompile em […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[3,280],"tags":[69,23,71,72,27,70,49,33,50,34,35],"class_list":["post-296","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-virtual-pass-br","tag-administracao-de-banco-de-dados","tag-dba","tag-dm_exec_cached_plans","tag-dm_exec_sql_text","tag-internals","tag-sp_recompile","tag-sql","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-sql-server-2008-r2"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/296","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/comments?post=296"}],"version-history":[{"count":0,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/296\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=296"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=296"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=296"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}