Nic Neufeld
July 21, 2016 9:47 am
We just went from 2008R2 to 2014, and let me put it this way…there’s a reason I have trace flag 9481 seared into my memory. I have been surprised at how many CE-related regressions we’ve ended up having! I think in large part it is that the long-existing codebase has been tweaked and massaged over time in a way that works good for the 7.0 CE, and the new CE just finds fresh new ways to get bad plans, but thus far I’ve avoided switching the whole DB back via compatibility level.
Loading...
Reply
Upgrades And Regressions – Curated SQL
July 26, 2016 5:15 am
[…] Kendra Little explains when upgrades can cause performance to suffer: […]
Loading...
Reply
Poster – Troubleshooting SQL Server After a Migration or Upgrade - by Kendra Little
July 26, 2016 8:00 am
[…] had so much fun doing the podcast episode last week on why an upgrade / migration can cause performance problems that I drew out the concepts for troubleshooting perf after an […]
Loading...
Reply
Derek
July 27, 2016 5:11 am
After I do a migration, I always update statistics with a full scan.
Loading...
Reply
Kendra Little
July 27, 2016 7:55 am
You’re not alone in updating statistics post-migration. I believe the story goes that once upon a time it was needed because the format of the way statistics were stored change– and once upon a time was when everyone was upgrading to SQL Server 2000. It was long enough ago that I can’t actually verify the details on the internet 🙂
Updating statistics with fullscan can take hours against many databases, and could significantly lengthen the time of a migration. For that reason, I typically recommend making sure normal index and stats maintenance is moved over and is run on schedule rather than making stats update part of the migration.
If time isn’t an issue, then doing this shouldn’t cause a problem, though.
Edit! I found it! And my memory was wrong, in fact. It was the upgrade to SQL Server 2005. From books online:
“Set AUTO_UPDATE_STATISTICS to ON before you upgrade any databases. Otherwise, database statistics are not updated as part of the upgrade to SQL Server 2005. Relying on statistics from an earlier version of SQL Server may cause suboptimal query plans. By setting AUTO_UPDATE_STATISTICS to ON, all statistics are updated when they are first referenced. Updating statistics increases the possibility that more optimal query plans are selected when you execute queries.”
Post-2005 no engine changes have been made that have required updating stats, at least not that I’ve found.
Loading...
Reply
Joe
August 23, 2016 6:13 pm
Kendra, it sounds like you are doing a little mythbusting here, and are saying that Update Stats is not necessary after a migration as long as you leave Auto Update Stats on. I wonder, though, if updating them during the migration is a better idea than forcing queries to wait on the update after the migration takes place. Typically during a migration you plan time to do this sort of thing to insure success — not necessarily with fullscan, which would take forever — as opposed to waiting until afterward to do it.
I’d love to hear more about how you would weigh these different factors when choosing how to approach a migration.
Loading...
Reply
Kendra Little
August 23, 2016 7:43 pm
Hey Joe!
Have you read that rowmodctr or column modification counters for statistics are reset on database restore in some weird way?
I have not seen that. Execution plans will have to be cached when queries are first compiled, but auto update stats should only kick in on statistics use to optimize after modification thresholds have been hit.
If you’ve read something about issues with those thresholds, just wondering if you have a reference to the version it was in.
PS: even if you don’t remember a version, I want to test this for a blog post and make sure it works like I recall. Should make a good post.
Loading...
Reply
manishkumar1980
September 21, 2016 9:29 pm
Really Grateful for myth busting about Updatestats after migration.
On 1 prod server, We have to take 14 hours downtime after migration to run Update statistics with full scan on sql server 2008 R2 from 2005 server.
Loading...
Reply
Alexandre Araujo
August 28, 2017 11:49 am
Great video Kendra, i have a situation only just 10% of the queries are bad with new cardinality estimator. When i change the LEGACY_CARDINALITY_ESTIMATION to ON or compatibility to olders (09,100 and 110) the whole queries are good.
My point is: for use the new cardinality and compatibility (130) i’ll have to identity the all queries with cardinality problem and rewrite them ?
Loading...
Reply
Brian
February 27, 2018 1:18 pm
Thanks you Kendra for sharing this. It is still timely in my case 🙂
Comments migrated from WordpressNic Neufeld July 21, 2016 9:47 am We just went from 2008R2 to 2014, and let me put it this way…there’s a reason I have trace flag 9481 seared into my memory. I have been surprised at how many CE-related regressions we’ve ended up having! I think in large part it is that the long-existing codebase has been tweaked and massaged over time in a way that works good for the 7.0 CE, and the new CE just finds fresh new ways to get bad plans, but thus far I’ve avoided switching the whole DB back via compatibility level.
Loading... Reply Upgrades And Regressions – Curated SQL July 26, 2016 5:15 am […] Kendra Little explains when upgrades can cause performance to suffer: […]
Loading... Reply Poster – Troubleshooting SQL Server After a Migration or Upgrade - by Kendra Little July 26, 2016 8:00 am […] had so much fun doing the podcast episode last week on why an upgrade / migration can cause performance problems that I drew out the concepts for troubleshooting perf after an […]
Loading... Reply Derek July 27, 2016 5:11 am After I do a migration, I always update statistics with a full scan.
Loading... Reply Kendra Little July 27, 2016 7:55 am You’re not alone in updating statistics post-migration. I believe the story goes that once upon a time it was needed because the format of the way statistics were stored change– and once upon a time was when everyone was upgrading to SQL Server 2000. It was long enough ago that I can’t actually verify the details on the internet 🙂
Updating statistics with fullscan can take hours against many databases, and could significantly lengthen the time of a migration. For that reason, I typically recommend making sure normal index and stats maintenance is moved over and is run on schedule rather than making stats update part of the migration.
If time isn’t an issue, then doing this shouldn’t cause a problem, though.
Edit! I found it! And my memory was wrong, in fact. It was the upgrade to SQL Server 2005. From books online:
“Set AUTO_UPDATE_STATISTICS to ON before you upgrade any databases. Otherwise, database statistics are not updated as part of the upgrade to SQL Server 2005. Relying on statistics from an earlier version of SQL Server may cause suboptimal query plans. By setting AUTO_UPDATE_STATISTICS to ON, all statistics are updated when they are first referenced. Updating statistics increases the possibility that more optimal query plans are selected when you execute queries.”
https://technet.microsoft.com/en-us/library/ms143179(v=sql.90).aspx
Post-2005 no engine changes have been made that have required updating stats, at least not that I’ve found.
Loading...
Reply Joe August 23, 2016 6:13 pm Kendra, it sounds like you are doing a little mythbusting here, and are saying that Update Stats is not necessary after a migration as long as you leave Auto Update Stats on. I wonder, though, if updating them during the migration is a better idea than forcing queries to wait on the update after the migration takes place. Typically during a migration you plan time to do this sort of thing to insure success — not necessarily with fullscan, which would take forever — as opposed to waiting until afterward to do it.
I’d love to hear more about how you would weigh these different factors when choosing how to approach a migration.
Loading... Reply Kendra Little August 23, 2016 7:43 pm Hey Joe!
Have you read that rowmodctr or column modification counters for statistics are reset on database restore in some weird way?
I have not seen that. Execution plans will have to be cached when queries are first compiled, but auto update stats should only kick in on statistics use to optimize after modification thresholds have been hit.
If you’ve read something about issues with those thresholds, just wondering if you have a reference to the version it was in.
PS: even if you don’t remember a version, I want to test this for a blog post and make sure it works like I recall. Should make a good post.
Loading... Reply manishkumar1980 September 21, 2016 9:29 pm Really Grateful for myth busting about Updatestats after migration.
On 1 prod server, We have to take 14 hours downtime after migration to run Update statistics with full scan on sql server 2008 R2 from 2005 server.
Loading... Reply Alexandre Araujo August 28, 2017 11:49 am Great video Kendra, i have a situation only just 10% of the queries are bad with new cardinality estimator. When i change the LEGACY_CARDINALITY_ESTIMATION to ON or compatibility to olders (09,100 and 110) the whole queries are good. My point is: for use the new cardinality and compatibility (130) i’ll have to identity the all queries with cardinality problem and rewrite them ?
Loading... Reply Brian February 27, 2018 1:18 pm Thanks you Kendra for sharing this. It is still timely in my case 🙂