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 SET NOCOUNT ON<\/p>\n — To go out the routine when the window finish Create table #Update_Stats( ;WITH Size_Tables AS ( insert into #Update_Stats(Ds_Comand,Nr_Rows) declare @Loop int, @Comand nvarchar(4000) while exists(select top 1 null from #Update_Stats) select @Comand = Ds_Comand EXECUTE sp_executesql @Comand<\/p>\n delete from #Update_Stats set @Loop= @Loop + 1 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":""},"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"],"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}]}}
\nBEGIN<\/p>\n
\nIF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))– Time > 23:50
\nRETURN<\/p>\n
\nId_Stats int identity(1,1),
\nDs_Comand varchar(4000),
\nNr_Rows int)<\/p>\n
\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
\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
\nset @Loop = 1<\/p>\n
\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
\nfrom #Update_Stats
\nwhere Id_Stats = @Loop<\/p>\n
\nwhere Id_Stats = @Loop<\/p>\n
\nend
\nEND<\/p>\n