Menu
What is the use of sp_updatestats? Can I run that in the production environment for performance improvement?
331
sp_updatestats can run from 2 minutes to 4 hours. Each night the ETL deletes all rows and recopies all rows, but overall, there is almost no difference in the size of the data.
If you mean UPDATE STATISTICS WITH FULLSCAN, weekly or monthly is probably enough for most situations. However, I often setup a job to run nightly to run EXECUTE SP_UPDATESTATS in active user databases, because it only updates the stats that are needed. It takes much less time and resources to run.
Answers. Some people suggested sp_updatestats, but sp_updatetats has no guaranteed way to result in a FULLSCAN for all tables.
Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries.
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.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
