4

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?

  • 8
    No, but you should update statistics. – Erik Darling Nov 10 '16 at 22:20
  • 1
    NO, not at all specially if before upgrade you find none of your big indexes are fragmented. – Shanky Nov 11 '16 at 4:59
3

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/

| improve this answer | |

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.