Sql-server – Update Statistics actually decreases performance

indexessql server

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.

Best Answer

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.

Related Topic