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?

Community
  • 1
James Anderson
  • 5,725
  • 2
  • 26
  • 43
  • What is the size of db? – user_0 Jun 05 '15 at 08:47
  • 2.6 TB with multiple file groups – James Anderson Jun 05 '15 at 08: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 05 '15 at 10:31
  • 1
    Only stats update will be sufficient, since you have a large db. – Kin Shah Jun 05 '15 at 11:11
  • Thanks @Kin. Out of interest what does rebuilding the stats actually fix? Are they built differently in 2012? – James Anderson Jun 05 '15 at 11:19
  • Stats are used by Query Optimizer to gain data distribution and data density.. So stats should be up to date. – Kin Shah Jun 05 '15 at 11:39
  • Yes I agree but if they are already up to date before the migration why the need to update them again after migration? – James Anderson Jun 05 '15 at 11:45
  • 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 05 '15 at 11:52
  • OK so we're not 100% sure it's needed but it's better to be safe than sorry. Sounds like a plan – James Anderson Jun 05 '15 at 12:03

1 Answers1

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...

user_0
  • 1,023
  • 1
  • 10
  • 25
  • 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 05 '15 at 09: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 05 '15 at 09:26
  • Yeah I'm running some tests in a dev environment now and recording the duration of each task. – James Anderson Jun 05 '15 at 09:31