查询存储计划强制失败，失败原因 NO_PLAN [重复]

我在查询存储中有一个查询，我已经为此制定了一个计划。

我可以确认该计划是被迫的

SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1

并且计划显示在结果中

但是，如果我查看该last_force_failure_reason_desc列，我会看到NO_PLAN

一些谷歌搜索把我带到了以下文章：

肯德拉小

Deepthi Goguri

这两者都表明更改计划使用的索引是导致NO_PLAN失败的原因。

我在第二篇文章中设置了扩展事件会话：

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

我可以看到相关计划的事件，其中包含以下文本：

查询处理器无法生成查询计划，因为 USE PLAN 提示包含无法验证为合法查询的计划。删除或替换 USE PLAN 提示。为获得成功执行计划的最大可能性，请验证 USE PLAN 提示中提供的计划是 SQL Server 为同一查询自动生成的计划

该查询作为数据仓库构建的一部分每晚运行，其中 DDL 命令很常见，因此我决定设置一个数据库审计规范来捕获SCHEMA_OBJECT_CHANGE_GROUP操作类型，以查看是否有任何索引被更改

USE [master]
GO

CREATE SERVER AUDIT [PlanForceAlters]
TO FILE 
(   FILEPATH = N'P:\Audit\'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = 'd2b6b090-395f-42f9-a8bb-c0ba742ce30e')
ALTER SERVER AUDIT [PlanForceAlters] WITH (STATE = ON)
GO


USE [MyDatabase]
GO

CREATE DATABASE AUDIT SPECIFICATION [PlanForceAlters]
FOR SERVER AUDIT [PlanForceAlters]
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

当我如下询问结果时：

SELECT  o.name,
        a.statement
FROM    sys.fn_get_audit_file ('P:\Audit\PlanForce*',default,default) a
        JOIN sys.objects o
            ON o.object_id = a.object_id
WHERE   o.name IN ('MyTableA','MyTable')

我可以看到 IN 子句中表的所有更改（哪些表是从查询中选择的表，我试图强制执行谁的计划）

我所能看到的只是外键删除和重新创建，这对于我们的数据仓库来说是相当正常的。外键使用与删除时相同的名称重新创建。事件顺序是

1. 存在约束（不可信）
2. 约束被删除
3. 查询运行
4. 重新创建约束（同名和 NOCHECK）

强制执行的计划是查询处理器在上面第 3 点生成的计划，因此事件的顺序每晚都相同，我会认为约束的变化是无关紧要的吗？

我更进一步，计算出被删除/重新创建的外键在哪些列上：

SELECT  o.name,
        a.statement,
        c.name
FROM    sys.fn_get_audit_file ('P:\Audit\PlanForce*',default,default) a
        JOIN sys.objects o
            ON o.object_id = a.object_id
        LEFT JOIN sys.foreign_keys fk
            ON  statement LIKE '%ADD CONSTRAINT%' + fk.name + '%' OR
                statement LIKE '%DROP CONSTRAINT%' + fk.name + '%' OR
                statement LIKE '%ADD  CONSTRAINT%' + fk.name + '%' OR
                statement LIKE '%DROP  CONSTRAINT%' + fk.name + '%'
        LEFT JOIN sys.foreign_key_columns fkc
            ON fk.object_id = fkc.constraint_object_id
        LEFT JOIN sys.all_columns c
            ON c.column_id = fkc.parent_column_id AND
                c.object_id = fkc.parent_object_id
WHERE   o.name IN ('MyTableA','MyTableB')

并且这些都不是 query_plan 中使用的任何非聚集索引中的列

我试图在 AdventureWorks2016 数据库上重新创建一个示例，在该示例中我强制执行一个计划，该计划对具有可信外键的列执行 NCI 搜索，然后观察优化器仍然使用该计划，尽管外键被删除并且在重新创建不受信任时仍然使用：

/* create our stored proc */
CREATE OR ALTER PROCEDURE sp_SalesbyProduct
    @ProductID INT
AS
SELECT 
  SalesOrderID, 
  OrderQty,
  UnitPrice
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
GO

/* create an index to support the query */
CREATE INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail
(
    ProductId
) 
WITH 
(
    DROP_EXISTING = ON
)

/* add a trusted foreign key on the column IX_SalesOrderDetail_ProductID is on */
ALTER TABLE Sales.SalesOrderDetail ADD CONSTRAINT FK_MyKey FOREIGN KEY (ProductID) REFERENCES Production.Product(ProductId)

/* run the proc and ensure differing plans */
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710 /* seek on IX_SalesOrderDetail_ProductID with key lookup */
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870 /* CI Scan*/
GO

/* force the seek / lookup plan */
EXEC sp_query_store_force_plan 222, 224;

/* verify the plan is forced */
SELECT  *
FROM    sys.query_store_plan
WHERE   is_forced_plan = 1

/* run the queries again and ensure both use the seek / lookup plan */

DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870
GO

/* drop the constraint on the column in the index */
ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT FK_MyKey 

/* is the plan still forced? */

DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870
GO

是的！

/* re-add the FK but make it untrusted */
ALTER TABLE Sales.SalesOrderDetail WITH NOCHECK ADD CONSTRAINT FK_MyKey FOREIGN KEY (ProductID) REFERENCES Production.Product(ProductId)

/* is the plan still forced? */

DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870
GO

是的！

是什么导致了NO_PLAN错误？这与删除/创建外键约束有关吗？