主页 / dba / 问题 / 216485
Accepted
Paul White
Asked: 2018-09-02 06:22:32 +0800 CST

为什么加入消除与 sys.query_store_plan 不起作用？

  • 772

以下是查询存储遇到的性能问题的简化：

CREATE TABLE #tears
(
    plan_id bigint NOT NULL
);

INSERT #tears (plan_id) 
VALUES (1);

SELECT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

该plan_id列被记录为 的主键sys.query_store_plan，但执行计划没有像预期的那样使用连接消除：

  1. DMV 没有投影任何属性。
  2. DMV 主键plan_id不能复制临时表中的行
  3. 使用了 A ，因此无法删除LEFT JOIN任何行。T

执行计划

平面图

这是为什么，如何才能在此处获得连接消除？

sql-server performance

1 个回答

  1. Best Answer

    该文档有点误导。DMV 是非物化视图，因此没有主键。底层定义有点复杂，但简化的定义sys.query_store_plan是：

    CREATE VIEW sys.query_store_plan AS
SELECT
    PPM.plan_id
    -- various other attributes
FROM sys.plan_persist_plan_merged AS PPM
LEFT JOIN sys.syspalvalues AS P
    ON P.class = 'PFT' 
    AND P.[value] = plan_forcing_type;

    此外，sys.plan_persist_plan_merged也是一个视图，尽管需要通过专用管理员连接进行连接才能查看其定义。再次，简化：

    CREATE VIEW sys.plan_persist_plan_merged AS   
SELECT     
    P.plan_id as plan_id,    
    -- various other attributes
FROM sys.plan_persist_plan P 
    -- NOTE - in order to prevent potential deadlock
    -- between QDS_STATEMENT_STABILITY LOCK and index locks   
    WITH (NOLOCK) 
LEFT JOIN sys.plan_persist_plan_in_memory PM
    ON P.plan_id = PM.plan_id;

    上的索引sys.plan_persist_plan是：

    ╔════════════════════════╦════════════════════════ ══════════════╦═════════════╗
║ index_name ║ index_description ║ index_keys ║
╠════════════════════════╬════════════════════════ ══════════════╬═════════════╣
║ plan_persist_plan_cidx ║ 集群，唯一位于 PRIMARY ║ plan_id ║
║ plan_persist_plan_idx1 ║ 位于 PRIMARY 上的非集群 ║ query_id(-) ║
╚════════════════════════╩════════════════════════ ══════════════╩═════════════╝

    Soplan_id被限制在 上是唯一的sys.plan_persist_plan。

    现在，sys.plan_persist_plan_in_memory是一个流式表值函数，呈现仅保存在内部存储器结构中的数据的表格视图。因此，它没有任何独特的约束。

    因此，本质上，正在执行的查询等同于：

    DECLARE @t1 table (plan_id integer NOT NULL);
DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT 
    T1.plan_id
FROM @t1 AS T1 
LEFT JOIN
(
    SELECT 
        T2.plan_id
    FROM @t2 AS T2
    LEFT JOIN @t3 AS T3 
        ON T3.plan_id = T2.plan_id
) AS Q1
    ON Q1.plan_id = T1.plan_id;

    ...不会产生连接消除：

    在此处输入图像描述

    切入问题的核心，问题是内部查询：

    DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT 
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3 
    ON T3.plan_id = T2.plan_id;

    ...很明显，左连接可能会导致行@t2被复制，因为@t3对 没有唯一性约束plan_id。因此，无法消除连接：

    在此处输入图像描述

    为了解决这个问题，我们可以明确地告诉优化器我们不需要任何重复plan_id值：

    DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT DISTINCT
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3 
    ON T3.plan_id = T2.plan_id;

    @t3现在可以消除外部连接：

    在此处输入图像描述

    将其应用于实际查询：

    SELECT DISTINCT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

    同样，我们可以添加GROUP BY T.plan_id而不是DISTINCT. plan_id无论如何，优化器现在可以通过嵌套视图一直正确地推断属性，并根据需要消除两个外部连接：

    在此处输入图像描述

    请注意，使plan_id临时表中的唯一性不足以获得连接消除，因为它不会排除不正确的结果。我们必须明确拒绝plan_id最终结果中的重复值，以允许优化器在这里发挥其魔力。

    • 16

相关问题