Falha ao forçar o plano do Query Store com o motivo da falha NO_PLAN [duplicado]

Eu tenho uma consulta no repositório de consultas para a qual forcei um plano.

Posso confirmar que o plano é forçado com

SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1

e o plano aparece nos resultados

No entanto, se eu olhar para a last_force_failure_reason_desccoluna, vejoNO_PLAN

Algumas pesquisas no Google me levaram aos seguintes artigos:

Kendra Little

Deepthi Goguri

que ambos sugerem que a alteração dos índices que são usados ​​pelo plano é a causa do motivo da NO_PLANfalha.

Configurei a sessão de eventos estendidos no segundo artigo:

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

e posso ver os eventos do plano em questão, que possuem o seguinte texto:

O processador de consulta não pôde produzir o plano de consulta porque a dica USE PLAN contém um plano que não pôde ser verificado como válido para a consulta. Remova ou substitua a dica USE PLAN. Para maior probabilidade de forçar o plano bem-sucedido, verifique se o plano fornecido na dica USE PLAN é gerado automaticamente pelo SQL Server para a mesma consulta

A consulta é executada todas as noites como parte de uma construção de data warehouse onde os comandos DDL são comuns, então decidi configurar uma especificação de Auditoria de Banco de Dados para capturar SCHEMA_OBJECT_CHANGE_GROUPtipos de ação para ver se algum índice estava sendo alterado

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

Quando eu interrogar os resultados da seguinte forma:

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')

Eu posso ver todas as alterações para as tabelas na cláusula IN (quais são as tabelas que são selecionadas na consulta quem é o plano que estou tentando forçar)

Tudo o que posso ver são quedas e recriações de chaves estrangeiras que são bastante normais para nosso data warehouse. As chaves estrangeiras são recriadas com o mesmo nome que tinham quando foram descartadas. A sequência de eventos é

1. Existem restrições (não confiáveis)
2. As restrições são eliminadas
3. Execuções de consulta
4. Restrições recriadas (mesmo nome e NOCHECK)

O plano que foi forçado é aquele que foi gerado pelo processador de consultas no ponto 3 acima, então como a sequência de eventos é a mesma todas as noites, eu teria pensado que a mudança de restrições é irrelevante?

Fui mais longe e descobri em quais colunas as chaves estrangeiras que foram descartadas / recriadas estão:

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')

e nenhum deles são colunas em qualquer um dos índices não clusterizados usados ​​no query_plan

Tentei recriar um exemplo em um banco de dados AdventureWorks2016 em que forço um plano que executa uma busca NCI em uma coluna que possui uma chave estrangeira confiável e, em seguida, observo que o otimizador ainda usa o plano, apesar da chave estrangeira ser descartada e ainda usa quando é recriado não confiável:

/* 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

Sim!

/* 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

Sim!

O que está causando os NO_PLANerros? Tem algo a ver com a eliminação/criação de restrições de chave estrangeira?