取消旋轉單行時，如何擺脫無用的並行分支？

考慮以下查詢，它取消了少量的標量聚合：

SELECT A, B
FROM (
   SELECT 
     MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
   , MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
   , MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
   , MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
   , MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
   , MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
   , MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
   , MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
   FROM dbo.PARALLEL_ZONE_REPRO
) q
UNPIVOT(B FOR A IN (
   VAL1
   ,VAL2
   ,VAL3
   ,VAL4
   ,VAL5
   ,VAL6
   ,VAL7
   ,VAL16
)) U
OPTION (MAXDOP 4);

在 SQL Server 2017 上，我得到了一個包含兩個並行分支的計劃。左側平行分支對我來說感覺格格不入。優化器保證全域標量聚合只有單行輸出，但它的父運算符是具有循環分區的 Distribute Streams：

當我執行查詢時，所有行都按預期轉到單個執行緒。這個查詢沒有性能問題，但是查詢保留了 8 個並行執行緒，MAXDOP 設置為 4。再一次，我覺得這不合適。兩個並行分支不可能同時執行。我想避免不必要的工作執行緒預留，因為我啟用了 TF 2467，它會更改調度算法以查看每個調度程序的工作執行緒數。

是否可以將查詢重寫為恰好有一個包含表掃描和本地聚合的並行分支？例如，我可以使用下面的一般形狀，但我希望嵌套循環在串列區域中執行：

對於 Application Reasons™，我強烈希望避免將此查詢拆分為多個部分。如果需要，您可以在此處查看實際的查詢計劃。如果你想在家一起玩，這裡是 T-SQL 來創建查詢中使用的表：

DROP TABLE IF EXISTS dbo.PARALLEL_ZONE_REPRO;

CREATE TABLE dbo.PARALLEL_ZONE_REPRO (
   ID BIGINT,
   FILLER VARCHAR(100)
);

INSERT INTO dbo.PARALLEL_ZONE_REPRO WITH (TABLOCK)
SELECT
 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 15
, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

當滿足以下所有條件時，我能夠通過串列循環連接獲得所需的計劃形狀：

• 使用APPLYorCROSS JOIN代替UNPIVOT
• 不APPLY包含外部引用
• 中的行源APPLY是表值建構子，而不是表

例如，這是一種方法：

SELECT A, B
FROM 
(
   SELECT A
   , MAX(
       CASE
           WHEN A = 'VAL1' THEN VAL1 
           WHEN A = 'VAL2' THEN VAL2
           WHEN A = 'VAL3' THEN VAL3
           WHEN A = 'VAL4' THEN VAL4
           WHEN A = 'VAL5' THEN VAL5
           WHEN A = 'VAL6' THEN VAL6
           WHEN A = 'VAL7' THEN VAL7
           WHEN A = 'VAL16' THEN VAL16
           ELSE NULL
       END
   ) B
   FROM (
        SELECT 
          MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
        , MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
        , MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
        , MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
        , MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
        , MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
        , MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
        , MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
        FROM dbo.PARALLEL_ZONE_REPRO
   ) q
   CROSS APPLY (
       VALUES ('VAL1'), ('VAL2'), ('VAL3'), ('VAL4'),
       ('VAL5'), ('VAL6'), ('VAL7'), ('VAL16') 
   ) ca (A)
   GROUP BY A
) q
WHERE q.B IS NOT NULL
OPTION (MAXDOP 4);

我只用一個平行分支就得到了所需的計劃計劃形狀：

我嘗試了許多其他不起作用的東西。這個答案不能令人滿意，因為我不知道它為什麼會起作用，而且它可能在 SQL Server 的未來版本中不起作用，但它確實解決了我的問題。

兩個並行分支不可能同時執行。

執行從計劃的左邊緣開始。當表掃描分支執行時，嵌套循環分支正在執行（打開、等待數據）。這是不可避免的。兩個分支同時處於活動狀態，因此 SQL Server 將為此計劃保留2 * DOP 工作人員。

對於一個健壯的解決方案，您可以將樞軸放在一個表值函式中：

CREATE OR ALTER FUNCTION dbo.PivotPZR()
RETURNS @R table 
(
   VAL1 bigint NOT NULL, VAL2 bigint NOT NULL,
   VAL3 bigint NOT NULL, VAL4 bigint NOT NULL,
   VAL5 bigint NOT NULL, VAL6 bigint NOT NULL,
   VAL7 bigint NOT NULL, VAL16 bigint NOT NULL
)
WITH SCHEMABINDING AS
BEGIN
   DECLARE 
       @Val1 bigint, @Val2 bigint, @Val3 bigint, @Val4 bigint,
       @Val5 bigint, @Val6 bigint, @Val7 bigint, @Val16 bigint;

   -- Can use parallelism
   SELECT
       @Val1 = MAX(CASE WHEN PZR.ID = 1 THEN 1 ELSE 0 END),
       @Val2 = MAX(CASE WHEN PZR.ID = 2 THEN 1 ELSE 0 END),
       @Val3 = MAX(CASE WHEN PZR.ID = 3 THEN 1 ELSE 0 END),
       @Val4 = MAX(CASE WHEN PZR.ID = 4 THEN 1 ELSE 0 END),
       @Val5 = MAX(CASE WHEN PZR.ID = 5 THEN 1 ELSE 0 END),
       @Val6 = MAX(CASE WHEN PZR.ID = 6 THEN 1 ELSE 0 END),
       @Val7 = MAX(CASE WHEN PZR.ID = 7 THEN 1 ELSE 0 END),
       @Val16 = MAX(CASE WHEN PZR.ID = 16 THEN 1 ELSE 0 END)
   FROM dbo.PARALLEL_ZONE_REPRO AS PZR;

   -- Single result row
   INSERT @R
       (VAL1, VAL2, VAL3, VAL4, VAL5, VAL6, VAL7, VAL16)
   VALUES
       (@Val1, @Val2, @Val3, @Val4, @Val5, @Val6, @Val7, @Val16);

   RETURN;
END;

然後將查詢重寫為：

SELECT
   U.A,
   U.B
FROM dbo.PivotPZR() AS PP
UNPIVOT
(
   B FOR A IN (VAL1, VAL2 ,VAL3 ,VAL4, VAL5 ,VAL6 ,VAL7 ,VAL16)
) AS U;

該函式根據需要使用具有單個分支的並行性：

頂層執行計劃是：

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