Re: When to update statistics

> > > Does anyone have a effective method of knowing when to update statistics > on a database? I know if queries are running really slow, to run it and > usually there is a increase in the performance, but does any one know > where and what to look at to figure out if update statistics needs to the > run? > We update statistics on our heavily changed tables as often as possible, and the whole database, at least once a week. When the nrows column of systables is very different than the actual number of rows in a table (isql, table, info, status will show the real number of rows, as will a count (*), it is time to update statistics. Unfortunately, I believe updating statistics currently locks the table, so it is not a wise idea to do this during production hours. It gets trickier when you run a 24 hour shop. The optimizer makes decisions based on its statistics. If this information is very wrong, it could cause a huge performance problem. Consider the example where the optimizer thought there were only 10 rows in a table, when in fact, there was a million. It is possible a sequential scan would be picked. Statistics also keep track of the lowest and highest value for an index. If this information has changed radically, results could be a real performance pig. -- Naomi Walker (aka N7FSA) | naomi@anasazi.com | Phoenix, Arizona Everything should be made as simple | as possible, but not simpler --Einstein | Visualize Whirled Peas