Re: More Update Statistics

Answers to my questions from John Miller III if anyone needs this information in the future. "Andrew Ford" <andrewford@austin.rr.com> wrote in message news:sZOre.55857$6g3.51506@tornado.texas.rr.com... > Ok. I think all but 2 of my questions relating to update statistics have > been answered. If anyone knows the answers to the following 2 questions, > I'd appreciate some feedback. > > 1. Do the update statistics performance enhancements appear in 9.30.XC2 or > 9.30.XC3? I have 2 documents written by John Miller III, one says 9.30.XC2 > and the other says 9.30.XC3. From what I can see from my own tests, the > enhancements are NOT in 9.30.XC2. In general 9.30.UC3 and 7.31.UD2 and above. There where some port of 9.30.?C2X? in which the X? means a patch ported that included these changes. > > 2. If I run 'update statistics high for <table> (<columns>)' (notice I've > omitted the 'distributions only' syntax you would expect to see here), I > know I will generate statistics (as if I ran 'update statistics low for > <table> (<columns>)') and I know I will generate distributions. But will I > ALSO send the logically deleted index pages to the btree cleaner like I > would when running 'update statistics low'? Yes, any time update statistics walks the index either high/med or low it will send information to the btree cleaner or btree scanner. > > Thanks, > > Andrew > > Based on this information, I would suggest the following guidelines when running update statistics for engines 9.30.xC3 and up and 7.31.xD2 and up. update statistics low for <table> (<columns>); -- where <columns> are all columns that are in an index update statistics medium for <table> (<columns>) distributions only; -- where <columns> are all columns of a table that do not appear -- in the next update statistics high statement update statistics high for <table> (<columns>) distributions only; -- where <columns> are columns that head indexes or columns that are -- the first differing column of composite indexes that start with the same -- subset of columns I had the hair brained idea that I could lose the 'distributions only' syntax from my update statistics high and remove the columns from update statistics low that appear in the update statistics high and save some time. Turns out that this will not save time and could cause me to miss some statistics for indexes. So, forget about all that. Thanks to everyone for helping me out on this one. I didn't really find the update statistics Holy Grail but I know a lot more today than I did 2 weeksago. Andrew