{"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":"","jetpack_post_was_ever_published":false},"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"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":241,"url":"https:\/\/fabriciolima.net\/blog\/2011\/01\/10\/querys-do-dia-a-dia-monitoramento-do-banco-de-dados-who-is-active\/","url_meta":{"origin":296,"position":0},"title":"Querys do Dia a Dia – Monitoramento do Banco de Dados – Who is Active","author":"Fabr\u00edcio Lima","date":"10 de janeiro de 2011","format":false,"excerpt":"Fala Pessoal, Nesse inicio de ano meu blog\u00a0ficaria um pouco parado pois estou intensificando meus estudos para atualizar meu t\u00edtulo de MCITP Database Administrator para o SQL Server 2008(prova 70-453). Entretanto, como a m\u00e9dia de acessos do blog est\u00e1 ainda maior que\u00a0no ano passado, n\u00e3o poderia deix\u00e1-lo parado todo esse\u2026","rel":"","context":"Em "Querys do Dia a Dia"","block_context":{"text":"Querys do Dia a Dia","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/querys-do-dia-a-dia\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":286,"url":"https:\/\/fabriciolima.net\/blog\/2010\/04\/11\/como-criar-um-controle-de-versao-de-procedures-views-e-functions-no-sql-server\/","url_meta":{"origin":296,"position":1},"title":"Como criar um Controle de Vers\u00e3o de Procedures, Views e Functions no SQL Server","author":"Fabr\u00edcio Lima","date":"11 de abril de 2010","format":false,"excerpt":"Quantas vezes j\u00e1 te perguntaram qual era o c\u00f3digo de uma fun\u00e7\u00e3o, procedure ou view em uma determinada data? At\u00e9 o SQL Server 2000, s\u00f3 era poss\u00edvel obter essa informa\u00e7\u00e3o atrav\u00e9s da gera\u00e7\u00e3o peri\u00f3dica de arquivos com scripts dos objetos do banco de dados. Entretanto, a partir do SQL Server\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/fg7djq.bay.livefilestore.com\/y1pZnviks_oXTAbvTepEXLmnGOQrvNjLmPIYsnTtHeOc35EqnNRyKgWy5UP9pLfVfu7xdnua1G4ZCJ-dmwm3lqJfpEEYCjfqwI3\/Figura%201%20-%20Resultado%20query.JPG?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/fg7djq.bay.livefilestore.com\/y1pZnviks_oXTAbvTepEXLmnGOQrvNjLmPIYsnTtHeOc35EqnNRyKgWy5UP9pLfVfu7xdnua1G4ZCJ-dmwm3lqJfpEEYCjfqwI3\/Figura%201%20-%20Resultado%20query.JPG?resize=350%2C200 1x, https:\/\/i0.wp.com\/fg7djq.bay.livefilestore.com\/y1pZnviks_oXTAbvTepEXLmnGOQrvNjLmPIYsnTtHeOc35EqnNRyKgWy5UP9pLfVfu7xdnua1G4ZCJ-dmwm3lqJfpEEYCjfqwI3\/Figura%201%20-%20Resultado%20query.JPG?resize=525%2C300 1.5x, https:\/\/i0.wp.com\/fg7djq.bay.livefilestore.com\/y1pZnviks_oXTAbvTepEXLmnGOQrvNjLmPIYsnTtHeOc35EqnNRyKgWy5UP9pLfVfu7xdnua1G4ZCJ-dmwm3lqJfpEEYCjfqwI3\/Figura%201%20-%20Resultado%20query.JPG?resize=700%2C400 2x"},"classes":[]},{"id":268,"url":"https:\/\/fabriciolima.net\/blog\/2011\/01\/17\/querys-do-dia-a-dia-como-encontrar-um-texto-em-functions-views-e-procedures\/","url_meta":{"origin":296,"position":2},"title":"Querys do Dia a Dia – Como encontrar um texto em Functions, Views e Procedures","author":"Fabr\u00edcio Lima","date":"17 de janeiro de 2011","format":false,"excerpt":"Fala Pessoal, Antes de incluir\/excluir\/alterar um campo de uma tabela ou antes de incluir\/excluir\/alterar uma procedure, function ou view se faz necess\u00e1rio procurar no script do banco de dados se esses objetos que ser\u00e3o alterados s\u00e3o utilizados\u00a0por alguma fun\u00e7\u00e3o, view ou SP. Constantemente eu preciso realizar esse tipo de opera\u00e7\u00e3o.\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":578,"url":"https:\/\/fabriciolima.net\/blog\/2011\/03\/24\/querys-do-dia-a-dia-como-verificar-o-codigo-de-um-objeto-via-query\/","url_meta":{"origin":296,"position":3},"title":"Querys do Dia a Dia – Como verificar o c\u00f3digo de um objeto via Query","author":"Fabr\u00edcio Lima","date":"24 de mar\u00e7o de 2011","format":false,"excerpt":"Fala Pessoal, A dica de hoje \u00e9 bem simples, mas pode ser muito \u00fatil para quem ainda n\u00e3o conhece. Hoje estava sendo executada uma grande carga (SELECT * INTO FROM ...) em uma tabela de uma das minhas databases. Nesse exato momento, eu precisava verificar qual era o c\u00f3digo de\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2011\/03\/Expand-Stored-Procedure.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":294,"url":"https:\/\/fabriciolima.net\/blog\/2010\/06\/05\/passo-a-passo-para-encontrar-as-querys-mais-demoradas-do-banco-de-dados-parte-2\/","url_meta":{"origin":296,"position":4},"title":"Passo a passo para encontrar as querys mais demoradas do Banco de Dados – Parte 2","author":"Fabr\u00edcio Lima","date":"5 de junho de 2010","format":false,"excerpt":"Continuando o artigo Passo a passo para encontrar as querys mais demoradas do Banco de Dados - Parte 1. Agora que o trace j\u00e1 foi testado e vimos que ele realmente funciona, criaremos um job para enviar o trace para a tabela de log e automatizar todo o processo. Voc\u00ea\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"Step1_thumb1","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/step1_thumb15b45d.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":293,"url":"https:\/\/fabriciolima.net\/blog\/2010\/06\/05\/passo-a-passo-para-encontrar-as-querys-mais-demoradas-do-banco-de-dados-parte-1\/","url_meta":{"origin":296,"position":5},"title":"Passo a passo para encontrar as querys mais demoradas do Banco de Dados – Parte 1","author":"Fabr\u00edcio Lima","date":"5 de junho de 2010","format":false,"excerpt":"Fala Pessoal, Quem n\u00e3o quer saber quais s\u00e3o as querys mais demoradas do seu ambiente de Banco de Dados? Em quais hor\u00e1rios essas querys rodam? Quem est\u00e1 executando? De onde est\u00e1 sendo rodada? Quais os recursos que essas querys consomem? Nesse artigo, demonstrarei como eu fa\u00e7o esse acompanhamento no meu\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"Resultado Query Trace","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/resultadoquerytrace5b65d.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/resultadoquerytrace5b65d.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/resultadoquerytrace5b65d.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/resultadoquerytrace5b65d.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]}],"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}]}}