為什麼加入消除不能與 sys.query_store_plan 一起使用？

以下是查詢儲存遇到的性能問題的簡化：

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

執行計劃

為什麼會這樣，以及可以做些什麼來獲得連接消除？

該文件有點誤導。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(-) ║
╚════════════════════════╩══════════════════════════════════════╩═════════════╝

所以plan_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最終結果中的重複值，以允許優化器在這裡發揮其魔力。

引用自：https://dba.stackexchange.com/questions/216485