SQL Server nie optymalizuje łączenia przez scalanie równoległe na dwóch równo podzielonych na partycje tabelach

Z góry przepraszamy za bardzo szczegółowe pytanie. Dołączyłem zapytania, aby wygenerować pełny zestaw danych do odtworzenia problemu, i korzystam z programu SQL Server 2012 na komputerze z 32 rdzeniami. Jednak nie sądzę, że jest to specyficzne dla SQL Server 2012 i dla tego konkretnego przykładu wymusiłem MAXDOP wynoszący 10.

Mam dwie tabele, które są podzielone na partycje przy użyciu tego samego schematu partycji. Łącząc je razem w kolumnie używanej do partycjonowania, zauważyłem, że SQL Server nie jest w stanie zoptymalizować łączenia równoległego scalania tak bardzo, jak można się spodziewać, i dlatego zamiast tego zdecydował się na użycie HASH JOIN. W tym konkretnym przypadku jestem w stanie ręcznie zasymulować znacznie bardziej optymalną równoległą łączenie MERGE JOIN, dzieląc zapytanie na 10 rozłącznych zakresów w oparciu o funkcję partycji i uruchamiając każde z tych zapytań jednocześnie w SSMS. Używając WAITFOR do uruchomienia ich wszystkich dokładnie w tym samym czasie, wynik jest taki, że wszystkie zapytania kończą się w ~ 40% całkowitego czasu używanego przez oryginalne równoległe ŁĄCZENIE HASH.

Czy jest jakiś sposób, aby SQL Server sam przeprowadził tę optymalizację w przypadku równo podzielonych tabel? Rozumiem, że SQL Server może generalnie wiązać się z dużym nakładem pracy w celu równoległego połączenia MERGE JOIN, ale wydaje się, że w tym przypadku istnieje bardzo naturalna metoda dzielenia fragmentów z minimalnym narzutem. Być może jest to tylko specjalistyczny przypadek, w którym optymalizator nie jest jeszcze wystarczająco sprytny, aby go rozpoznać?

Oto instrukcja SQL umożliwiająca skonfigurowanie uproszczonego zestawu danych w celu odtworzenia tego problemu:

/* Create the first test data table */
CREATE TABLE test_transaction_properties 
    ( transactionID INT NOT NULL IDENTITY(1,1)
    , prop1 INT NULL
    , prop2 FLOAT NULL
    )

/* Populate table with pseudo-random data (the specific data doesn't matter too much for this example) */
;WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
    UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b)
, E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b)
, E8(N) AS (SELECT 1 FROM E4 a CROSS JOIN E4 b)
INSERT INTO test_transaction_properties WITH (TABLOCK) (prop1, prop2)
SELECT TOP 10000000 (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 5) + 1 AS prop1
                , ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * rand() AS prop2
FROM E8

/* Create the second test data table */
CREATE TABLE test_transaction_item_detail
    ( transactionID INT NOT NULL
    , productID INT NOT NULL
    , sales FLOAT NULL
    , units INT NULL
    )

 /* Populate the second table such that each transaction has one or more items
     (again, the specific data doesn't matter too much for this example) */
INSERT INTO test_transaction_item_detail WITH (TABLOCK) (transactionID, productID, sales, units)
SELECT t.transactionID, p.productID, 100 AS sales, 1 AS units
FROM test_transaction_properties t
JOIN (
    SELECT 1 as productRank, 1 as productId
    UNION ALL SELECT 2 as productRank, 12 as productId
    UNION ALL SELECT 3 as productRank, 123 as productId
    UNION ALL SELECT 4 as productRank, 1234 as productId
    UNION ALL SELECT 5 as productRank, 12345 as productId
) p
    ON p.productRank <= t.prop1

/* Divides the transactions evenly into 10 partitions */
CREATE PARTITION FUNCTION [pf_test_transactionId] (INT)
AS RANGE RIGHT
FOR VALUES
(1,1000001,2000001,3000001,4000001,5000001,6000001,7000001,8000001,9000001)

CREATE PARTITION SCHEME [ps_test_transactionId]
AS PARTITION [pf_test_transactionId]
ALL TO ( [PRIMARY] )

