Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
2  
Kimberly is the man for statistics (which sounds kind of weird I know, but I'm her husband and I can say that :-) –  Paul Randal Jun 4 '09 at 3:27
add comment

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.

share|improve this answer
    
That's my primary problem: what is a "default sampling rate", and why does it change the behavior of the "resample" option? And if rebuilding the index updates that statistics automatically, why does this article (sqlserverpedia.com/blog/sql-server-bloggers/…) recommend updating stats AFTER you rebuild the index? –  BradC Jun 3 '09 at 21:58
add comment

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

share|improve this answer
    
The difference before vs after had to do with the use of a (not-very-selective) index inside a view called by the proc. This morning they deployed a fix I had recommended, reorganizing the proc to not use the view at all. Previously, each call to this proc cost 380,000 reads in the best case, now its down to 250 to 800 reads. The bad news is they now don't see the need to deploy the covering index I recommended that should improve it even further! Eh, I guess 4 orders of magnitude improvement isn't too bad :) –  BradC Jun 4 '09 at 18:42
    
Very cool - thanks! –  Paul Randal Jun 4 '09 at 19:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.