{"id":910,"date":"2011-06-29T23:11:57","date_gmt":"2011-06-30T01:11:57","guid":{"rendered":"http:\/\/fabriciolima.net\/blog\/?p=910"},"modified":"2017-12-21T09:32:34","modified_gmt":"2017-12-21T11:32:34","slug":"rotina-para-atualizar-as-estatisticas-do-seu-banco-de-dados","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2011\/06\/29\/rotina-para-atualizar-as-estatisticas-do-seu-banco-de-dados\/","title":{"rendered":"Rotina para Atualizar as Estat\u00edsticas do seu Banco de Dados"},"content":{"rendered":"

Fala Pessoal,<\/p>\n

N\u00f3s sempre ouvimos que as estat\u00edsticas de um Banco de Dados SQL Server devem estar sempre atualizadas para que o Query Optimizer possa gerar o melhor plano de execu\u00e7\u00e3o para nossas consultas. Para isso, devemos habilitar a op\u00e7\u00e3o ‘Auto Update Statistics’<\/strong> nas nossas databases.<\/p>\n

Entretanto, com essa op\u00e7\u00e3o habilitada, se nossa tabela tiver mais de 500 registros (praticamente todas), as estat\u00edsticas dessa tabela s\u00f3 ser\u00e3o atualizadas quando tivermos (500 + 20% do tamanho da tabela) de altera\u00e7\u00f5es na tabela. O pior \u00e9 que isso pode acontecer no meio do dia gerando um custo no seu ambiente de produ\u00e7\u00e3o.<\/p>\n

No meu ambiente, tenho uma tabela que \u00e9 muito utilizada e a mesma possui 70 milh\u00f5es de registros. Se eu fosse esperar as estat\u00edsticas serem atualizadas automaticamente, desconsiderando as estat\u00edsticas de \u00edndices que s\u00e3o atualizadas por exemplo com um REBUILD, minhas estat\u00edsticas seriam atualizadas quando eu tivesse (500 + 20%*70.000.000 = 14.000.500) de altera\u00e7\u00f5es. No meu caso, isso iria demorar um tempo muito grande e querys com planos n\u00e3o \u00f3timos poderiam ser geradas.<\/p>\n

Para tentar minimizar esse problema, resolvi eu mesmo atualizar minhas estat\u00edsticas quando tivesse 0,5% de altera\u00e7\u00f5es na minha tabela (e n\u00e3o 20% como \u00e9 o default). Defini esse valor pois a opera\u00e7\u00e3o de UPDATE STATISTICS WITH FULLSCAN \u00e9 muito custosa e eu n\u00e3o tenho uma janela para atualizar todas as estat\u00edsticas diariamente. Com isso, essa minha tabela teria as estat\u00edsticas atualizadas quando tivesse (70.000.000 * 0.005 = 350.000) altera\u00e7\u00f5es.<\/p>\n

Como de madrugada j\u00e1 tenho janelas de manuten\u00e7\u00e3o de \u00edndices e dezenas de rotinas rodando, fiquei na VPN at\u00e9 \u00e0s 22:40 e rodei um UPDATE STATISTICS nesse hor\u00e1rio para analisar o impacto que teria no meu Banco de Dados que \u00e9 7×24. Ent\u00e3o, defini minha janela de 22:40 \u00e0s 23:50 para executar esse procedimento.<\/p>\n

Em cada database que quero executar a atualiza\u00e7\u00e3o das estat\u00edsticas, criei a procedure abaixo. Em seguida fiz um job que roda diariamente \u00e0s 22:40 com um step para cada database.<\/p>\n

CREATE PROCEDURE [dbo].[stpAtualiza_Estatisticas]\r\nAs\r\nBEGIN\r\n\r\nSET NOCOUNT ON\r\n-- Sai da rotina quando a janela de manuten\u00e7\u00e3o \u00e9 finalizada\r\nIF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))-- hora > 23:50\r\nBEGIN\r\nRETURN\r\nEND\r\n\r\nCreate table #Atualiza_Estatisticas(\r\nId_Estatistica int identity(1,1),\r\nDs_Comando varchar(4000),\r\nNr_Linha int)\r\n\r\n;WITH Tamanho_Tabelas AS (\r\nSELECT obj.name, prt.rows\r\nFROM sys.objects obj\r\nJOIN sys.indexes idx on obj.object_id= idx.object_id\r\nJOIN sys.partitions prt on obj.object_id= prt.object_id\r\nJOIN sys.allocation_units alloc on alloc.container_id= prt.partition_id\r\nWHERE obj.type= 'U' AND idx.index_id IN (0, 1)and prt.rows> 1000\r\nGROUP BY obj.name, prt.rows)\r\n\r\ninsert into #Atualiza_Estatisticas(Ds_Comando,Nr_Linha)\r\nSELECT 'UPDATE STATISTICS ' + B.name+ ' ' + A.name+ ' WITH FULLSCAN', D.rows\r\nFROM sys.stats A\r\njoin sys.sysobjects B on A.object_id = B.id\r\njoin sys.sysindexes C on C.id = B.id and A.name= C.Name\r\nJOIN Tamanho_Tabelas D on\u00a0 B.name= D.Name\r\nWHERE\u00a0 C.rowmodctr > 100\r\nand C.rowmodctr> D.rows*.005\r\nand substring( B.name,1,3) not in ('sys','dtp')\r\nORDER BY D.rows\r\n\r\ndeclare @Loop int, @Comando nvarchar(4000)\r\nset @Loop = 1\r\n\r\nwhile exists(select top 1 null from #Atualiza_Estatisticas)\r\nbegin\r\n\r\nIF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))-- hora > 23:50 am\r\nBEGIN\r\nBREAK -- Sai do loop quando acabar a janela de manuten\u00e7\u00e3o\r\nEND\r\n\r\nselect @Comando = Ds_Comando\r\nfrom #Atualiza_Estatisticas\r\nwhere Id_Estatistica = @Loop\r\n\r\nEXECUTE sp_executesql @Comando\r\n\r\ndelete from #Atualiza_Estatisticas\r\nwhere Id_Estatistica = @Loop\r\n\r\nset @Loop= @Loop + 1\r\nend\r\nEND<\/pre>\n
Caso algu\u00e9m tenha alguma outra sugest\u00e3o para uma rotina de atualiza\u00e7\u00e3o de estat\u00edstica, pode deixar um coment\u00e1rio.<\/p>\n
Lembre-se, se voc\u00ea tem uma janela grande, voc\u00ea pode atualizar todas as estat\u00edsticas diariamente que o Query Optimizer ir\u00e1 agradecer.<\/p>\n
 <\/p>\n