/* Apply the same partition scheme to both test data tables */
ALTER TABLE test_transaction_properties
ADD CONSTRAINT PK_test_transaction_properties
PRIMARY KEY (transactionID)
ON ps_test_transactionId (transactionID)

ALTER TABLE test_transaction_item_detail
ADD CONSTRAINT PK_test_transaction_item_detail
PRIMARY KEY (transactionID, productID)
ON ps_test_transactionId (transactionID)

Teraz jesteśmy w końcu gotowi odtworzyć suboptymalne zapytanie!

/* This query produces a HASH JOIN using 20 threads without the MAXDOP hint,
    and the same behavior holds in that case.
    For simplicity here, I have limited it to 10 threads. */
SELECT COUNT(*)
FROM test_transaction_item_detail i
JOIN test_transaction_properties t
    ON t.transactionID = i.transactionID
OPTION (MAXDOP 10)

Jednak użycie jednego wątku do przetworzenia każdej partycji (przykład dla pierwszej partycji poniżej) doprowadziłoby do znacznie bardziej wydajnego planu. Przetestowałem to, uruchamiając zapytanie podobne do poniższego dla każdej z 10 partycji dokładnie w tym samym momencie, a wszystkie 10 zakończyło się w nieco ponad 1 sekundę:

SELECT COUNT(*)
FROM test_transaction_item_detail i
INNER MERGE JOIN test_transaction_properties t
    ON t.transactionID = i.transactionID
WHERE t.transactionID BETWEEN 1 AND 1000000
OPTION (MAXDOP 1)

Masz rację, że optymalizator SQL Server woli nie generować równoległych MERGEplanów łączenia (kosztuje to dość wysoką alternatywę). Równoległe MERGEzawsze wymaga wymiany partycjonowania na obu wejściach łączenia, a co ważniejsze, wymaga zachowania kolejności wierszy na tych wymianach.

Równoległość jest najbardziej wydajna, gdy każdy wątek może działać niezależnie; konserwacja zamówień często prowadzi do częstych oczekiwań synchronizacji i może ostatecznie spowodować rozlanie się wymian w tempdbcelu rozwiązania problemu zakleszczenia w trakcie zapytania.

Problemy te można obejść, uruchamiając wiele wystąpień całego zapytania w jednym wątku, a każdy wątek przetwarza wyłączny zakres danych. Nie jest to jednak strategia, którą optymalizator rozważa natywnie. W tej chwili oryginalny model SQL Server dotyczący równoległości przerywa zapytanie podczas wymiany i uruchamia segmenty planu utworzone przez te podziały na wielu wątkach.

Istnieją sposoby na uruchomienie całych planów zapytań dla wielu wątków w wyłącznych zakresach zestawów danych, ale wymagają one podstępu, z którego nie wszyscy będą zadowoleni (i nie będą obsługiwane przez Microsoft ani nie będą gwarantowane do pracy w przyszłości). Jednym z takich podejść jest iteracja partycji tabeli podzielonej na partycje i przydzielenie każdemu wątkowi zadania utworzenia sumy pośredniej. Wynikiem jest SUMliczba wierszy zwracana przez każdy niezależny wątek:

Uzyskanie numerów partycji jest dość łatwe z metadanych:

DECLARE @P AS TABLE
(
    partition_number integer PRIMARY KEY
);

INSERT @P (partition_number)
SELECT
    p.partition_number
FROM sys.partitions AS p 
WHERE 
    p.[object_id] = OBJECT_ID(N'test_transaction_properties', N'U')
    AND p.index_id = 1;

Następnie używamy tych liczb do sterowania skorelowanym złączeniem ( APPLY) i $PARTITIONfunkcją ograniczania każdego wątku do bieżącego numeru partycji:

SELECT
    row_count = SUM(Subtotals.cnt)
FROM @P AS p
CROSS APPLY
(
    SELECT
        cnt = COUNT_BIG(*)
    FROM dbo.test_transaction_item_detail AS i
    JOIN dbo.test_transaction_properties AS t ON
        t.transactionID = i.transactionID
    WHERE 
        $PARTITION.pf_test_transactionId(t.transactionID) = p.partition_number
        AND $PARTITION.pf_test_transactionId(i.transactionID) = p.partition_number
) AS SubTotals;

