July 18, 2019 at 8:22 pm
I have migrated SQL 2008 R2 to SQL 2016
I took back and restore from SQL 2008 R2 to SQL 2016
I did change SQL compatible mode to SQL 2016 in new server.
Now the problem is couple of query which use to finish in 10 min in SQL 2008 R2 which is taking double the time in SQL 2016 to execute.
What should I do to diagnostic more further?
July 18, 2019 at 8:38 pm
We went through the same thing you are going through in the past, we migrated from 2008 R2 to 2016 SP1 CU8 (the latest at the time).
You can change the Database settings to use the "Legacy Cardinality Estimation" in the database Properties > Options, see how it behaves, if it doesn't work you should change compatibility mode to 2008 (100).
Identify which queries are taking longer and modify them to fit the new SQL Server 2016 version.
Ask your users which reports/transactions might be taking longer if you can.
If you still have your older environment (2008 R2) one, try these in there see differences, go to microsoft doc and see if a feature have been deprecated or not being supported.
See the difference between the plans in the different versions.
There's an extended event which tells you which features will be deprecated in the future.
July 18, 2019 at 8:49 pm
Also, this post may be helpful:
https://www.sqlconsulting.com/archives/big-performance-problems-with-the-cardinality-estimator/
I'm preparing to do similar thing, so just curious, did you run Data Migration Assistant before you did migration? And if so, did it reveal anything that you had to fix?
--Vadim R.
July 19, 2019 at 12:54 pm
Thanks I will check all the option you mentioned
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply