Answer recommended by moderator
I would recommend that you revert to 150 to see if the degradation persists. If it does not, the degradation is due to one or more queries seeing performance regressions with the new features of the optimizer.
Keep running with compat level 150 for a week or two and then flip back to 160. You can now use Query Store to find the degraded queries, and as a quick resolution you can force the old plans from compat level 150. In the long run, you may want to take a closer look at these queries to see how they can be improved.
While you can do this check manually, there is also a tool in SSMS to help you. Right-click the database in SSMS and select Tasks. You find Database Upgrade at the bottom of the menu.
-
K Durga Prasanna 30 Reputation points • Microsoft External Staff
Hi @Ginesh
Hope you are doing great. We would like to confirm if your query has been resolved by the provided solutions. If it is still outstanding, please share any additional information so we can assist you further.
Thank you.
-
K Durga Prasanna 30 Reputation points • Microsoft External Staff
Hi @Ginesh
We would like to confirm whether the issue has been resolved. If it is still outstanding, please share any additional information so we can assist you further.
Thank you.
Sign in to comment