What is more appropriate sp_updatestats or UPDATE STATISTICS?

Question

In a system there is a nightly data load process where all the foreign keys are dropped, all tables are truncated, repopulated from an external source, and the foreign keys are put back in place.

At the end of this process I would expect sp_updatestats to run, so that statistics for all this new data is updated.

However, while inspecting the code I noticed the following line instead:

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

Can you compare that with running sp_updatestats? That is what is the differences and advantages/disadvantages of one over the other?

Answer 1

Couple of things ..

1. Stop using the undocumented sp_MSforeachtable

2. From Erin's blog - Understanding What sp_updatestats Really Updates

   sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows

Note that sp_updatestats in the code sp_helptext sp_updatestats, it runs 'UPDATE STATISTICS '

Recommendation:

• Use Ola's Index maintenance solution