• The SQL Server 2016 Query Store – Part 1: Overview and Architecture
• The SQL Server 2016 Query Store – Part 2: Built-In Reporting
• The SQL Server 2016 Query Store – Part 3: Accessing Query Store Information Using DMVs
• The SQL Server 2016 Query Store – Part 4: Forcing Execution Plans Using the Query Store
• The SQL Server 2016 Query Store – Part 5: Analyzing Query Store Performance

So far, in this series of articles about the SQL Server 2016 Query Store, we have taken a look at its architecture, explored the built-in reports and shown the various new DMVs related to the Query Store. Up until now, we mainly focused on the query runtime, information-gathering and reporting. Another great feature of the Query Store, the ability to easily force Execution Plans for specific queries, has thus far just been mentioned only briefly. In this article, the fourth in the series, we will focus on the plan-forcing options inside the Query Store, and show how you can use it to battle the problem of “plan regression”.

What is plan regression

Before we dive into the details of how we can force an Execution Plan for a specific query, I want to spend a few moments explaining a phenomena known as ‘plan regression’.

Plan regression happens when the SQL Server Query Optimizer compiles a new Execution Plan for a query that performs (much) worse than the previous Execution Plan for that same query.

There are many reasons why the Query Optimizer thinks a new plan is needed. Some examples include a change in the number of rows inside a table, an addition or removal of an index or a change in the table schema. Also, the distribution of data inside a table or index plays a big role in the way that the Query Optimizer selects an Execution Plan. To show you an example of plan regression, take a look at the Query Store Execution Plan graph below:

Figure 19 Query Store Execution Plan graph

The graph above shows us that query 104 generated two plans during execution, plan id 113 and plan id 114. Looking at the graph above immediately shows the advantage of the Query Store, in one glance, we can see that the Execution Plan with id 113 performed much worse, based on its duration, than the plan with id 114.

Before we had access to the query store, it was frequently a complicated and time-consuming task to analyze plan regression: We had to try and capture the different Execution Plans that were generated and compare them. Thankfully, The Query Store visualizes the information in which we are interested; we can then easily and quickly compare the different Execution Plans of the query in question by using the “Compare plans” button as shown in Figure 20 below.

Figure 20 Compare Execution Plans

Forcing Execution Plans the old way

In the past, when we wanted to force a specific Execution Plan for a query, we had to go through some pretty complex steps to build a Plan Guide and force it on a specific query. To give you an idea just how time-consuming and complex the task of creating and forcing Plan Guides can be, take a look at the simple queries below.

To demonstrate this easily, I ran both queries inside one SQL batch. As you can see, both queries are identical, the only difference being in the p.ProductID value inside the WHERE clause. Both of the Execution Plans are shown in Figure 21.

Figure 21 Different Execution Plans

Now imagine that we wanted to optimize this query by making sure that the first Execution Plan would always be selected. To do this, one of the options is to create a so-called Plan Guide. We would then link the Plan Guide to the query so that every time the query gets executed, SQL Server will pick the plan we supplied for the Plan Guide. To do this we would have to follow the following steps.

1. Run SET SHOWPLAN_XML ON to return the XML Execution Plan, or alternatively, grab the XML Plan from the sys.dm_exec_text_query_plan DMF)
2. Create the Plan Guide using the sp_create_plan_guide Stored Procedure. In his example the input of the SP would look something like this, I removed most of the XML Plan since it is very large:
   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   EXEC sp_create_plan_guide @name = N'ForcePlan', @stmt = N'SELECT p.name, tha.TransactionDate, tha.TransactionType, tha.Quantity, tha.ActualCost FROM Production.TransactionHistoryArchive tha JOIN Production.Product p ON tha.ProductID = p.ProductID WHERE p.ProductID = @productID', @type = N'SQL', @module_or_batch = NULL, @params = N'@productID INT', @hints = '<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.4" Summary..........'

3. Execute the second one (Query 2) again, if you look at the Execution Plan this time you can see that the Plan Guide was used because we now use the Execution Plan of the first query as you can see in Figure 22

Figure 22 Force plan

Also keep in mind that creating Plan Guides is a very precise action. For instance, aligning the query text just a bit different can result in the Plan Guide not being used.Now that you have an idea of the actions you would have to perform in order to specify a specific Execution Plan for a query in previous versions of SQL Server, let’s take a look how we can do the same thing using the Query Store.

Forcing Execution Plans using the Query Store

If we want to force Execution Plans using the Query Store we have two options,

• clicking the “Force Plan” button inside one of the Query Store reports
• using the sp_query_store_force_plan SP.

The “Force Plan” button in the reports is, by far, the easiest option to use. Figure 23 shows the Execution Plan graph of the queries we executed earlier in the previous section.

Figure 23 Execution Plan graph

As you can see in the figure above, my testing with Plan Guides generated three different Execution Plans. Since I am interested in using the Execution Plan with ID 146 for the queries, which is the same one as in Figure 22, I only need to click on the plan inside the Execution Plan graph and press the “Force Plan” button as shown in Figure 24.

Figure 24 Force plan 146

After clicking “Yes” the Execution Plan graph will change, indicating that the query is now using that specific Execution Plan.

Figure 25 Force Plan active

If I were to once again execute the query that we used earlier to demonstrate Plan Guides, SQL Server would use the Execution Plan selected in the Query Store to execute the query.

The other options to force a specific Execution Plan for a query is to use the new sp_query_store_force_plan SP. This works almost as simply as the use of the “Force Plan” button but you will have to check the ID that the query and the Execution Plan received inside the Query Store. To force the same Execution Plan as we did earlier, we would execute the following command:

Here the first parameter stands for the query ID, the second for the Execution Plan ID inside the Query Store.

As you can see, both methods inside the Query Store are a lot easier to use than the Plan Guide option, saving valuable time and immensely simplifies the process of forcing a plan.

Summary

In this article we took a look at plan-regression and how it occurs. We explored the method we had to use pre-Query Store when we wanted to force a specific Execution Plan and at the end we looked at how we can force Execution Plans using the Query Store.

This concludes the fourth part of the Query Store article series. In the fifth, and for now final, article in the series, The SQL Server 2016 Query Store: Query Store Performance, we will take a look at the performance impact of enabling the Query Store.