Re: Update Statistics

I'm not sure I agree 100% with running update stats medium on the entire table. Sure, there is no performance penalty with regards to the update stats run times but the optimizer will be using less accurate medium statistics for heads of indexes to generate query plans until update stats high for those columns completes successfully. This may not be a problem for engines that do light processing during maintenance windows, but for 24/7 engines this *could* be a problem. Other than the extra human work needed to generate a column list for update stats medium, are there any downsides to running update statistics in the following way for newer versions of IDS that have the new update stats performance improvements (9.3xC2 and up)? update statistics low for <tablename (<column list>); # for every column inan index update statistics medium for <tablename> (<column list>) distributions only;# for every column not in the update stats high update statistics high for <tablename> (<column list>) distributions only;# for every column that heads an index and the first differing columns of composite indexes that begin with the same subset of columns Also, the order in which update statistics is run should no longer matter since we are not overwriting anything. Andrew "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:42A8A5CD.3000204@bloomberg.net... > Andrew Ford wrote: > <SNIP> > > > Am I wrong in thinking that to update statistics against a table you only > > need 3 update statistics statements. > > > > low - every column that appears in an index > > high - every column that heads an index and every column in a composite > > index that is the first column that differs when multiple indexes have the > > same columns at the head of the index (i.e. (a, b, c, d) and (a, b, c, e) > > run high for d and e) > > medium - every column not included in the high stats > <SNIP> > > Specific comments: > > medium - The medium should be done first and it can be done on all columns. > Because of the sampling algorithms used it's just as fast to do all > columns as to do the subset that's not heading indexes, so dostats just does > a MEDIUM on all columns first then overwrites the medium stats with high > stats where needed. > > low - Again, yes for stats quality, no for efficiency if you are running an > older release of IDS. > > high - Same comments as for low. On newer IDSs do it all together, on older > ones, break it up. > > Art S. Kagel

Or... update statistics low for table <table> (<columns>); # for every column in an index that doesn't appear in the high run update statistics medium for table <table> (<columns>) distributions only; # for every column of a table not in the update stats high update statistics high for <tablename> (<column list>); # for every column that heads an index and the first differing columns of composite indexes that begin with the same subset of columns moved some colums from the low run to the high run and removed the distributions only clause of the high run. Andrew "Andrew Ford" <andrewford@austin.rr.com> wrote in message news:_vgre.43956$PR6.7994@tornado.texas.rr.com... > I'm not sure I agree 100% with running update stats medium on the entire > table. > > Sure, there is no performance penalty with regards to the update stats run > times but the optimizer will be using less accurate medium statistics for > heads of indexes to generate query plans until update stats high for those > columns completes successfully. This may not be a problem for engines that > do light processing during maintenance windows, but for 24/7 engines this > *could* be a problem. > > Other than the extra human work needed to generate a column list for update > stats medium, are there any downsides to running update statistics in the > following way for newer versions of IDS that have the new update stats > performance improvements (9.3xC2 and up)? > > update statistics low for <tablename (<column list>); # for every column in> an index > update statistics medium for <tablename> (<column list>) distributionsonly; > # for every column not in the update stats high > update statistics high for <tablename> (<column list>) distributions only;> # for every column that heads an index and the first differing columns of > composite indexes that begin with the same subset of columns > > Also, the order in which update statistics is run should no longer matter > since we are not overwriting anything. > > Andrew > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:42A8A5CD.3000204@bloomberg.net... > > Andrew Ford wrote: > > <SNIP> > > > > > Am I wrong in thinking that to update statistics against a table you > only > > > need 3 update statistics statements. > > > > > > low - every column that appears in an index > > > high - every column that heads an index and every column in a composite > > > index that is the first column that differs when multiple indexes have > the > > > same columns at the head of the index (i.e. (a, b, c, d) and (a, b, c, > e) > > > run high for d and e) > > > medium - every column not included in the high stats > > <SNIP> > > > > Specific comments: > > > > medium - The medium should be done first and it can be done on all > columns. > > Because of the sampling algorithms used it's just as fast to do all > > columns as to do the subset that's not heading indexes, so dostats just > does > > a MEDIUM on all columns first then overwrites the medium stats with high > > stats where needed. > > > > low - Again, yes for stats quality, no for efficiency if you are running > an > > older release of IDS. > > > > high - Same comments as for low. On newer IDSs do it all together, on > older > > ones, break it up. > > > > Art S. Kagel > >

Also, if the order no longer matters, can the three update statistics be run in parallel? And if so, does anyone think this will hurt/help/have no effect on performance? Thanks, Andrew "Andrew Ford" <andrewford@austin.rr.com> wrote in message news:_vgre.43956$PR6.7994@tornado.texas.rr.com... > I'm not sure I agree 100% with running update stats medium on the entire > table. > > Sure, there is no performance penalty with regards to the update stats run > times but the optimizer will be using less accurate medium statistics for > heads of indexes to generate query plans until update stats high for those > columns completes successfully. This may not be a problem for engines that > do light processing during maintenance windows, but for 24/7 engines this > *could* be a problem. > > Other than the extra human work needed to generate a column list for update > stats medium, are there any downsides to running update statistics in the > following way for newer versions of IDS that have the new update stats > performance improvements (9.3xC2 and up)? > > update statistics low for <tablename (<column list>); # for every column in> an index > update statistics medium for <tablename> (<column list>) distributionsonly; > # for every column not in the update stats high > update statistics high for <tablename> (<column list>) distributions only;> # for every column that heads an index and the first differing columns of > composite indexes that begin with the same subset of columns > > Also, the order in which update statistics is run should no longer matter > since we are not overwriting anything. > > Andrew > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:42A8A5CD.3000204@bloomberg.net... > > Andrew Ford wrote: > > <SNIP> > > > > > Am I wrong in thinking that to update statistics against a table you > only > > > need 3 update statistics statements. > > > > > > low - every column that appears in an index > > > high - every column that heads an index and every column in a composite > > > index that is the first column that differs when multiple indexes have > the > > > same columns at the head of the index (i.e. (a, b, c, d) and (a, b, c, > e) > > > run high for d and e) > > > medium - every column not included in the high stats > > <SNIP> > > > > Specific comments: > > > > medium - The medium should be done first and it can be done on all > columns. > > Because of the sampling algorithms used it's just as fast to do all > > columns as to do the subset that's not heading indexes, so dostats just > does > > a MEDIUM on all columns first then overwrites the medium stats with high > > stats where needed. > > > > low - Again, yes for stats quality, no for efficiency if you are running > an > > older release of IDS. > > > > high - Same comments as for low. On newer IDSs do it all together, on > older > > ones, break it up. > > > > Art S. Kagel > >