What does exec sp_updatestats do?

Question

What is the use of sp_updatestats? Can I run that in the production environment for performance improvement?

Mehalek

Answer 1

sp_updatestats updates all statistics for all tables in the database, where even a single row has changed. It does it using the default sample, meaning it doesn't scan all rows in the table so it will likely produce less accurate statistics than the alternatives.

If you have a maintenance plan with 'rebuild indexes' included, it will also refresh statistics, but more accurate because it scans all rows. No need to rebuild stats after rebuilding indexes.

Manually updating particular statistics object or a table with update statistics command gives you much better control over the process. For automating it, take a look here.

Auto-update fires only when optimizer decides it has to. There was a change in math for 2012: in <2012, auto update was fired for every 500 + 20% change in table rows; in 2012+ it is SQRT(1000 * Table rows). It means it is more frequent on large tables. Temporary tables behave differently, of course.

To conclude, sp_updatestats could actually do more damage than good, and is the least recommendable option.

Dowski

@Dowski I'm interested as to your concern about the consequences of this? "could actually do more damage that good, and is the least recommendable option." - It seems the only problem is that it might do things your maintenance plans already do, or be inefficient, but how big of an issue are those if the alternative is a SQL server instance that's unresponsive? – Avantgarde

Answer 2

This is true if you replace full-scan statistics with inferior stats at 1% sampling (or less)

However after a big dataload the most important thing is often the edges of the histogram, and those are updated with sp_updatestats

So don't dismiss its use, especially since it runs very fast. It can save the day on certain DW loads

Panamerican