SQL Server 不會優化兩個等效分區表上的並行合併連接

November 10, 2019

提前為非常詳細的問題道歉。我已經包含查詢以生成完整的數據集以重現問題，並且我在 32 核機器上執行 SQL Server 2012。但是，我不認為這是特定於 SQL Server 2012 的，對於這個特定的範例，我已強制 MAXDOP 為 10。
我有兩個使用相同分區方案分區的表。當在用於分區的列上將它們連接在一起時，我注意到 SQL Server 無法像預期的那樣優化並行合併連接，因此選擇使用 HASH JOIN。在這種特殊情況下，我可以通過基於分區函式將查詢拆分為 10 個不相交的範圍並在 SSMS 中同時執行每個查詢來手動模擬更優化的並行 MERGE JOIN。使用 WAITFOR 在完全相同的時間執行它們，結果是所有查詢在原始並行 HASH JOIN 使用的總時間的約 40% 內完成。
在等效分區表的情況下，有什麼方法可以讓 SQL Server 自行進行這種優化？我知道 SQL Server 通常可能會產生大量成本以使 MERGE JOIN 並行，但在這種情況下似乎有一種非常自然的分片方法，成本最小。也許這只是優化器還不夠聰明辨識的特殊情況？
這是設置簡化數據集以重現此問題的 SQL：
/* 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 &lt;= 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)
現在我們終於準備好重現次優查詢了！
/* 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)
但是，使用單個執行緒來處理每個分區（下面的第一個分區範例）將導致更有效的計劃。我通過在完全相同的時刻對 10 個分區中的每一個執行如下查詢來測試這一點，所有 10 個分區都在 1 秒多的時間內完成：
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)

你是對的，SQL Server 優化器不喜歡生成並行MERGE連接計劃（這種替代方案的成本非常高）。並行MERGE總是需要在兩個連接輸入上重新分區交換，更重要的是，它要求在這些交換中保留行順序。
當每個執行緒可以獨立執行時，並行性是最有效的；訂單保存通常會導致頻繁的同步等待，並最終可能導致交換溢出以tempdb解決查詢內死鎖條件。
這些問題可以通過在一個執行緒上執行整個查詢的多個實例來規避，每個執行緒處理一個專有範圍的數據。然而，這不是優化器本機考慮的策略。實際上，用於並行性的原始 SQL Server 模型會在交換時中斷查詢，並在多個執行緒上執行由這些拆分形成的計劃段。
有一些方法可以實現在多個執行緒上在專有數據集範圍內執行整個查詢計劃，但它們需要一些技巧，並不是每個人都會滿意（並且不會得到 Microsoft 的支持或保證將來可以工作）。一種這樣的方法是遍歷分區表的分區，並為每個執行緒分配生成小計的任務。結果是SUM每個獨立執行緒返回的行數：
從元數據中獲取分區號很容易：
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;
然後我們使用這些數字來驅動一個關聯連接 ( APPLY)，以及將$PARTITION每個執行緒限制為目前分區號的函式：
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;
查詢計劃顯示MERGE為 table 中的每一行執行連接@P。聚集索引掃描屬性確認每次迭代只處理一個分區：
不幸的是，這只會導致分區的順序串列處理。在您提供的數據集上，我的 4 核（超執行緒到 8）筆記型電腦在7 秒內返回正確的結果，所有數據都在記憶體中。
為了讓MERGE子計劃同時執行，我們需要一個並行計劃，其中分區 ID 分佈在可用執行緒 ( MAXDOP) 上，每個MERGE子計劃使用一個分區中的數據在單個執行緒上執行。不幸的是，優化器經常MERGE以成本為由決定反對並行，並且沒有記錄的方法來強制並行計劃。有一種未記錄（且不受支持）的方式，使用跟踪標誌 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);
現在，查詢計劃顯示分區號@P以循環方式分佈線上程之間。每個執行緒都在嵌套循環連接的內側執行單個分區，從而實現我們同時處理不相交數據的目標。現在，我的 8 個超核在3 秒內返回相同的結果，所有 8 個超核的使用率均為 100%。
我不建議您一定要使用這種技術 - 請參閱我之前的警告 - 但它確實解決了您的問題。
有關詳細資訊，請參閱我的文章提高分區表連接性能。
列儲存
看到您使用的是 SQL Server 2012（並假設它是 Enterprise），您還可以選擇使用列儲存索引。這顯示了在有足夠記憶體可用的情況下批處理模式雜湊連接的潛力：
CREATE NONCLUSTERED COLUMNSTORE INDEX cs 
ON dbo.test_transaction_properties (transactionID);

CREATE NONCLUSTERED COLUMNSTORE INDEX cs 
ON dbo.test_transaction_item_detail (transactionID);
有了這些索引，查詢…
SELECT
   COUNT_BIG(*)
FROM dbo.test_transaction_properties AS ttp
JOIN dbo.test_transaction_item_detail AS ttid ON
   ttid.transactionID = ttp.transactionID;
…從優化器中得到以下執行計劃，沒有任何技巧：
在2 秒內正確結果，但消除標量聚合的行模式處理有助於更多：
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;
優化的列儲存查詢在851ms中執行。
Geoff Patterson 創建了錯誤報告Partition Wise Joins，但由於無法修復而被關閉。