People are often overly paranoid about running UPDATE STATISTICS, running it too often, or running them at too high a level, or, worst of all, running the wrong stats.
So, to keep it simple, let's start with what is a bare minimum:
Do an UPDATE STATISTICS LOW on your entire database every three months.
Are you still experiencing any unduly slow queries? Are the underlying tables very active? Perhaps try UPDATE STATISTICS LOW on those tables monthly, or even weekly. If you find that UPDATE STATISTICS fixes things for a while, but then performance tails off, you probably just need to UPDATE STATISTICS more often. However, more than once a week is probably an indication of a different problem.
So, assuming you still have query problems, it's time to start taking time to look into other levels of statistics. However, do not assume that UPDATE STATISTICS MEDIUM is necessarily any better than UPDATE STATISTICS LOW. I've seen more pain from this assumption than I care to remember.
Identify the queries which are still running badly (onstat -g sql), identify the tables that they query (you will almost certainly find one or two tables are common to most of them) and fix the statistics on these.
And now it's time for me to slip into lazy Delia mode: in here is a program called dostats.ec, written by a very clever man called Art Kagel, who has saved us all a lot of trouble -- compile it and run it against the previously identified tables. The temptation may be there to run it against the entire database, but don't succumb, because in many cases it will just be wasted disk space and wasted cycles.
Think of your carbon footprint!
Just kidding! But seriously, don't overdo your stats, OK?
1 comment:
I have found the efficacy/requirement for UPDATE STATISTICS can vary greatly depending on the IDS version. At IDS 7, an update stats low was quite sufficient (admittedly the app was very specific with the select statements and the indexes followed the where clauses column for column !!). At IDS 9 update stats low for the database followed by medium for the leading column of each index sufficed. At IDS 10 it was absolutely vital to update stats EXACTLY as per the performance guide. Not gone to IDS 11 yet. Just my personnal experiance.
Post a Comment