Eu tenho uma consulta para a qual forço um plano no Query Store (o plano é o SQL Server compilado para esta consulta) Se eu executar a consulta imediatamente após forçar o plano, recebo o NO_PLANlast_force_failure_reason_desc apesar de nenhuma alteração no banco de dados. Posso forçar um plano diferente para a mesma consulta com sucesso
O problema pode ser ilustrado abaixo:
Crie nosso banco de dados de teste
USE [master]
CREATE DATABASE NO_PLAN
ALTER DATABASE [NO_PLAN] SET QUERY_STORE = ON
ALTER DATABASE [NO_PLAN] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL)
GO
USE NO_PLAN
GO
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyTableA') DROP TABLE MyTableA
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyTableB') DROP TABLE MyTableB
/* create our tables */
CREATE TABLE [dbo].[MyTableA](
[Column1] VARCHAR(50) NULL ,
[Column2] VARCHAR(255) NULL ,
[Column3] INT NULL ,
[Column4] DATETIME NULL ,
[Column5] INT NULL ,
[Column6] VARCHAR(50) NULL ,
[Column7] VARCHAR(255) NULL ,
[Column8] INT NULL ,
[Column9] DATETIME NULL ,
[Column10] INT NULL ,
[Column11] INT NULL ,
[Column12] DATETIME NULL ,
[Column13] VARCHAR(50) NULL ,
[Column14] VARCHAR(50) NULL ,
[Column15] DATETIME NULL ,
[Column16] DATETIME NULL ,
[Column17] VARCHAR(8) NULL ,
[Column18] DATETIME NULL ,
[Column19] INT NULL ,
[Column20] INT NULL ,
[Column21] VARCHAR(50) NULL ,
[Column22] VARCHAR(255) NULL ,
[Column23] VARCHAR(50) NULL ,
[Column24] VARCHAR(255) NULL ,
[Column25] VARCHAR(50) NULL ,
[Column26] INT NULL ,
[Column27] INT NULL ,
[Column28] INT NULL ,
[Column29] INT NULL ,
[Column30] INT NULL ,
[Column31] INT NULL ,
[Column32] INT NULL ,
[Column33] INT NULL ,
[Column34] INT NULL ,
[Column35] VARCHAR(50) NULL ,
[Column36] VARCHAR(50) NULL ,
[Column37] VARCHAR(50) NULL ,
[Column38] VARCHAR(50) NULL ,
[Column39] VARCHAR(255) NULL ,
[Column40] INT NULL ,
[Column41] VARCHAR(50) NULL ,
[Column42] INT NULL ,
[Column43] VARCHAR(255) NULL ,
[Column44] INT NULL ,
[Column45] VARCHAR(255) NULL ,
[Column46] INT NULL ,
[Column47] DATETIME NULL ,
[Column48] DATETIME NULL ,
[Column49] DATETIME NULL ,
[Column50] INT NULL ,
[Column51] VARCHAR(50) NULL ,
[Column52] VARCHAR(255) NULL ,
[Column53] VARCHAR(50) NULL ,
[Column54] VARCHAR(255) NULL ,
[Column55] VARCHAR(50) NULL ,
[Column56] VARCHAR(255) NULL ,
[Column57] VARCHAR(50) NULL ,
[Column58] VARCHAR(50) NULL ,
[Column59] CHAR NULL ,
[Column60] CHAR NULL ,
[Column61] CHAR NULL ,
[Column62] CHAR NULL ,
[Column63] CHAR NULL ,
[Column64] CHAR NULL ,
[Column65] CHAR NULL ,
[Column66] CHAR NULL ,
[Column67] CHAR NULL ,
[Column68] CHAR NULL ,
[Column69] CHAR NULL ,
[Column70] CHAR NULL ,
[Column71] CHAR NULL ,
[Column72] CHAR NULL ,
[Column73] CHAR NULL ,
[Column74] CHAR NULL ,
[Column75] CHAR NULL ,
[Column76] DATETIME NULL ,
[Column77] INT NULL ,
[Column78] INT NULL ,
[Column79] VARCHAR(50) NULL ,
[Column80] VARCHAR(255) NULL ,
[Column81] VARCHAR(50) NULL ,
[Column82] VARCHAR(255) NULL ,
[Column83] VARCHAR(50) NULL ,
[Column84] VARCHAR(255) NULL ,
[Column85] VARCHAR(50) NULL ,
[Column86] VARCHAR(255) NULL ,
[Column87] VARCHAR(50) NULL ,
[Column88] VARCHAR(255) NULL ,
[Column89] VARCHAR(50) NULL ,
[Column90] VARCHAR(255) NULL ,
[Column91] VARCHAR(50) NULL ,
[Column92] VARCHAR(255) NULL ,
[Column93] VARCHAR(50) NULL ,
[Column94] VARCHAR(255) NULL ,
[Column95] VARCHAR(50) NULL ,
[Column96] VARCHAR(255) NULL ,
[Column97] VARCHAR(50) NULL ,
[Column98] VARCHAR(255) NULL ,
[Column99] VARCHAR(50) NULL ,
[Column100] VARCHAR(255) NULL ,
[Column101] VARCHAR(50) NULL ,
[Column102] VARCHAR(255) NULL ,
[Column103] VARCHAR(50) NULL ,
[Column104] VARCHAR(255) NULL ,
[Column105] VARCHAR(50) NULL ,
[Column106] VARCHAR(255) NULL ,
[Column107] VARCHAR(50) NULL ,
[Column108] VARCHAR(50) NULL ,
[Column109] VARCHAR(50) NULL ,
[Column110] VARCHAR(255) NULL ,
[Column111] VARCHAR(50) NULL ,
[Column112] VARCHAR(255) NULL ,
[Column113] VARCHAR(50) NULL ,
[Column114] VARCHAR(255) NULL ,
[Column115] VARCHAR(50) NULL ,
[Column116] VARCHAR(255) NULL ,
[Column117] VARCHAR(50) NULL ,
[Column118] VARCHAR(255) NULL ,
[Column119] VARCHAR(50) NULL ,
[Column120] VARCHAR(50) NULL ,
[Column121] VARCHAR(255) NULL ,
[Column122] VARCHAR(50) NULL ,
[Column123] VARCHAR(255) NULL ,
[Column124] VARCHAR(50) NULL ,
[Column125] VARCHAR(255) NULL ,
[Column126] VARCHAR(50) NULL ,
[Column127] VARCHAR(255) NULL ,
[Column128] VARCHAR(50) NULL ,
[Column129] VARCHAR(255) NULL ,
[Column130] VARCHAR(50) NULL ,
[Column131] VARCHAR(255) NULL ,
[Column132] DATETIME NULL ,
[Column133] VARCHAR(50) NULL ,
[Column134] VARCHAR(255) NULL ,
[Column135] VARCHAR(50) NULL ,
[Column136] INT NULL ,
[Column137] VARCHAR(50) NULL ,
[Column138] VARCHAR(255) NULL ,
[Column139] VARCHAR(50) NULL ,
[Column140] VARCHAR(255) NULL ,
[Column141] VARCHAR(50) NULL ,
[Column142] VARCHAR(255) NULL ,
[Column143] VARCHAR(50) NULL ,
[Column144] VARCHAR(255) NULL ,
[Column145] VARCHAR(50) NULL ,
[Column146] VARCHAR(255) NULL ,
[Column147] VARCHAR(50) NULL ,
[Column148] VARCHAR(255) NULL ,
[Column149] VARCHAR(50) NULL ,
[Column150] VARCHAR(255) NULL ,
[Column151] VARCHAR(50) NULL ,
[Column152] VARCHAR(255) NULL ,
[Column153] VARCHAR(50) NULL ,
[Column154] VARCHAR(255) NULL ,
[Column155] VARCHAR(50) NULL ,
[Column156] VARCHAR(255) NULL ,
[Column157] VARCHAR(50) NULL ,
[Column158] VARCHAR(255) NULL ,
[Column159] INT NULL ,
[Column160] INT NULL ,
[Column161] VARCHAR(50) NULL ,
[Column162] VARCHAR(50) NULL ,
[Column163] VARCHAR(50) NULL ,
[Column164] VARCHAR(50) NULL ,
[Column165] VARCHAR(50) NULL ,
[Column166] VARCHAR(50) NULL ,
[Column167] VARCHAR(50) NULL ,
[Column168] VARCHAR(50) NULL ,
[Column169] VARCHAR(255) NULL ,
[Column170] INT NULL ,
[Column171] VARCHAR(50) NULL ,
[Column172] INT NULL ,
[Column173] VARCHAR(50) NULL ,
[Column174] VARCHAR(50) NULL ,
[Column175] VARCHAR(50) NULL ,
[Column176] VARCHAR(255) NULL ,
[Column177] VARCHAR(50) NULL ,
[Column178] VARCHAR(255) NULL ,
[Column179] VARCHAR(50) NULL ,
[Column180] VARCHAR(50) NULL ,
[Column181] VARCHAR(50) NULL ,
[Column182] VARCHAR(255) NULL ,
[Column183] VARCHAR(50) NULL ,
[Column184] VARCHAR(255) NULL ,
[Column185] VARCHAR(50) NULL ,
[Column186] VARCHAR(255) NULL ,
[Column187] VARCHAR(50) NULL ,
[Column188] VARCHAR(255) NULL ,
[Column189] VARCHAR(50) NULL ,
[Column190] VARCHAR(50) NULL ,
[Column191] VARCHAR(50) NULL ,
[Column192] VARCHAR(255) NULL ,
[Column193] VARCHAR(50) NULL ,
[Column194] VARCHAR(255) NULL ,
[Column195] VARCHAR(50) NULL ,
[Column196] VARCHAR(50) NULL ,
[Column197] VARCHAR(255) NULL ,
[Column198] INT IDENTITY (1,1) ,
[Column199] VARCHAR(500) NULL ,
[Column200] VARCHAR(255) NULL ,
[Column201] VARCHAR(50) NULL ,
[Column202] VARCHAR(255) NULL ,
[Column203] CHAR NULL ,
[Column204] CHAR NULL ,
[Column205] VARCHAR(50) NULL ,
[Column206] VARCHAR(255) NULL ,
[Column207] BIGINT NULL ,
[Column208] VARCHAR(50) NULL ,
[Column209] VARCHAR(50) NULL ,
[Column210] VARCHAR(50) NULL ,
[Column211] VARCHAR(255) NULL ,
[Column212] VARCHAR(50) NULL ,
[Column213] VARCHAR(255) NULL ,
[Column214] VARCHAR(50) NULL ,
[Column215] VARCHAR(50) NULL ,
[Column216] VARCHAR(50) NULL ,
[Column217] VARCHAR(50) NULL ,
[Column218] VARCHAR(50) NULL ,
[Column219] VARCHAR(50) NULL ,
[Column220] VARCHAR(50) NULL ,
[Column221] VARCHAR(50) NULL ,
[Column222] DATETIME NULL ,
[Column223] VARCHAR(50) NULL ,
[Column224] VARCHAR(50) NULL ,
[Column225] CHAR NULL ,
[Column226] CHAR NULL ,
[Column227] CHAR NULL ,
[Column228] CHAR NULL ,
[Column229] CHAR NULL ,
[Column230] CHAR NULL ,
[Column231] VARCHAR(50) NULL ,
[Column232] VARCHAR(50) NULL ,
[Column233] VARCHAR(50) NULL ,
[Column234] VARCHAR(255) NULL ,
[Column235] VARCHAR(50) NULL ,
[Column236] VARCHAR(50) NULL ,
[Column237] VARCHAR(255) NULL ,
[Column238] VARCHAR(50) NULL ,
[Column239] VARCHAR(255) NULL ,
[Column240] VARCHAR(50) NULL ,
[Column241] VARCHAR(255) NULL ,
[Column242] CHAR NULL ,
[Column243] CHAR NULL ,
[Column244] DATE NULL ,
[Column245] DATE NULL ,
[Column246] DATE NULL ,
[Column247] VARCHAR(50) NULL ,
[Column248] VARCHAR(255) NULL ,
[Column249] VARCHAR(50) NULL ,
[Column250] VARCHAR(255) NULL ,
[Column251] DATE NULL ,
[Column252] DATE NULL ,
CONSTRAINT [PKC_MyTableA] PRIMARY KEY CLUSTERED
(
[Column198] ASC
)
)
GO
CREATE TABLE [dbo].[MyTableB]
(
Column1 [INT] IDENTITY(1,1) NOT NULL,
Column2 [INT] NULL,
Column3 [VARCHAR](255) NOT NULL,
Column4 [VARCHAR](255) NULL,
Column5 [CHAR](1) NOT NULL,
Column6 [VARCHAR](MAX) NULL,
Column7 [VARCHAR](50) NULL,
CONSTRAINT [PK_MyTableB] PRIMARY KEY CLUSTERED
(
Column3 ASC
)
)
GO
Insira alguns dados fictícios:
DECLARE @valsSQL NVARCHAR(MAX) = 'SET IDENTITY_INSERT MyTableA ON;
INSERT INTO [MyTableA] ('
SELECT @valsSQL += c.name + ','
FROM sys.columns c
JOIN sys.tables t
ON c.object_id = t.object_id
WHERE t.name = 'MyTableA'
ORDER BY column_id
SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')
SET @valsSQL += ' VALUES ( '
SELECT @valsSql +=
CASE
WHEN c.system_type_id = 167 OR --varchar
c.system_type_id = 175 -- char
THEN '''' + REPLICATE('a',c.max_length) + ''''
WHEN c.system_type_id = 61
THEN '''' + CONVERT(NVARCHAR,GETDATE(),120) + ''''
WHEN c.system_type_id = 56 OR --int OR
c.system_type_id = 47 OR -- bigint
c.system_type_id = 127
THEN CONVERT(NVARCHAR(10),CONVERT(INT,FLOOR(RAND()*2147483647)))
WHEN c.system_type_id = 40
THEN '''' + '1900-01-01' + ''''
END + ','
FROM sys.columns c
JOIN sys.types t
ON c.system_type_id = t.system_type_id
WHERE OBJECT_NAME(object_id) = 'MyTableA'
ORDER BY column_id
SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')
SET @valsSQL += '; SET IDENTITY_INSERT MyTableA OFF;'
EXEC sp_executesql @stmt = @valsSQL
GO 500
Agora que o banco de dados está configurado, execute a consulta:
USE NO_PLAN
SELECT 1
-- my unique text to find this query in query store views
FROM MyTableA
INNER JOIN MyTableB Alias
ON Alias.Column3 = 'value'
LEFT JOIN MyTableB
ON MyTableB.Column3 = 'value'
WHERE MyTableB.Column4 IS NULL
NB - o plano de execução real está aqui
use os DMVs do repositório de consultas para obter o ID da consulta e o ID do plano para que possamos forçar o plano:
SELECT t.query_sql_text,
q.query_id,
p.plan_id,
p.query_plan,
p.is_forced_plan,
p.last_force_failure_reason_desc,
p.last_execution_time
FROM sys.query_store_plan p
JOIN sys.query_store_query q
ON q.query_id = p.query_id
JOIN sys.query_store_query_text t
ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE '%-- my unique text to find this query in query store views%' AND
t.query_sql_text NOT LIKE '%sys.query_store_plan%' /* exclude this query */
Minha saída está abaixo:
Agora force o SQL Server a usar o plano que acabou de compilar, cada vez que executar esta consulta
EXEC sp_query_store_force_plan @query_id = 6, @plan_id = 6
Execute a consulta novamente:
USE NO_PLAN
SELECT 1
-- my unique text to find this query in query store views
FROM MyTableA
INNER JOIN MyTableB Alias
ON Alias.Column3 = 'value'
LEFT JOIN MyTableB
ON MyTableB.Column3 = 'value'
WHERE MyTableB.Column4 IS NULL
Verifique os DMVs do repositório de consultas para ver se ele usou o plano:
SELECT t.query_sql_text,
q.query_id,
p.plan_id,
p.query_plan,
p.is_forced_plan,
p.last_force_failure_reason_desc,
p.last_execution_time
FROM sys.query_store_plan p
JOIN sys.query_store_query q
ON q.query_id = p.query_id
JOIN sys.query_store_query_text t
ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE '%-- my unique text to find this query in query store views%' AND
t.query_sql_text NOT LIKE '%sys.query_store_plan%' /* exclude this query */
We can see a failure reason of NO_PLAN:
If I reset things by truncating the table, clearing query store and then adding only 20 rows to the table (or dropping the database and running all the above setup above but use GO 20 rather than GO 500):
USE NO_PLAN;
ALTER DATABASE NO_PLAN SET QUERY_STORE CLEAR;
TRUNCATE TABLE [MyTableA];
DECLARE @valsSQL NVARCHAR(MAX) = 'SET IDENTITY_INSERT MyTableA ON;
INSERT INTO [MyTableA] ('
SELECT @valsSQL += c.name + ','
FROM sys.columns c
JOIN sys.tables t
ON c.object_id = t.object_id
WHERE t.name = 'MyTableA'
ORDER BY column_id
SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')
SET @valsSQL += ' VALUES ( '
SELECT @valsSql +=
CASE
WHEN c.system_type_id = 167 OR --varchar
c.system_type_id = 175 -- char
THEN '''' + REPLICATE('a',c.max_length) + ''''
WHEN c.system_type_id = 61
THEN '''' + CONVERT(NVARCHAR,GETDATE(),120) + ''''
WHEN c.system_type_id = 56 OR --int OR
c.system_type_id = 47 OR -- bigint
c.system_type_id = 127
THEN CONVERT(NVARCHAR(10),CONVERT(INT,FLOOR(RAND()*2147483647)))
WHEN c.system_type_id = 40
THEN '''' + '1900-01-01' + ''''
END + ','
FROM sys.columns c
JOIN sys.types t
ON c.system_type_id = t.system_type_id
WHERE OBJECT_NAME(object_id) = 'MyTableA'
ORDER BY column_id
SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')
SET @valsSQL += '; SET IDENTITY_INSERT MyTableA OFF;'
EXEC sp_executesql @stmt = @valsSQL
GO 20
then once again run the query, I get a different plan (note the filter operator placement has changed)
If I then repeat the process of getting the query_id and plan_id, forcing the plan and re-running the query, this time it will force the plan:
I can confirm that NO_PLAN plan cannot be forced with OPTION (RECOMPILE, USE PLAN N'<planxmlhere>') hints, I get
Msg 8698, Level 16, State 0, Line 5 Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.
A number of articles suggest that the NO_PLAN failure reason is due to changing indexes, however, as can be seen from the example above, nothing has changed between forcing and running the query for the second time.
Why can SQL server not be forced to use a plan it just generated, when nothing has changed? What is it about the first plan that causes the forcing to fail an why is that not an issue for the second plan?
Not every plan SQL Server can generate is capable of being forced, as the error message suggests (emphasis added):
This is a consequence of the way plan search is guided by the supplied xml. SQL Server uses the guide to choose transformation rules that could possibly result in the operators and properties provided. When all goes well, a plan with the same major features as the supplied xml representation is produced, though it may differ in minor details such as filter and compute scalar placement:
SQL Server still goes through a very similar sort of process to that involved in finding the original plan. It's a step-by-step process of applying transformations and substitutions to the original logical tree representation, taking account of the hinted plan shape. Many things can go wrong during these steps, meaning SQL Server doesn't end up anywhere close to the desired finishing point.
The following is a broad overview of the guided search:
I mention all this because people don't generally appreciate the xml is a representation of the internal executable plan, not the plan itself. SQL Server cannot directly convert the xml into all the right internal structures. It has to go through a search process, using the xml as a rough guide.
The point I'm trying to make is, despite what the documentation says, plan forcing is not an exact procedure with minimal (and completely documented) failure modes. The original plan guides were not widely used, and failures were easily explained (or handwaved) away. The increasing popularity of Query Store, with its version of plan guiding, is increasing the level of general experience with this feature.
The likelihood of a failure increases as the statement relies on more complex interactions of query optimizer transformations. Your example query is a good illustration of that as it employs complex selection on outer join/join switching:
That complex rewrite might not (always) work well with guided search, since it doesn't generally consider filter position as already noted.
In your second example with only 20 rows, the optimizer's plan search ends after the search 0 (transaction processing) stage, due to the lower expected plan cost. Search 0 does not allow the SelOJJoinSwitch rule. The placement of the filter (selection) in the plan is different as a consequence of not running that exploration.
If you disable search 0 with undocumented trace flag 8750, you get the same unforceable plan with the 20-row test.
The original SQL is also an odd expression of the query requirement, with a cross join masquerading as an inner join with a selection on one table in its
ONclause.The supplied plan can be forced with the following equivalent rewrites:
I used a
USE PLANhint, but any of those rewrites will generate the same plan, forceable using Query Store.The parentheses are important in the
CROSS JOINandINNER JOINvariants because of binding and precedence rules. The initial logical tree derived from the statement text needs to start from a point that makes the final plan reachable. These three are examples of such starting points, your original is not.The dependability of plan guiding may increase with SQL Server 2022 as it includes a limited optimizer rule replay capability. This may or may not turn out to be superior to guiding based on the features found in the xml representation of the plan.
Finalmente, é sempre possível que uma falha de orientação como esta seja devido a um defeito do produto. Você precisaria entrar em contato com a Microsoft para obter uma resposta definitiva para isso (embora você ainda não consiga uma). Se for um defeito, é provável que envolva a regra SelOJJoinSwitch .
Exemplo mínimo
O seguinte reproduz o problema:
Isso produzirá um plano que não pode ser forçado usando uma
USE PLANdica. Substituir a(SELECT 1)subconsulta pela constante 1 produz um plano forçado gerado na fase de pesquisa 0 . Alternativamente, desabilitar a regra comOPTION (QUERYRULEOFF SelOJJoinSwitch)também produz um plano forçado.As reescritas acima também são aplicáveis ao exemplo mínimo:
Isso produz um plano forçado com a mesma estrutura sem envolver SelOJJoinSwitch :