This post originally went live on May 11, 2018, but modifications were made on May 14, 2018 after some additional internal discussions with Microsoft. Changes made on May 14, 2018 are in blue.
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.
tl;dr
Yes. Update statistics after an upgrade. Further, if you’re 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).
History
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. Official Microsoft documentation about the stats blog change in SQL Server 2005 is difficult to find, but this article includes the following paragraph:
After you upgrade from SQL Server 2000, update statistics on all databases. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server databases. This step is not necessary for upgrading from SQL Server 2005.
Current Microsoft documentation related to upgrading 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’ll see recommendations to update statistics. Further, the documentation that Microsoft provides about when to update statistics does not mention anything about upgrades.
Side bar: I don’t recommend using sp_updatestats, and here’s why: Understanding What sp_updatestats Really Updates.
Today
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. Here you go:
Microsoft suggests that customers test the need for a full update of statistics after a major version change and/or a database compatibility level change.
Further items to note:
- If Microsoft updates the format of statistics (e.g. the stats blog), customers will be expected to update statistics after an upgrade.
- Microsoft does not always upgrade the statistics format as part of a major version upgrade.
- There are occasions where Microsoft does not change the format of statistics, but they do change the algorithm for creating statistics as part of a major version upgrade or database compatibility level change.
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. And remember, rebuilding indexes updates the statistics for those indexes with a fullscan, so you do not need to update them again.
Conclusion
As part of your upgrade methodology, it is recommended (by me, based on experience with a lot of customer upgrades) to build in time to update statistics. I’ve gotten some pushback from customers who don’t want to update statistics after upgrade because it takes too long. Some kind reminders:
- Updating statistics is an online operation, therefore, the database and related applications are accessible and usable. A statistics update does take a schema modification lock so you’re not allowed to make any changes to a table while its stats are updating. Therefore, if you decide to change your schema after upgrading your SQL Server version (not something I would typically recommend), do that before you update stats.
- You need to update statistics regularly to provide the optimizer with current information about your data, so at some point it needs to be done. Immediately after an upgrade is a really good time, considering the aforementioned items.
If you’re not comfortable upgrading to a newer version of SQL Server, we can help! I’m in the process of helping a customer migrate from SQL Server 2012 to SQL Server 2017, and I’m so excited to get them up to the latest version so they can start using some new features…like Query Store 😉
10 thoughts on “Do you need to update statistics after an upgrade?”
Is your source at Microsoft willing to be named? I ask just because it would be nice to have a specific product team source to reference when requesting they update some of their documentation around this.
The Microsoft guidance for upgrades online in the “complete the database engine upgrade” section of books online doesn’t mention it at all– but it does call out a page that mentions manually updating the statistics for memory optimized tables specifically.
So that implies it is ONLY needed for those tables.
This is the page that probably needs to be updated: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/complete-the-database-engine-upgrade?view=sql-server-2017
Heh, just saw that you link to that page in your post. I didn’t follow the link the first time, sorry 🙂
Hey Kendra-
The recommendations came from Conor. I had a question about updating stats on the MVP DL and then followed up with him directly. This was the guidance he provided and confirmed I could write about it (and I followed up to make sure I could attach his name to it here 🙂
For anyone reading this who is not familiar with the SQL Server product team, Conor Cunningham is one of the main architects for SQL Server.
Erin
Thanks for the great info, Erin!
So, I admit that I’m using sp_updatestats, but let me qualify my use case. I’m running a data warehouse, so I run it after I have updated all, or nearly all, of the tables, within a given database. These are databases which hold tables that:
1. Are staging databases where tables are truncated and reloaded with new results.
2. Production databases where we have loaded new results for that period, which occurs monthly.
3. Client databases that have significant number of tables fall under 1. or 2.
In the end, I’m only using it monthly, against specific databases, and I have demonstratively proven that it resolves performance bottlenecks.
If it works for you, that’s great! I’m just not a fan of using it in something like OLTP where you might not know how much data changes, or when, and you might be wasting resources.
Here you mentioned “was a change in the nonclustered leaf level internals in SQL Server 2012”. Could you please elaborate more what was changed for nci.
Prior to 2012, a non-unique, nonclustered index row will have a null bitmap (minimum of 3 bytes) only if there are columns that are NULLable.
In 2012+, a non-unique, nonclustered index row will have a NULL bitmap of 1 bit per columns (and a 2-byte column count)
Thanks, Erin! I followed your articles on Red-Gate and found this discussion about sp_updatestats. Agree with the point. Is it easy to change your show all stats query to include index stats and column stats and when they were both updated?
Randy Bonnette
Hi Randy-
Which query did you want updated? Can you provide a link?
Erin