Plan zapytań pokazuje MERGEłączenie wykonywane dla każdego wiersza w tabeli @P. Właściwości skanowania indeksu klastrowego potwierdzają, że podczas każdej iteracji przetwarzana jest tylko jedna partycja:

Niestety skutkuje to tylko sekwencyjnym szeregowym przetwarzaniem partycji. Na podanym zestawie danych mój 4-rdzeniowy laptop (hipertekstowany do 8) zwraca poprawny wynik w ciągu 7 sekund z wszystkimi danymi w pamięci.

Aby MERGEpod-plany działały jednocześnie, potrzebujemy planu równoległego, w którym identyfikatory partycji są dystrybuowane w dostępnych wątkach ( MAXDOP), a każdy MERGEpodplan działa w jednym wątku z wykorzystaniem danych w jednej partycji. Niestety optymalizator często decyduje się na porównanie równoległe MERGEze względu na koszty i nie ma udokumentowanego sposobu wymuszenia planu równoległego. Istnieje nieudokumentowany (i nieobsługiwany) sposób przy użyciu flagi śledzenia 8649 :

SELECT
    row_count = SUM(Subtotals.cnt)
FROM @P AS p
CROSS APPLY
(
    SELECT
        cnt = COUNT_BIG(*)
    FROM dbo.test_transaction_item_detail AS i
    JOIN dbo.test_transaction_properties AS t ON
        t.transactionID = i.transactionID
    WHERE 
        $PARTITION.pf_test_transactionId(t.transactionID) = p.partition_number
        AND $PARTITION.pf_test_transactionId(i.transactionID) = p.partition_number
) AS SubTotals
OPTION (QUERYTRACEON 8649);

Teraz plan kwerendy pokazuje, że numery partycji @Psą rozdzielane między wątki na zasadzie rundy. Każdy wątek uruchamia wewnętrzną stronę zagnieżdżonego połączenia pętli dla pojedynczej partycji, osiągając nasz cel równoczesnego przetwarzania danych rozłącznych. Ten sam wynik jest teraz zwracany w ciągu 3 sekund na moich 8 hiper-rdzeniach, przy wszystkich ośmiu przy 100% wykorzystaniu.

Nie polecam niekoniecznie korzystania z tej techniki - patrz moje wcześniejsze ostrzeżenia - ale odpowiada ona na twoje pytanie.

Zobacz mój artykuł, aby poprawić szczegółowe informacje na temat łączenia partycjonowanej tabeli .

Sklep z kolumnami

Ponieważ używasz programu SQL Server 2012 (i zakładając, że jest to wersja Enterprise), masz również opcję użycia indeksu magazynu kolumn. Pokazuje to potencjał łączenia skrótów w trybie wsadowym, gdy dostępna jest wystarczająca ilość pamięci:

CREATE NONCLUSTERED COLUMNSTORE INDEX cs 
ON dbo.test_transaction_properties (transactionID);

CREATE NONCLUSTERED COLUMNSTORE INDEX cs 
ON dbo.test_transaction_item_detail (transactionID);

Po wprowadzeniu tych indeksów zapytanie ...

SELECT
    COUNT_BIG(*)
FROM dbo.test_transaction_properties AS ttp
JOIN dbo.test_transaction_item_detail AS ttid ON
    ttid.transactionID = ttp.transactionID;

... daje w wyniku następujący plan wykonania z optymalizatora bez żadnych podstępów:

Poprawne wyniki w ciągu 2 sekund , ale wyeliminowanie przetwarzania w trybie wiersza dla agregatu skalarnego pomaga jeszcze bardziej:

SELECT
    COUNT_BIG(*)
FROM dbo.test_transaction_properties AS ttp
JOIN dbo.test_transaction_item_detail AS ttid ON
    ttid.transactionID = ttp.transactionID
GROUP BY
    ttp.transactionID % 1;

Zoptymalizowane zapytanie do magazynu kolumn działa w 851ms .

Geoff Patterson utworzył raport o błędzie Partition Wise Joins, ale został zamknięty jako Won't Fix.

Sposób, w jaki optymalizator działa tak, jak myślisz, jest lepszy dzięki wskazówkom dotyczącym zapytań.

W tym przypadku, OPTION (MERGE JOIN)

Lub możesz przejść cały wieprz i użyć USE PLAN