Gostou dessa Dica?<\/b><\/p>\n
Curta, comente, compartilhe\u2026<\/p>\n
Assine meu canal no Youtube<\/a> e curta minha p\u00e1gina no Facebook<\/a> para receber Dicas de Leituras, V\u00eddeos e Eventos sobre SQL Server.<\/p>\n
Aprenda a criar essa e outras rotinas para administrar melhor seu SQL Server no meu Treinamento de Tarefas do Dia a Dia de um DBA<\/a>.<\/p>\n
At\u00e9 a pr\u00f3xima.<\/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":"
Rotina para Atualizar as Estat\u00edsticas do seu Banco de Dados<\/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":[350,63,351,357,49,33,358,360,155,359,354,356,355,40,352,353],"class_list":["post-910","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-virtual-pass-br","tag-desempenho","tag-performance","tag-query-optimizer","tag-rowmodctr","tag-sql","tag-sql-server","tag-sys-allocation_units-alloc","tag-sys-indexes","tag-sys-objects","tag-sys-partitions","tag-sys-stats","tag-sys-sysindexes","tag-sys-sysobjects","tag-tuning","tag-update-statistics","tag-update-statistics-with-fullscan"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":920,"url":"https:\/\/fabriciolima.net\/blog\/2011\/07\/02\/routine-to-update-database-statistics\/","url_meta":{"origin":910,"position":0},"title":"Routine to Update Database Statistics","author":"Fabr\u00edcio Lima","date":"2 de julho de 2011","format":false,"excerpt":"Hi Folks, First off all, sorry for my English. I hope you understand me. We always heard that the database statistics must be constantly updated to the Query Optimizer generate the best execution plan for our querys. For this, we should enable the 'Auto Update Statistics' option in our databases.\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":2575,"url":"https:\/\/fabriciolima.net\/blog\/2013\/12\/14\/5-motivos-para-quem-utiliza-o-protheus-totvs-contratar-um-dba-sql-server\/","url_meta":{"origin":910,"position":1},"title":"5 motivos para quem utiliza o Protheus (Totvs) contratar um DBA SQL Server","author":"Fabr\u00edcio Lima","date":"14 de dezembro de 2013","format":false,"excerpt":"Ol\u00e1 Pessoal, Trabalho com administra\u00e7\u00e3o de banco de dados SQL Server Protheus desde 2009 em um ambiente que considero de grande porte: Base de Dados de 300 GB completamente compactada \u00e0 n\u00edvel de p\u00e1gina, caso contr\u00e1rio j\u00e1 estaria passando de 1 TB de dados. (update em 28\/09\/2015 - 500 GB\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\/2013\/12\/ConstraintProtheus.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2013\/12\/ConstraintProtheus.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2013\/12\/ConstraintProtheus.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2013\/12\/ConstraintProtheus.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":10214,"url":"https:\/\/fabriciolima.net\/blog\/2019\/02\/13\/query-store-3-lock-grande-ao-tentar-limpar-o-query-store\/","url_meta":{"origin":910,"position":2},"title":"Query Store (#03) – Lock grande ao tentar limpar o Query Store","author":"Fabr\u00edcio Lima","date":"13 de fevereiro de 2019","format":false,"excerpt":"Fala Pessoal, Continuando a s\u00e9rie sobre o Query Store, antes de entrar nos detalhes de como ele pode nos ajudar em nosso dia a dia, vou mostrar alguns problemas que tive em alguns cen\u00e1rios para que possam ter conhecimento antes de habilitarem a feature em 100% dos ambientes que administram.\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\/fabriciolima.net\/blog\/wp-content\/uploads\/2019\/02\/Lock_clear_query_Store.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2019\/02\/Lock_clear_query_Store.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2019\/02\/Lock_clear_query_Store.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2019\/02\/Lock_clear_query_Store.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2019\/02\/Lock_clear_query_Store.png?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2019\/02\/Lock_clear_query_Store.png?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":9365,"url":"https:\/\/fabriciolima.net\/blog\/2018\/09\/11\/queries-do-dia-a-dia-como-monitorar-o-percentual-de-execucao-na-criacao-ou-rebuild-de-um-indice\/","url_meta":{"origin":910,"position":3},"title":"Queries do Dia a Dia – Como Monitorar o Percentual de Execu\u00e7\u00e3o na Cria\u00e7\u00e3o ou Rebuild de um \u00cdndice?","author":"Fabr\u00edcio Lima","date":"11 de setembro de 2018","format":false,"excerpt":"Fala Pessoal, Quantas vezes voc\u00eas j\u00e1 criaram um \u00edndice em produ\u00e7\u00e3o e ficaram torcendo, apertando F5 toda hora, rezando, fazendo promessa para essa cria\u00e7\u00e3o acabar mais r\u00e1pido e resolver seu problema cr\u00edtico? Ficamos naquela d\u00favida, ser\u00e1 que cancelo ou n\u00e3o? Ser\u00e1 que j\u00e1 est\u00e1 acabando ou ainda vai demorar muito\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\/fabriciolima.net\/blog\/wp-content\/uploads\/2018\/09\/MonitorandoRebuildIndiceClustered.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2018\/09\/MonitorandoRebuildIndiceClustered.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2018\/09\/MonitorandoRebuildIndiceClustered.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2018\/09\/MonitorandoRebuildIndiceClustered.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":7756,"url":"https:\/\/fabriciolima.net\/blog\/2017\/04\/25\/alerta-monitorando-processos-bloqueados-no-sql-server\/","url_meta":{"origin":910,"position":4},"title":"Alerta: Monitorando Processos Bloqueados no SQL Server","author":"Fabr\u00edcio Lima","date":"25 de abril de 2017","format":false,"excerpt":"Ol\u00e1 Pessoal, Hoje vou compartilhar com voc\u00eas o script de mais um alerta para nos ajudar na Administra\u00e7\u00e3o do nosso SQL Server. Esse alerta \u00e9 muito \u00fatil no dia a dia. Ele vai monitorar os famosos \"Locks\" que acontecem no nosso banco\u00a0de dados.\u00a0Caso aconte\u00e7a algum Lock com um\u00a0processo que esteja\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\/2017\/04\/Processo_Bloqueado_Alerta.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2017\/04\/Processo_Bloqueado_Alerta.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2017\/04\/Processo_Bloqueado_Alerta.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2017\/04\/Processo_Bloqueado_Alerta.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2017\/04\/Processo_Bloqueado_Alerta.png?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2017\/04\/Processo_Bloqueado_Alerta.png?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":2313,"url":"https:\/\/fabriciolima.net\/blog\/2013\/08\/26\/casos-do-dia-a-dia-trigger-para-descobrir-como-um-registro-e-inserido-alterado-e-deletado\/","url_meta":{"origin":910,"position":5},"title":"Casos do Dia a Dia – Trigger para descobrir como um registro \u00e9 inserido, alterado e deletado","author":"Fabr\u00edcio Lima","date":"26 de agosto de 2013","format":false,"excerpt":"Fala Pessoal, Ap\u00f3s um longo tempo, segue mais um post com um caso do dia a dia que aconteceu comigo. Onde trabalho, estavam tentando descobrir o que estava alterando dados de uma tabela e como isso estava sendo feito. Uma das formas de fazer isso era criando um trace, contudo,\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\/2013\/08\/ResultadoTrigger.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2013\/08\/ResultadoTrigger.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2013\/08\/ResultadoTrigger.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2013\/08\/ResultadoTrigger.png?resize=700%2C400&ssl=1 2x"},"classes":[]}],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/910","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=910"}],"version-history":[{"count":3,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/910\/revisions"}],"predecessor-version":[{"id":8553,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/910\/revisions\/8553"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=910"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=910"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=910"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}