none
SQL server 2005 to SQL server 2014 up-gradation post in place up gradation do we need to update stats

    Question

  • Hi - I am not migrating but upgrading (in place) an SQL instance from SQL 2005 to SQL 2014, i need an post migration guidance on whether for up-gradation we will need to execute the update statistics with full scan on the key database after up gradation.

    I am certain its very important during migration but not clear if it would be of benefit on up gradation (in-place)

    EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

    Regards

    Eben

    Tuesday, June 12, 2018 7:03 PM

Answers

  • I would say stats update in your case would even be more important as you are moving from SQL Server 2005 to SQL Server 2014 which has new CE. If you do not update stats and use compatibility level 120 the queries will run very slow. I would strongly advise you to update stats.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Wednesday, June 13, 2018 10:56 AM
    Moderator
  • Hello Eben,

    Yes, you should update all stats for better Performance and there are some more consideration for the Migration, see Supported Version and Edition Upgrades => SQL Server 2014 Support for SQL Server 2005


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 13, 2018 9:06 AM
    Moderator

All replies

  • It does not matter whether you are migrating the database or if you are making an in-place upgrade.

    Tuesday, June 12, 2018 9:28 PM
  • Hello Eben,

    Yes, you should update all stats for better Performance and there are some more consideration for the Migration, see Supported Version and Edition Upgrades => SQL Server 2014 Support for SQL Server 2005


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 13, 2018 9:06 AM
    Moderator
  • I would say stats update in your case would even be more important as you are moving from SQL Server 2005 to SQL Server 2014 which has new CE. If you do not update stats and use compatibility level 120 the queries will run very slow. I would strongly advise you to update stats.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Wednesday, June 13, 2018 10:56 AM
    Moderator
  • Hi - I am not migrating but upgrading (in place) an SQL instance from SQL 2005 to SQL 2014, i need an post migration guidance on whether for up-gradation we will need to execute the update statistics with full scan on the key database after up gradation.

    I am certain its very important during migration but not clear if it would be of benefit on up gradation (in-place)

    Irrespective of the upgrade, you should be doing the stats update already unless you have a database with very infrequent and minimal data or index changes and if things work fine with the default auto-update stats database option. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, June 13, 2018 11:16 AM
  • Thank you 
    Wednesday, June 13, 2018 2:30 PM
  • Thank you
    Wednesday, June 13, 2018 2:30 PM