{"id":917,"date":"2018-05-11T09:13:54","date_gmt":"2018-05-11T16:13:54","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=917"},"modified":"2018-05-14T18:38:56","modified_gmt":"2018-05-15T01:38:56","slug":"do-you-need-to-update-statistics-after-an-upgrade","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/do-you-need-to-update-statistics-after-an-upgrade\/","title":{"rendered":"Do you need to update statistics after an upgrade?"},"content":{"rendered":"

This post originally went live on May 11, 2018, but modifications were made on May 14, 2018 after some additional internal discussions with Microsoft.\u00a0 Changes made on May 14, 2018 are in blue.\u00a0\u00a0<\/span><\/p>\n

There are a variety of methods we use for helping customers upgrade to a new SQL Server version, and one question we often get asked is whether or not statistics need to be updated as part of the upgrade process.<\/p>\n

tl;dr<\/p>\n

Yes.\u00a0 Update statistics after an upgrade.\u00a0<\/em><\/strong> Further, if you\u2019re upgrading to 2012 or higher from an earlier version, you should rebuild your indexes (which will update index statistics, so then you just need to update column statistics).<\/p>\n

History<\/strong><\/p>\n

Some of you may remember that the stats blob changed between SQL Server 2000 and SQL Server 2005, and Microsoft specifically recommended updating statistics after upgrading from SQL Server 2000.\u00a0 Official Microsoft documentation about the stats blog change in SQL Server 2005 is difficult to find, but this article<\/a> includes the following paragraph:<\/p>\n

After you upgrade from SQL Server 2000, update statistics on all databases. Use the\u00a0sp_updatestats<\/a>\u00a0stored procedure to update statistics in user-defined tables in SQL Server\u00a0databases. This step is not necessary for upgrading from SQL Server 2005.<\/p><\/blockquote>\n

Current Microsoft documentation related to upgrading<\/a> does not state anything specific about updating statistics, but people continue to ask and if you peruse forums, blog posts, and other social media options, you\u2019ll see recommendations to update statistics.\u00a0 Further, the documentation that Microsoft provides about\u00a0when<\/em><\/a> t<\/span>o update statistics<\/a> does not mention anything about upgrades.<\/span><\/p>\n

Side bar:<\/strong> I don\u2019t recommend using sp_updatestats, and here\u2019s why: Understanding What sp_updatestats Really Updates<\/a>.<\/em><\/p>\n

Today<\/strong><\/p>\n

The statistics blob has not changed since SQL Server 2000 to my knowledge, but I thought I would ask someone from Microsoft for an official recommendation to share publicly.\u00a0 Here you go:<\/p>\n

Microsoft suggests that customers\u00a0test the need for<\/span> a full update of statistics after a major version change and\/or a database compatibility level change.<\/em><\/strong><\/p>\n

Further items to note:<\/p>\n

    \n
  1. If Microsoft updates the format of statistics (e.g. the stats blog), customers will be expected to update statistics after an upgrade.\n
      \n
    1. Microsoft does not always upgrade the statistics format as part of a major version upgrade.<\/li>\n<\/ol>\n<\/li>\n
    2. There are occasions where Microsoft does not change the format of statistics, but they do change the algorithm for creating statistics<\/em> as part of a major version upgrade or database compatibility level change.<\/li>\n<\/ol>\n

      In addition, there was a change in the nonclustered leaf level internals in SQL Server 2012, so if you are upgrading to 2012 or higher from an earlier version (e.g. 2008, 2008R2), rebuild your nonclustered indexes.\u00a0 And remember, rebuilding indexes updates the statistics for those indexes with a fullscan, so you do not need to update them again.<\/p>\n

      Conclusion<\/strong><\/p>\n

      As part of your upgrade methodology, it is recommended (by me, based on experience with a lot<\/em> of customer upgrades)<\/span> to build in time to update statistics.\u00a0 I\u2019ve gotten some pushback from customers who don\u2019t want to update statistics after upgrade because it takes too long.\u00a0 Some kind reminders:<\/p>\n