{"id":69162,"date":"2016-11-29T13:40:00","date_gmt":"2016-11-29T13:40:00","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=69162"},"modified":"2021-08-24T13:39:27","modified_gmt":"2021-08-24T13:39:27","slug":"determining-happens-force-execution-plan-via-query-store","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/determining-happens-force-execution-plan-via-query-store\/","title":{"rendered":"Determining What Happens When You Force an Execution Plan via the Query Store"},"content":{"rendered":"

In one of my previous articles about the Query Store<\/a> I briefly touched on the subject of forcing an execution plan via the Query Store. There is, however, much more to relate than I could fit in the previous introduction article. In this article, I\u2019ll be explaining how you can set up a test to find out what happens when you force a plan, both through the Query Store built-in reports and via Query Store\u2019s own DMVs. I\u2019ll then use these to demonstrate what happens when plan-forcing fails, and what is causing the failure.<\/p>\n

Forcing execution plans<\/h2>\n

In my \u201cForcing Execution Plans using the Query Store\u201d article I described how it was easier to force a specific execution plan than it was to manually build a plan guide: However, I didn\u2019t go into more detail about the way that the Query Store records forced plans or what happens when execution plans can no longer be forced.<\/p>\n

There are several ways in which we can force specific execution plans for a query through the Query Store. You have the alternatives of forcing execution plans through the various built-in reports that are provided through the Query Store or by using the sp_query_store_force_plan<\/strong> stored procedure.<\/p>\n

In order to get several plans for a single query inside the Query Store, I created a stored procedure that retrieves data from the SalesOrderDetail<\/strong> table based on the ProductID<\/strong> inside the AdventureWorks<\/strong> database.<\/p>\n

CREATE PROCEDURE sp_SalesbyProduct\r\n\t@ProductID INT\r\nAS\r\nSELECT \r\n  SalesOrderID, \r\n  OrderQty,\r\n  UnitPrice\r\nFROM Sales.SalesOrderDetail\r\nWHERE ProductID = @ProductID\r\nGO\r\n<\/pre>\n
The next step is to execute the sp_SalesbyProduct<\/strong> stored procedure. I am executing the stored procedure twice with different parameters, 710 and 870. I picked both these parameter values deliberately, basing my choice on how the table data is distributed inside the statistics of the table. Because there are only 44 sales inside the SalesOrderDetail<\/strong> table for Product ID 710, the Query Optimizer resorts to using an \u2018Index Seek\u2019 operation. There are a lot more sales for Product ID<\/strong> 870, 4688 to be exact. For such a high number of rows the Query Optimizer chooses to scan the entire table and uses an Index Scan operation.<\/p>\n
Between executions of the stored procedure I am forcing a clearance of the plan cache. The reason for this is that SQL Server always prefers to avoid the cost of (re)compilation of execution plans. This is especially true for stored procedures that can easily be parameterized. If I didn\u2019t clear the plan cache between both executions, the first execution would result in a parameterized execution plan inside the plan cache. The second time the stored procedure would be executed the Query Optimizer would detect there already is an execution plan in the plan cache that could be used for the execution of the stored procedure. Even though the execution plan in the plan cache was compiled for a parameter value of 710, SQL Server would execute the exact same plan but then use the parameter value of 870. This way we would never get a different plan based on the parameter value unless we either trigger an execution plan recompilation or remove the plan from the plan cache.<\/p>\n
DBCC FREEPROCCACHE\r\nGO\r\nEXEC sp_SalesbyProduct @ProductID = 710\r\nGO\r\nDBCC FREEPROCCACHE\r\nGO\r\nEXEC sp_SalesbyProduct @ProductID = 870\r\nGO\r\n<\/pre>\n
Through the Query Store built-in reports, I can see that two different plans were used when executing the T-SQL above. One of the execution plans uses an Index Seek operation to retrieve the data, whereas the other uses an Index Scan.<\/p>\n
<\/p>\n
Figure 1:<\/strong> different execution plans for the same query<\/p>\n
Let\u2019s force one of the plans for the query. In this case I want to force the execution plan that uses an Index Seek operation. From figure 1 above, we can see that our query received a Query Store query ID of 1375, and the execution plan we want to use has an execution plan ID of 1459.<\/p>\n
  EXEC sp_query_store_force_plan 1375, 1459;\r\n<\/pre>\n
By running the stored procedure above, the execution plan is forced as shown by the little \u201cV\u201d on top of the circle depicting the specific execution plan in figure 2 below.<\/p>\n
<\/p>\n
Figure 2:<\/strong> execution plan 1459 forced on query 1375<\/p>\n
So besides showing a \u201cV\u201d on the execution plan inside the graphical Query Store reports, how does the Query Store record what execution plan is forced, and for what query?<\/p>\n
Before we were able to use the Query Store, we would query the sys.plan_guides<\/strong> DMV when we were dealing with forced execution plans, so this seems the first logical place to look for forced plans. But, as you can see in figure 3 below, the Query Store doesn\u2019t record information about the forced-plan inside the sys.plan_guides<\/strong> DMV.<\/p>\n
<\/p>\n
Figure 3:<\/strong> sys.plan_guides results<\/p>\n
Instead, the Query Store records those plans that are forced inside one of its own DMVs: sys.query_store_plan<\/strong>. By running the following query, we can retrieve all the plans that were forced using the Query Store including some additional information such as the number of occasions that the plan-forcing failed.<\/p>\n
SELECT\r\n  query_id,\r\n  plan_id,\r\n  is_forced_plan,\r\n  force_failure_count,\r\n  last_force_failure_reason_desc\r\nFROM sys.query_store_plan\r\nWHERE is_forced_plan = 1\r\n<\/pre>\n
<\/p>\n
Figure 4:<\/strong> forced plan information inside sys.query_store_plan.<\/p>\n
To easily identify all the queries that have an execution plan forced, you can also use the (custom) Query Store Database Dashboard. The Query Store Database Dashboard is an open-source and free SSMS report that returns additional information that is stored inside the Query Store, and that isn\u2019t available through the built-in reports. It also includes a section that shows all the forced plans for a database that has the Query Store enabled. You can download the Query Store Database Dashboard through its GitHub page: https:\/\/github.com\/Evdlaar\/QueryStoreDashboard<\/a>.<\/p>\n
<\/p>\n
Figure 5:<\/strong> Forced Execution Plans section inside the Query Store Database Dashboard<\/p>\n
When plan forcing fails<\/h2>\n
As you have seen in figure 4, the Query Store also records information when it isn\u2019t able to force the execution plan, such as the number of times that it failed to force the plan and the last reason for failure.<\/p>\n
As it turns out, there are various reasons why the Query Store is unable to force the execution plan that we want. Let\u2019s take a look at some of those reasons for failure through some examples.<\/p>\n
For starters, what would happen if you remove an index that the forced execution plan uses? The plan we forced earlier happens to use the IX_SalesOrderDetail_ProductID<\/strong> index on the SalesOrderDetail<\/strong> table for an Index Seek operation, so let\u2019s disable the index!<\/p>\n
  ALTER INDEX IX_SalesOrderDetail_ProductID\r\n ON Sales.SalesOrderDetail DISABLE\r\n<\/pre>\n
We can show this by running our stored procedure again, this time with a product ID of 870. Without plan forcing, this would result in an Index Scan operation and as you can see in figure 6 below we do not get an execution plan forced on the query.<\/p>\n
<\/p>\n
Figure 6:\u00a0<\/strong> the old plan instead of the forced plan is returned after disabling the index it used<\/p>\n
If we run our query to grab forced plan information from the Query Store we can see if, and why, it failed to force the execution plan.<\/p>\n
SELECT\r\n  query_id,\r\n  plan_id,\r\n  is_forced_plan,\r\n  force_failure_count,\r\n  last_force_failure_reason_desc\r\nFROM sys.query_store_plan\r\nWHERE is_forced_plan = 1\r\n<\/pre>\n
<\/p>\n
Figure 7:<\/strong> NO_INDEX execution plan force failure<\/p>\n
As you can see in figure 7 above, the Query Store couldn\u2019t force the execution plan because one of the operators inside the execution plan depended on the index we disabled. Hence the NO_INDEX inside the last_force_failure_reason_desc<\/strong> column.<\/p>\n
So what happens when we enable our index again? Would the Query Store go back to forcing the execution plan again?<\/p>\n
<\/p>\n
Figure 8:<\/strong> execution plan is forced again<\/p>\n
Yes, it does! 
 Directly after rebuilding the index (a rebuild is required to enable the index) the Query Store is able to force the execution plan again.<\/p>\n
Let\u2019s look at another example. What would happen if we were to remove the execution plan we want to force from the Query Store? There are two ways we can use to remove execution plans that are stored inside the Query Store:<\/p>\n