Jak mogę pozbyć się nieprzydatnej gałęzi równoległej, gdy odwracam pojedynczy wiersz?

Rozważ następujące zapytanie, które rozdziela kilka garstek agregatów skalarnych:

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);

Na SQL Server 2017 otrzymuję plan z dwoma równoległymi gałęziami. Lewa równoległa gałąź wydaje mi się nie na miejscu. Optymalizator ma gwarancję, że globalny agregat skalarny będzie generował tylko jeden wiersz, ale jego operatorem nadrzędnym jest dystrybucja strumieni z partycjonowaniem okrągłym:

Kiedy wykonuję zapytanie, wszystkie wiersze przechodzą do jednego wątku zgodnie z oczekiwaniami. Z tym zapytaniem nie ma problemu z wydajnością, ale zapytanie rezerwuje 8 równoległych wątków z MAXDOP ustawionym na 4. Ponownie czuję, że to nie na miejscu. Niemożliwe jest jednoczesne wykonanie obu równoległych gałęzi. Chcę uniknąć niepotrzebnej rezerwacji wątków roboczych, ponieważ mam włączony TF 2467, który zmienia algorytm szeregowania, aby sprawdzić liczbę wątków roboczych na harmonogram.

Czy możliwe jest przepisanie zapytania, tak aby zawierało dokładnie jedną gałąź równoległą, która zawiera skanowanie tabeli i lokalną agregację? Na przykład byłbym w porządku z ogólnym kształtem poniżej, z tym wyjątkiem, że chcę, aby zagnieżdżona pętla była wykonywana w strefie szeregowej:

W przypadku powodów związanych z aplikacją zdecydowanie wolę unikać dzielenia tego zapytania na części. W razie potrzeby możesz wyświetlić aktualny plan zapytań tutaj . Jeśli chcesz grać razem w domu, oto T-SQL do utworzenia tabeli używanej w zapytaniu:

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;

Jestem w stanie uzyskać pożądany kształt planu za pomocą połączenia szeregowego, gdy spełnione są wszystkie poniższe warunki:

• APPLYLub CROSS JOINjest używana zamiastUNPIVOT
• Nie APPLYzawiera żadnych odnośników zewnętrznych
• Źródłem wierszy w APPLYjest konstruktor wartości tabeli, a nie tabela

Oto na przykład jeden ze sposobów:

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);

Otrzymuję żądany kształt planu planu, jak twierdzono, za pomocą tylko jednej równoległej gałęzi:

Próbowałem wielu innych rzeczy, które nie działały. Ta odpowiedź jest niezadowalająca, ponieważ nie wiem, dlaczego to działa i może nie działać w przyszłej wersji programu SQL Server, ale rozwiązało mój problem.

Niemożliwe jest jednoczesne wykonanie obu równoległych gałęzi.

Realizacja rozpoczyna się od lewej krawędzi planu. Oddział zagnieżdżonej pętli działa (otwieranie, oczekiwanie na dane), gdy działa gałąź skanowania tabeli. Jest to nieuniknione . Obie gałęzie są jednocześnie aktywne, więc SQL Server zarezerwuje 2 * pracowników DOP dla tego planu.

Aby uzyskać solidne rozwiązanie, możesz umieścić oś przestawną w funkcji wycenianej w tabeli:

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;

Następnie przepisz zapytanie jako:

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;

Funkcja używa równoległości z jedną gałęzią według potrzeb:

Plan wykonania najwyższego poziomu to: