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)?

asked 30 Oct '13, 16:07

Justin%20Willey's gravatar image

Justin Willey
6.8k110142212
accept rate: 20%

edited 30 Oct '13, 16:09

"in the course of upgrading from v10.0.1": to which version did you upgrade?

(31 Oct '13, 05:34) Reimer Pods
Replies hidden

Sorry - I didn't explain that very well! One db was upgraded to 11.0.1, the other to 16.0 - the behaviour was similar in both cases.

(31 Oct '13, 05:37) Justin Willey
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242
×203
×137
×11
×7

question asked: 30 Oct '13, 16:07

question was seen: 1,116 times

last updated: 31 Oct '13, 05:37