June 30, 2014 at 3:16 pm
We are doing a side by side upgrade from SQL Server 2008 to 2014 by setting up log shipping to minimize the downtime of this VLDB.
Do we need to rebuild all the indexes after 2008 db is upgraded to 2014?
July 1, 2014 at 4:54 am
I wouldn't worry about the indexes on 2014 more than I would on 2008. Whatever index rebuild you normally use should be fine. Traditionally many people will update their statistics with a full scan after the upgrade, but that's different. Also, be sure you change the compatibility level of the database after the upgrade. That's how the new cardinality estimator gets enabled for the database. Also, also, make sure you get the latest cumulative update. If you are doing ONLINE index operations, there's a bug that could lead to data loss that's addressed by the CU.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 1, 2014 at 8:29 am
Thanks Grant. So do we still need to update statistics as suggested by Microsoft?
We are not planning to change the compatibility level until after couple of weeks of migration to make sure everything works "as is".
Can we go back and forth on compatibility levels? Or once it's changed to a higher level it can't set to a lower level?
July 1, 2014 at 12:06 pm
I've been hearing a lot of mixed stuff lately on the need to update statistics. Personally, I'd do it. It's not going to hurt anything and might help.
You can switch the compatibility level on and off and you'll get different behaviors within the optimizer depending whether your compatibility level is set or not. But, if you turn it on and the stats get updated, the estimates will be using the new cardinality estimates until you turn it off and rebuild that set of statistics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 7, 2014 at 3:56 pm
Hallo UDBA,
my two cents on it.
if you don't change your compatibility level, it does not means that you are not doing a major change. In fact you will do it in two steps: instance upgrade/database move, and then database very upgrade. In general when I plan to move a database from one instance to another, (specially if the version is different), I put in my checklist the following steps:
Ececuting DBCC CHECKDB WITH DATA_PURITY;
Executing DBCC UPDATEUSAGE(dbname);
Executing EXEC sp_updatestats; for the database;
Optional: refresh any VIEWs using sp_refreshview (just to be sure that all the external resources like linked server and/or grants are available in the new instance... Following this philosophy you can recompile all procedures and functions too but normally I like to leave this job to developers);
Optional: create/fix login (but someone or something will have to do it).
If you didn't do it and if you are in charge to guarantee that all code will work after the database upgrade/s, I suggest you to use upgrade advisor especially before changing your compatibility level.
Rebuilding indexes does not really impact "the migration" results... They remain as good or bad as they was before the database move. I always rebuild them after compatibility level change.
Regards
Giovanni
=========================================================
I'm always happy to hear your opinion!
Senior Oracle DBA
Sql Server "learner" and... enthusiast!
July 9, 2014 at 9:28 am
At bare minimum to do the CHECK DB with data Purity and Update stats.
August 1, 2014 at 3:20 am
UDBA, I will suggest to rebuild index as well after migration... It will be bad to hear that new server isnt performing better (in case)...
Cheers
August 1, 2014 at 9:21 am
Agree but it's a very large 5tb+ DW so rebuilding index might not be an option for us in favor of quick cut-over.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply