Are Forced Plans Always Forced?

Query Store is useful for forcing a beneficial plan. What if something changes that makes the forced plan impossible to use? Let’s look at an example of that today.

Building a Rep

Let’s use the StackOverflow 2013 database and create a test procedure that returns information from the Users table based on a user DisplayName:

CREATE PROCEDURE [dbo].[GetUser] @DisplayName NVARCHAR(40)
AS
BEGIN
SELECT Id
,DisplayName
,Reputation
FROM Users
WHERE DisplayName = @DisplayName
END

If we execute that procedure and check the execution plan, we’ll see that an index can improve performance:

EXEC [dbo].[GetUser] @DisplayName = N'Chad';
GO

Let’s create an index:

CREATE INDEX DisplayName_Reputation ON Users (DisplayName)
INCLUDE (Reputation);
GO

Then we’ll check our execution plan again to see the differences:

We now have an index seek instead of an index scan and key lookup.

Use the Force

Our plan looks much better after adding the DisplayName_Reputation index. If we check Top Resource Consuming Queries in Query Store, we’ll see our query with the two execution plans (before and after our index) we observed earlier. For the sake of this example, we want to make sure that Plan Id 3 using our DisplayName_Reputation index is forced. Let’s make sure that plan is selected on the right and click Force Plan:

We can check the Queries With Forced Plans pane in Query Store and see our query there:

Let’s run our stored procedure a few times and refresh the query store pane. We’ll see the check mark on the plan indicating the plan was forced successfully:

Good to Go?

What happens if the DisplayName_Reputation index being used in the forced plan is disabled or dropped? If we run our stored procedure again, will it fail like a query would if it was forcing an disabled or missing index? Let’s disable our index and find out:

ALTER INDEX [DisplayName_Reputation] ON [dbo].[Users] DISABLE
GO

And run our stored procedure again:

The query completed successfully but our execution plan shows the original plan without the index.

Refreshing the Queries With Forced Plans pane will confirm that the “old” plan, Plan Id 1, was used instead of our forced plan:

We can also see the forced plan failure count value is 1. The reason why is in the “last forced plan failure description field”:

Luckily for us, our stored procedure didn’t fail when the requirements for the forced plan weren’t available.

A missing index isn’t the only reason a forced plan can fail. Below is a list from Microsoft’s sys.query_store_plan page showing what last_force_failure_reason_desc values you can possibly see in sys.query_store_plan:

If you forced plans in the past, it’s worth taking another look to see if those plans are still being forced today.

Thanks for reading!