whats the Difference between sp_updatestats vs UPDATE STATISTICS. I know that sp_updatestats updates the whole database. I read that sp_updatestats is never recommended.
Not quite, although when it finishes I think it says something like "All tables updated". It actually only updates stats for tables where at least one row has changed.
If you do UPDATE STATISTICS, and you do it on ALL tables, even ones for which no changes have been made, then (contrary to "I read that sp_updatestats is never recommended") I reckon that sp_updatestats would be better
Better still, IMHO, would be to interrogates the meta data to see how many rows have changed (either as a percentage of the total rows in the table, or according to some per-table settings that you have defined) and then Update Stats only one ones that need it.
Depends how big your database is, how long UPDATE STATS currently takes to run, and whether you have a 24/7 system where you have no, or only a small, housekeeping window.
On a small database with no other concerns just updating the lot, every night, means that users have tables with STATS in best-possible-condition. Unless the database is READ ONLY
P.S. sp_updatestats uses SAMPLE. We find that FULLSCAN is much more reliable on large tables ... so that could be a reason not to use sp_updatestats
You can control that by using the RESAMPLE option on sp_updatestats. This only works if you can guarantee the stats were not previously updated with a sampling rate.
FULLSCAN wouldn't necessarily be more or less reliable based on table size - it will be based on the distribution of the data. If you know for sure that the data is evenly distributed then a sampling rate will be fine...however, most of the time the distribution is not even - and it becomes much more likely that you get skewed results from a sampling rate.
But...if your table is very large - it could take several hours to update using a full scan and cause lots of performance issues while updating so you may need to suffer the sampling rate - or define a lower cost percentage for that statistic.
Yes, good point. Just have to guard against anyone Updating the Stats on that table with an "undesirable" sampling rate. "I'll just try using FULLSCAN on this monstrous table" without realising that that is permanently changing the way that sp_updatestats will then maintain stats on that table - its a risky strategy, so best to avoid relying on RESAMPLE I think ... for a large, mission critical, system it would be better that Update Stats is under sophisticated DBA control ... but for a small DB its probably fine - may be no tangible difference between SAMPLE and FULLSCAN in a small database. What does maintenance plan do by default? Perhaps to update stats on every index regardless? even if they have just been REINDEX'd?
Ola Hallengren's stuff is well worth exploring for anyone that needs anything other than an MS "Out of the box" solution