I have had a couple of instances recently where re-building a database (in the course of upgrading from v10.0.1) has resulted in a very significant drop off in performance in some areas, which is corrected by running CREATE STATISTICS on all tables.
In each case the database was moderately large (30-50 GB) and had been running on the final ebf of v10.0.1. The re-build was done using dbunload.exe with -an. The latest ebfs for both v11.0.1 and v16.0 had been applied.
Performance was not universally poor but seemed to affect a number of tables (all with large numbers of rows 10,000,000+) and did not improve even with the same query being run repeatedly (to exclude caching issues - one possibility that had occurred to me was that the benefit of refreshing the statistics was coming from data being cached as a side-effect of running CREATE STATISTICS, but it seems not).
Re-creating statistics for the affected tables produced an instant performance improvement to something noticeably faster than v10.0.1 rather than a fraction of the speed. We then ran CREATE STATISTICS for all remaining tables with no apparent disadvantage.
How well do statistics transfer across versions (are they actually preserved, I know that there are LOAD STATISTICS statements included in reload.sql but does this happen when the version changes)?
Is it advisable to run CREATE STATISTICS for the whole database after any re-build or is there a downside to doing that (apart from the time taken)?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.