Update Statistics actually decreases performance?

Question

I have a nightly sp_updatestats on a production database, which seems to work fine, except for one particular stored proc that experience performance issues after the update.

The dev team is currently working on fixing the proc (some new indexes and some reorganization of the query that I've recommended), but currently the only workaround is to do an

UPDATE STATISTICS [SuperGiantTable]
WITH FULLSCAN

So, the question is, can I force the sp_ updatestats to do the "with fullscan" option? Do I want to? If not, then I'll probably just add this above UPDATE STATISTICS command to run immediately after the sp_updatestats.

SQL 2000, btw.

Answer 1

It really depends...

How many databases?

How big is the database(s)?

How much activity?

sp_updatestats (by default) updates statistics with a default sampling rate. You can try using the resample option, but that will only work if an auto update has not occurred since the last time you updated stats (auto update also uses a default sampling rate).

When you rebuild an index, the statistics are updated with a full scan and most people see good performance after an index rebuild.

Only rare situations people turn off auto stats features. I would recommend leaving it ON.

Have a job that runs at an appropriate interval (which depends on your data modification patterns for each table) and does a manual stats update using full scan or some other high sampling rate.

Generally I would go as follows:

If its a big database -- Transaction log backup hourly.

-- Differential backup daily. -- Daily defrag. -- Daily update stats.

-- Reindex once a week. -- Full backup once a week.

A lot depends on the database and transactions.

Answer 2

As KPWINC said, "It really depends".

There are several more items which influence the answer to your question that a general answer may not be appropriate.

Microsoft MSDN has entries for sp_updatestats (http://msdn.microsoft.com/en-us/library/aa260337(SQL.80).aspx) and "UPDATE STATISTICS" (http://msdn.microsoft.com/en-us/library/aa260645(SQL.80).aspx)

To answer your last questions first:

You need to re-read the SQLServerPedia entry. Specifically:

Your index rebuild will automatically update the statistics for the index (with 100% sample size which is usually better then what you end up with when using sp_updatestats). After the index rebuild is complete you can use sp_updatestats to update the other statistics that require attention (2005+).

Looking at the UPDATE STATISTICS (2000) documentation says the following for sampling:

Note The default behavior is to perform a sample scan on the target table or indexed view. SQL Server automatically computes the required sample size.

Kimberly Tripp has put together a few blog entries on statistics, indexes, and database maintenance plans (http://www.sqlskills.com/BLOGS/KIMBERLY/category/Statistics.aspx). While the information may or may not be directly usable by you, the guidance of each blog entry should be and the referenced sites will probably be helpful.

Answer 3

One more thing to bear in mind is that the automatically-computed sample rate may cause the generated histogram and density vector to not properly account for any data skew you might have, which the sproc happens to operating against.

Do you know the difference in query plans between the sproc after a sampled stats update and a fullscan stats update?

One other thing - be careful of maintenance plans that rebuild indexes and then update those same statistics that were updated as a side-effect of the rebuild - you can end up with WORSE statistics then if you'd just left the rebuild-updated-stats alone.

Thanks