0

I can't find anything on BOL that indicates I need to rebuild indexes or statistics after the migration and compatibility level upgrade of a database. But I have read a few articles/answers around the net that indicate this would be a wise move. Kins great answer here mentions statistics rebuilds but it is for 2000 to 2008

Is this something that was a recommended practice with earlier upgrades or is it something we should still be doing?

  • What is the size of db? – user_0 Jun 5 '15 at 8:47
  • 2.6 TB with multiple file groups – James Anderson Jun 5 '15 at 8:50
  • I don't work with SQL Server anymore, but if rebuilding indexes was actually crucial it would be mentioned in the BOL. Similar for statistics, only if the internal structure added more data in the new release it might be recommended, but you will recollect stats within the next days/weeks anyway... – dnoeth Jun 5 '15 at 10:31
  • 1
    Only stats update will be sufficient, since you have a large db. – Kin Shah Jun 5 '15 at 11:11
  • 1
    Updating stats and switching compatibility mode are among the most prominent first steps advised after an upgrade. You want the new version of the engine to understand the distribution of the data in its own terms, which may be slightly different from the old version. – Aaron Bertrand Jun 5 '15 at 11:52
1

I found interesting this link: http://thomaslarock.com/2011/12/7-mistakes-you-cant-afford-to-make-when-upgrading-to-sql-2012/

In my case I upgraded from sql 2000 to 2014 (with a 2008 bridge) detaching and moving files, but considerations are still good. My db whas about 1.3 terabytes.

I run

DBCC CHECKDB WITH DATA_PURITY;

But this is critical just becaming from 2000 or 2005, not your case.
Anyway: if you are moving datafile, you must check db... It is an online operation.
Also:

DBCC UPDATEUSAGE(db_name);

You can skip this.

Reindexes? I didn't find any documents about it. In my case it was even not acceptable. To much time and resources.

I updated statistics. I think this is important when you move a database. This can take long time, yes, but database is available to users. It is mandatory? No, but it makes me feel safe...

  • Thanks. We have a very small window to achieve this so I need to nail down if it is really required or not. – James Anderson Jun 5 '15 at 9:14
  • Some things are just raccomended. But consider that checkdb and update statistics are online operations. In my scenario I run checkdb before open access to users, just to avoid problems. Also, I tested it in a test environment (both to check my procedure and to take times). – user_0 Jun 5 '15 at 9:26
  • Yeah I'm running some tests in a dev environment now and recording the duration of each task. – James Anderson Jun 5 '15 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.