{"id":920,"date":"2011-07-02T16:34:37","date_gmt":"2011-07-02T18:34:37","guid":{"rendered":"http:\/\/fabriciolima.net\/blog\/?p=920"},"modified":"2016-07-23T23:22:36","modified_gmt":"2016-07-24T02:22:36","slug":"routine-to-update-database-statistics","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2011\/07\/02\/routine-to-update-database-statistics\/","title":{"rendered":"Routine to Update Database Statistics"},"content":{"rendered":"

Hi Folks,<\/p>\n

First off all, sorry for my English. I hope you understand me.<\/p>\n

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.<\/p>\n

However, with this option enabled if our table has more than 500 rows (almost our table have), the statistics in this table will be updated only when we have “500 + 20% of the table size”<\/em> of changes in the table. The worst is that can happen in a wrong time and create more cost to your production environment.<\/p>\n

In my Job, I have a table that is widely used and it has 70 million of rows, If I wait for the statistics to be updated automatically (ignoring the index statistics that are updated with a REBUILD for example), my statistics would be updated when I reach “500 + 20% * 70,000,000 = 14,000,500”<\/em> of changes. In my case, it would take a very long time and querys with bad plans could be generated.<\/p>\n

So, with the objective to minimize this problems, I decided to update my statistics when I reach 0.5% of changes on my table (and not 20% as is the default). I set this value because the UPDATE STATISTICS WITH FULLSCAN operation is very expensive and I don’t have a window to update all my statistics daily. Then, my table will be an update statistics when I get “70,000,000 * 0,005 = 350,000<\/em>” of changes.<\/p>\n

As I already have a window to indexes maintenance and dozens of routines running at dawn, I get in the VPN at 22:40 and I ran an UPDATE STATISTICS at this time to analyze the impact it would have in my database that is 7 x 24. So, I decided to use a window 22:40 to 23:50 to perform this procedure.<\/p>\n

In each database that I want to run the update statistics, I created the procedure below. Then I did a job that runs daily at 22:40 with one step for each database.<\/p>\n

CREATE PROCEDURE [dbo].[stpUpdate_Stats]\nAs
\nBEGIN<\/p>\n

SET NOCOUNT ON<\/p>\n

— To go out the routine when the window finish
\nIF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))– Time > 23:50
\nRETURN<\/p>\n

Create table #Update_Stats(
\nId_Stats int identity(1,1),
\nDs_Comand varchar(4000),
\nNr_Rows int)<\/p>\n

;WITH Size_Tables AS (
\nSELECT obj.name, prt.rows
\nFROM sys.objects obj
\nJOIN sys.indexes idx on obj.object_id= idx.object_id
\nJOIN sys.partitions prt on obj.object_id= prt.object_id
\nJOIN sys.allocation_units alloc on alloc.container_id= prt.partition_id
\nWHERE obj.type= ‘U’ AND idx.index_id IN (0, 1)and prt.rows> 1000
\nGROUP BY obj.name, prt.rows)<\/p>\n

insert into #Update_Stats(Ds_Comand,Nr_Rows)
\nSELECT ‘UPDATE STATISTICS ‘ + B.name+ ‘ ‘ + A.name+ ‘ WITH FULLSCAN’, D.rows
\nFROM sys.stats A
\njoin sys.sysobjects B on A.object_id = B.id
\njoin sys.sysindexes C on C.id = B.id and A.name= C.Name
\nJOIN Size_Tables D on\u00a0 B.name= D.Name
\nWHERE\u00a0 C.rowmodctr > 100
\nand C.rowmodctr> D.rows*.005
\nand substring( B.name,1,3) not in (‘sys’,’dtp’)
\nORDER BY D.rows<\/p>\n

declare @Loop int, @Comand nvarchar(4000)
\nset @Loop = 1<\/p>\n

while exists(select top 1 null from #Update_Stats)
\nbegin
\nIF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))– Time > 23:50
\nBEGIN
\nBREAK — To go out the routine when the window finish
\nEND<\/p>\n

select @Comand = Ds_Comand
\nfrom #Update_Stats
\nwhere Id_Stats = @Loop<\/p>\n

EXECUTE sp_executesql @Comand<\/p>\n

delete from #Update_Stats
\nwhere Id_Stats = @Loop<\/p>\n

set @Loop= @Loop + 1
\nend
\nEND<\/p>\n

If someone has another suggestions for an update statistics routine, please feel free to leave a comment.<\/p>\n

Remember, if you have a large window, you can update all statistics daily and the Query Optimizer will thank you a lot.<\/p>\n

Enjoy,<\/p>\n

