Query Store Fundamentals: Why my plan forcing has failed?

One of the advantages of using Query store is the ability to force a plan. Once you force a plan, there may be reasons when the optimizer still chose to create a new execution plan without using the forced plan. During this time, a failure reason is stored in the Query Store. When the forced plan cannot be used, the optimizer goes through the regular compilation and optimization phases to create a new plan but it doesn’t actually fail the query itself. It executes in a normal way with the new plan being created.

But what causes these failures and how to know the reason behind these forced plan failures?

When you alter/drop the schema of the objects used inside the query plan you forced can cause the forced plan failures.

Let see with examples.

Let’s create a stored procedure, execute it and see the actual execution plan

IF EXISTS (
		SELECT *
		FROM sys.objects
		WHERE type = 'P'
			AND name = 'StockInfo'
		)
	DROP PROCEDURE dbo.StockInfo
GO

CREATE PROCEDURE dbo.StockInfo @Quantity [int]
AS
BEGIN
	SELECT [fo].[Order Key]
		,[si].[Lead Time Days]
		,[fo].[Quantity]
	FROM [Fact].[Order] AS [fo]
	INNER JOIN [Dimension].[Stock Item] AS [si] ON [fo].[Stock Item Key] = [si].[Stock Item Key]
	WHERE [fo].[Quantity] = @Quantity;
END;

USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;

To work on this example, let’s create the index and see if the index will be used.

USE [WideWorldImportersDW]
GO

CREATE NONCLUSTERED INDEX OderQuantity ON [Fact].[Order] ([Quantity]) INCLUDE (
	[Order Key]
	,[Stock Item Key]
	)
GO

Rerun the stored procedure and check if the index is being used

USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;

As the new plan is using the index, let’s force this plan so we can later modify this index object and see how the forced plan failures can be caused.

As I forced the plan using the index, I will now go ahead and drop the index and execute the stored procedure again.

USE [WideWorldImportersDW]
GO

DROP INDEX OderQuantity ON [Fact].[Order]

USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;

Did you observe, the query itself doesn’t fail but a new plan is generated although there is a forced plan. If you observe the newly generated plan doesn’t have the index in it.

Let’s go ahead and see the reason for this failure in Queries with forced plans using GUI. The reason for the failure is NO_INDEX as we dropped the index being used in the forced plan. There is no index that was used in the forced plan and so is the reason.

Once you create the exact index that has been dropped, it uses the forced plan back again.

USE [WideWorldImportersDW]
GO

CREATE NONCLUSTERED INDEX OderQuantity ON [Fact].[Order] ([Quantity]) INCLUDE (
	[Order Key]
	,[Stock Item Key]
	)
GO

/* Rerun the sp */
USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;

Altering the index used in the forced plan can also cause the failure. I will add a column to the index and rerun the stored procedure again. Check for the plan again.

USE [WideWorldImportersDW]
GO

CREATE INDEX OderQuantity ON [Fact].[Order] (
	[Quantity]
	,[Order Key]
	)
	WITH (DROP_EXISTING = ON);

/* Rerun the sp */
USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;
GO

Plan 2 is being used.

Open the Queries with forced plans and see the failure reason. NO_PLAN is the reason as there is no forced plan associated with that index definition. Remember, only the last failure reason is shown here and only the last_force_failure_count is shown but not the history of the reasons are shown. We can capture this information using Extended events shown at the ending of this post.

If you have queries with forced plans and those queries have execution plans using the three-part name queries (database.schema.object), renaming the database name will cause failure as well (Source: Microsoft)

If you observe, only the latest failure reason is shown in the Queries with forced plan report using GUI or by using sys.query_store_plan.

Code Source: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15
USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

To capture the history of the failure reasons, you can capture using the extended event “query_store_plan_forcing_failed“. Let’s create the Xevent:

CREATE EVENT SESSION [Querystoreforcedplanfailures] ON SERVER 

ADD EVENT qds.query_store_plan_forcing_failed
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\qserror.xel'),
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO

Enabled the Extended event and captured the information of when the Query Store plan forcing failed and the reason for the failure. Dropped the index “OderQuantity” first which captured the failure and later changed the definition of the index “OderQuantity” by adding a column. Both the failures were captured along with the error number, plan_id and the query_id information.

You can also use the extended event to notify when there is a forced plan failure by using the event “qds.query_store_notify_force_failure_failed“

Summary:

In this blog post, we have seen the reasons of why plan forcing fails and how to view them using GUI/sys.query_store_plan/extended events. Once you forced a plan doesn’t mean that it will always be forced and used. It can create a new plan by going through the regular optimization processes. Altering or dropping the objects within the forced execution plan or changing the name of the database with active forced plans can cause the failures.

Thanks for reading!

Resources:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15