The company I work for decided to upgrade the database engine from SQL Server 2005 to SQL Server 2014, the database is big and uses multiple files, and each table has a complex set of indexes to insure fast performance, and we are calculating the time required to upgrade, one missing bit of information I didn't find an answer for is:
Do we have to rebuild indexes after we upgrade the databases?
-
8No, but you should update statistics.Erik Reasonable Rates Darling– Erik Reasonable Rates Darling2016年11月10日 22:20:07 +00:00Commented Nov 10, 2016 at 22:20
-
1NO, not at all specially if before upgrade you find none of your big indexes are fragmented.Shanky– Shanky2016年11月11日 04:59:29 +00:00Commented Nov 11, 2016 at 4:59
1 Answer 1
You can just rebuild indexes as you normally would as part of your standard maintenance schedule, this doesn't need to be done specifically due to the upgrade. That being said it depending the size of your tables\indexes and how much time you have for your upgrade, it can't hurt to start with fresh rebuilt indexes if you can work it into your upgrade plan. But no, it's not a requirement.
You definitely need to update your statistics however, as this can lead to poor plan generation by the optimizer after upgrading. This needs to happen before you do any index rebuilding. I'll try and dig up the link I had that gave a more detailed explanation.
Here is a great article by Thomas LaRock that can serve as a basic upgrade checklist to build on, to ensure you haven't missed anything:
http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
Explore related questions
See similar questions with these tags.