Fabr\u00edcio Fran\u00e7a Lima<\/p>\n

MCITP \u2013 Database Administrator<\/p>\n","protected":false},"excerpt":{"rendered":"

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. However, with this option enabled […]<\/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,33],"tags":[350,362,1094,1093,63,351,357,1092,1095,1091,1090,49,33,358,363,155,359,354,361,355,40,352,353],"class_list":["post-920","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-sql-server","tag-desempenho","tag-es","tag-help-update-statistics","tag-how-to-update-statistics","tag-performance","tag-query-optimizer","tag-rowmodctr","tag-script-sql-server","tag-script-sql-server-statistics","tag-script-statistics","tag-script-update-statistics","tag-sql","tag-sql-server","tag-sys-allocation_units-alloc","tag-sys-inde","tag-sys-objects","tag-sys-partitions","tag-sys-stats","tag-sys-sysinde","tag-sys-sysobjects","tag-tuning","tag-update-statistics","tag-update-statistics-with-fullscan"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":910,"url":"https:\/\/fabriciolima.net\/blog\/2011\/06\/29\/rotina-para-atualizar-as-estatisticas-do-seu-banco-de-dados\/","url_meta":{"origin":920,"position":0},"title":"Rotina para Atualizar as Estat\u00edsticas do seu Banco de Dados","author":"Fabr\u00edcio Lima","date":"29 de junho de 2011","format":false,"excerpt":"Rotina para Atualizar as Estat\u00edsticas do seu Banco de Dados","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":7342,"url":"https:\/\/fabriciolima.net\/blog\/2017\/02\/08\/improve-the-query-performance-with-like-string-changing-only-the-collation\/","url_meta":{"origin":920,"position":1},"title":"Improve the performance of a query that uses ” like ‘%String%’ ” changing only the collation","author":"Fabr\u00edcio Lima","date":"8 de fevereiro de 2017","format":false,"excerpt":"Hi Folks, In this blog post, I will give you a very useful tip to improve the performance of a query that uses \" like '%String%' \". How often do you have to use a query like this? Select Columns,... from Table where Name like '%String%' Reading the great book\u2026","rel":"","context":"Em "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2017\/02\/PostCollationTable.png?resize=350%2C200&ssl=1","width":350,"height":200},"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":920,"position":2},"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":7833,"url":"https:\/\/fabriciolima.net\/blog\/2017\/05\/23\/migrando-um-sql-server-2008-totvs-protheus-para-o-sql-server-2016-standard\/","url_meta":{"origin":920,"position":3},"title":"Migrando um SQL Server 2008 Totvs Protheus para o SQL Server 2016 Standard","author":"Fabr\u00edcio Lima","date":"23 de maio de 2017","format":false,"excerpt":"Fala Pessoal, Hoje vou compartilhar com voc\u00eas sobre uma migra\u00e7\u00e3o que fiz em um cliente no in\u00edcio do m\u00eas. Cen\u00e1rio Antigo: Windows Server 2008 SQL Server 2008 R2 Standard Servidor \u00fanico (migra\u00e7\u00e3o para o mesmo Hardware) Novo Cen\u00e1rio: Windows Server 2012 R2 SQL Server 2016 Standard Edition Como n\u00e3o t\u00ednhamos\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\/05\/sql2016Totvs.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":19561,"url":"https:\/\/fabriciolima.net\/blog\/2024\/05\/31\/azure-sql-copilot-02-qual-query-o-copilot-usa-para-validar-a-performance-do-bd\/","url_meta":{"origin":920,"position":4},"title":"Azure SQL Copilot (#02) – Qual query o Copilot usa para validar a performance do BD?","author":"Fabr\u00edcio Lima","date":"31 de maio de 2024","format":false,"excerpt":"Fala Pessoal, Seguindo nos testes e posts sobre Copilot para SQL no Azure, hoje vou mostrar a query que ele usa para realizar a valida\u00e7\u00e3o de performance do ambiente. Ao perguntar ao Copilot como voc\u00ea pode melhorar a performance do ambiente, assim como no post anterior, ele responde como est\u00e1\u2026","rel":"","context":"Em "Copilot"","block_context":{"text":"Copilot","link":"https:\/\/fabriciolima.net\/blog\/category\/copilot\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2024\/05\/mensagem-validacao-copilot-300x172.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":7756,"url":"https:\/\/fabriciolima.net\/blog\/2017\/04\/25\/alerta-monitorando-processos-bloqueados-no-sql-server\/","url_meta":{"origin":920,"position":5},"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":[]}],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/920","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=920"}],"version-history":[{"count":0,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/920\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=920"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=920"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=920"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}