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 to enforce a better plan or the old execution plan
• If you have saved the old plan, you can try to enforce it by using plan guides

All these tasks require time and knowledge to implement, and since they include code changes there is a risk that the change will break the application's functionality. Therefore, it introduces testing, which means additional time, resources, and money. You usually don't have a lot of time, and company management is not happy when an action requires more money.

As you can guess, Query Store will save you time and money. It allows you to instruct the optimizer to use the old plan. All you have to do is to choose the plan you want to be applied, click the Force Plan button, and then confirm the decision by clicking on Yes in the Confirmation dialog box as shown in the following screenshot:

Now, we will execute the query again, as shown in the following code:

SET NOCOUNT ON;
SELECT * FROM Sales.Orders o
INNER JOIN Sales.OrderLines ol ON o.OrderID= ol.OrderID
WHERE o.SalespersonPersonID IN (0, 897);

Now, you will see a third color (with the circle representing the third execution plan) as shown in the following screenshot:

The execution is faster, you get the old plan again and there is no risk of a breaking change. And also, you did not spend much time fixing the issue!

You can also force and unforce a plan by using Query Store stored procedures. The following command unforces the execution plan that you forced in the previous step:

EXEC sp_query_store_unforce_plan @query_id = 1, @plan_id = 1; 

Now we will execute the query again, as shown in the following code: 

SET NOCOUNT ON;
SELECT * FROM Sales.Orders o
INNER JOIN Sales.OrderLines ol ON o.OrderID= ol.OrderID
WHERE o.SalespersonPersonID IN (0, 897);

Now, you will see that the plan is not forced anymore and that the execution is slow again, as shown in the following screenshot:

Now you can use Transact-SQL to force the old plan again:

EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1;

When you re-execute the query, you will see that the plan is forced again and another circle has appeared in the main pane as you can see in the following screenshot:

SET NOCOUNT ON;
SELECT * FROM Sales.Orders o
INNER JOIN Sales.OrderLines ol ON o.OrderID= ol.OrderID
WHERE o.SalespersonPersonID IN (0, 897);

When you re-execute the query, you will see that the plan is forced again and another circle has appeared to the main pane as you can see in the following screenshot:

In this section, you saw that Query Store can help you not only to identify performance regressions, but also to solve them quickly, elegantly, and with almost no effort. However, bear in mind that forcing an old plan is a forever decision; the plan will always be applied whenever the query is executed. You have to be absolutely sure that you want this when you force the plan.

You should also notice that forcing an old, good-looking execution plan in a Query Store report does not guarantee that the execution with it will be better. A typical example would be issues with parameter sniffing, where different parameter combinations require different plans. Forcing an old plan in that case might be good for some parameter combinations only; but for others, it could be even worse than the actual, bad execution plan. Generally, Query Store helps you to solve problems with queries whose execution plans have changed over time but that have stable input parameters. You should not force the old plan for all queries when you see that the old execution parameters look better!

I am using Query Store intensively, and it is an excellent tool and a great help for me during query troubleshooting. I have forced an old execution plan several times in a production system to solve or mitigate a significant performance degradation. In my company, massive workload and peaks happen on a weekend, and if you have an issue on a weekend, you usually want to solve it or find a workaround as quickly as possible. Query Store allows me to force a well-known and good plan and solve the issue temporarily. I review and evaluate the situation later, during regular working time, without pressure and the risk of breaking some applications. Sometimes, I rewrite the code and unforce the plan; sometimes, when I am completely sure that I want exactly the plan that I have forced, I leave it in the production database. When you know what you are doing, you can use all Query Store features. Query Store can save time and money.