Of course, after migration, you need to fix regression as soon as possible. It is obvious in this case that the old plan is better: both the average execution time and the number of logical reads are significantly increased. What can you do with this information? All you want is to have the same (or similar) execution parameters as you had before the migration. How can you get them back? Here are the steps you usually need to perform prior to SQL Server 2016 when an important query suddenly starts to run slow:
- You need to understand why SQL Server decided to change the plan
- You can try to rewrite the query and hope that the optimizer will choose a better plan or the old plan
- You can apply a query hint ...
