SQL Server - 使用聚集索引时如何存储数据页

Question

SEarle1986

Asked: 2022-09-28 08:07:56 +0800 CST2022-09-28 08:07:56 +0800 CST 2022-09-28 08:07:56 +0800 CST

查询存储计划强制失败，NO_PLAN 取决于过滤器运算符在计划中的位置

772

我有一个查询，我在查询存储中强制执行一个计划（该计划是为此查询编译的一个 SQL Server）如果我在强制执行该计划后立即运行查询，NO_PLAN尽管数据库没有更改，但我会得到 last_force_failure_reason_desc。我可以成功地为同一个查询强制执行不同的计划

问题如下图所示：

创建我们的测试数据库

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

插入一些虚拟数据：

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

现在数据库已经建立，运行查询：

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 - 实际的执行计划在这里

使用查询存储 DMV 获取查询 ID 和计划 ID，以便我们可以强制执行计划：

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

我的输出如下：

现在强制 SQL 服务器使用它刚刚编译的计划，每次它运行这个查询

EXEC sp_query_store_force_plan @query_id = 6, @plan_id = 6

再次运行查询：

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

检查查询存储 DMV 以查看它是否使用了计划：

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

我们可以看到 NO_PLAN 的失败原因：

如果我通过截断表、清除查询存储然后只向表中添加 20 行（或删除数据库并运行上述所有设置但使用GO 20而不是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

然后再次运行查询，我得到了不同的计划（注意过滤器运算符的位置已更改）

如果我然后重复获取 query_id 和 plan_id 的过程，强制执行计划并重新运行查询，这一次它将强制执行计划：

我可以确认 NO_PLAN 计划不能通过OPTION (RECOMPILE, USE PLAN N'<planxmlhere>')提示强制执行，我明白了

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?

1 个回答

Voted

Paul White · Answer 1 · 2022-10-14T10:24:44+08:00

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.

最后，像这样的引导失败总是有可能是由于产品缺陷造成的。您需要联系 Microsoft 以获得明确的答案（尽管您可能仍然没有得到答案）。如果是缺陷，则很可能涉及SelOJJoinSwitch规则。

最小的例子

以下重现了该问题：

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;

这将产生一个无法使用USE PLAN提示强制执行的计划。将子查询替换为(SELECT 1)常量 1 会生成在搜索 0阶段生成的强制计划。或者，禁用规则OPTION (QUERYRULEOFF SelOJJoinSwitch)也会产生一个强制计划。

上面的重写也适用于最小示例：

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;

这会产生一个具有相同结构且不涉及SelOJJoinSwitch的强制计划：

查询存储计划强制失败，NO_PLAN 取决于过滤器运算符在计划中的位置

最小的例子

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

查询存储计划强制失败，NO_PLAN 取决于过滤器运算符在计划中的位置

1 个回答

最小的例子

相关问题