Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan

Question

I have a query which I force a plan for in Query Store (the plan is the one SQL Server compiled for this query) If I run the query immediately afer forcing the plan, I get the NO_PLAN last_force_failure_reason_desc despite no changes to the database. I can force a different plan for the same query successfully

The problem can be illustrated below:

Create our test database

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

Insert some dummy data:

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

Now the database is set up, run the query:

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 - the actual execution plan is here

use the query store DMVs to get the query id and plan id so we can force the plan:

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

My output is below:

Now force SQL server to use the plan it just compiled, each time it runs this query

EXEC sp_query_store_force_plan @query_id = 6, @plan_id = 6

Run the query again:

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

Check the query store DMVs to see if it used the plan:

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.

Article A

Article B

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?

Paul White · Accepted Answer · 2022-10-16 08:49:53Z

Not every plan SQL Server can generate is capable of being forced, as the error message suggests (emphasis added):

Msg 8698, Level 16, State 0, Line xxx
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.

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:

The resulting execution plan forced by this feature will be the same or similar to the plan being forced. Because the resulting plan may not be identical to the plan specified by the plan guide, the performance of the plans may vary. In rare cases, the performance difference may be significant and negative; in that case, the administrator must remove the forced plan.

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:

SelOJJoinSwitch - Sel((A JN B) OJ C) -> (Sel(A OJ C)) JN B

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 ON clause.

The supplied plan can be forced with the following equivalent rewrites:

-- Rewrite 1
SELECT 1 
FROM dbo.MyTableA, dbo.MyTableB AS Alias
LEFT JOIN dbo.MyTableB ON MyTableB.Column3 = 'value'
WHERE dbo.MyTableB.Column4 IS NULL

-- Rewrite 2
SELECT 1 
FROM dbo.MyTableA 
CROSS JOIN 
( 
    dbo.MyTableB AS Alias
    LEFT JOIN dbo.MyTableB
        ON MyTableB.Column3 = 'value'
)
WHERE dbo.MyTableB.Column4 IS NULL

-- Rewrite 3
SELECT 1
FROM MyTableA 
JOIN 
(
    MyTableB Alias              
    LEFT JOIN MyTableB ON MyTableB.Column3 =  'value'
) ON Alias.Column3 = 'value'
WHERE
    MyTableB.Column4 IS NULL

I used a USE PLAN hint, but any of those rewrites will generate the same plan, forceable using Query Store.

The parentheses are important in the CROSS JOIN and INNER JOIN variants 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.

Finally, it is always possible a guiding failure like this is due to a product defect. You would need to contact Microsoft for a definitive answer to that (though you still might not get one). If it is a defect, it's likely to involve the SelOJJoinSwitch rule.

Minimal example

The following reproduces the issue:

DECLARE @A table (c1 integer NULL);
DECLARE @B table (c1 integer NULL);
DECLARE @C table (c1 integer NULL);

SELECT (SELECT 1)
FROM @A AS A
CROSS JOIN @B AS B
LEFT JOIN @C AS C
    ON C.c1 = B.c1
WHERE C.c1 IS NULL;

That will produce a plan which is not forceable using a USE PLAN hint. Replacing the (SELECT 1) subquery with constant 1 produces a forceable plan generated in the search 0 phase. Alternatively, disabling the rule with OPTION (QUERYRULEOFF SelOJJoinSwitch) also produces a forceable plan.

The rewrites above are also applicable to the minimal example:

DECLARE @A table (c1 integer NULL);
DECLARE @B table (c1 integer NULL);
DECLARE @C table (c1 integer NULL);

SELECT (SELECT 1)
FROM @A AS A, @B AS B -- Changed cross join syntax
LEFT JOIN @C AS C
    ON C.c1 = B.c1
WHERE C.c1 IS NULL;

That produces a forceable plan with the same structure without involving SelOJJoinSwitch:

Thankyou - as always, there's much more to this than meets the eye — SE1986, Oct 17, 2022 at 15:40

Stack Exchange Network

Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan

1 Answer 1

Minimal example

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2016
execution-plan
query-store
hints
or ask your own question.

Linked

Hot Network Questions

Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan

1 Answer 1

Minimal example

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2016execution-planquery-storehints or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2016
execution-plan
query-store
hints
or ask your own question.