查詢計劃問題

July 10, 2018

我創建了一個 varbinary 雜湊來檢查 2 個表之間的更改。
這是執行計劃，我對索引有點難過，或者確實有更好的編寫方法。 https://www.brentozar.com/pastetheplan/?id=HkHmqoczm
連接中的 2 列是目標中的 PK，並且在源中具有非聚集索引。困擾我的一點是排序導致的 tempdb 溢出。

行儲存
排序溢出本身可以通過啟用跟踪標誌 7470 來解決。請參閱修復：當估計的行數和行大小正確時，排序運算符溢出到 tempdb。此跟踪標誌糾正了計算中的疏忽。它使用起來非常安全，在我看來應該預設開啟。更改受跟踪標誌保護只是為了避免意外的計劃更改。
也就是說，正如 Rob Farley 在回答中提到的那樣，完全避免這種情況會更好。更改分群鍵是實現此目的的一種方法，但它可能不是最佳選擇。
SQL Server 選擇不使用非聚集索引來避免排序，因為該索引不提供更新可能需要的其他列。使用提示強制該索引將產生具有大量鍵查找的計劃。高估計成本解釋了為什麼優化器更喜歡排序。
優化器目前無法自行考慮的另一種方法是查找將要更新的行的鍵，然後僅為這些行獲取附加列（通過查找類型的操作）。提供的計劃顯示沒有更新行。如果這是常見情況，或者至少有一小部分行符合更新條件，那麼顯式編碼該邏輯可能是值得的。
執行計劃中的另一個問題是每個目標更新行可能與多個源行相關聯。這就是ANYStream Aggregate 運算符中存在聚合的原因。給定連接鍵上的多個匹配項（以及雜湊上的不匹配項），將用於更新的行是不確定的。
如果更新被寫為MERGE，當遇到多個源行時會拋出錯誤。通常最好編寫確定性更新，其中每個目標行最多與一個源行相關聯。
例子
該問題沒有提供 DDL 或太多背景知識，因此以下是一個簡單的近似值，其中所有非鍵列都由單個大列表示，並且從計劃中推斷出基數和索引：
DROP TABLE IF EXISTS 
   dbo.dwSource, 
   dbo.dwTarget;

CREATE TABLE dbo.dwSource
(
   loadkey bigint NOT NULL,
   mytableid integer NOT NULL,
   ppw_id integer NOT NULL,
   other_columns varchar(1000) NOT NULL,
   row_hash binary(20) NOT NULL,

   CONSTRAINT PK_dbo_dwSource
       PRIMARY KEY CLUSTERED (loadkey),
);

CREATE TABLE dbo.dwTarget
(
   mytableid integer NOT NULL,
   ppw_id integer NOT NULL,
   other_columns varchar(1000) NOT NULL,
   row_hash binary(20) NOT NULL,

   CONSTRAINT PK_dbo_dwTarget
       PRIMARY KEY CLUSTERED (ppw_id, mytableid)
);

UPDATE STATISTICS dbo.dwSource 
WITH ROWCOUNT = 1295450, PAGECOUNT = 100000;

UPDATE STATISTICS dbo.dwTarget 
WITH ROWCOUNT = 1296390, PAGECOUNT = 100000;
鑑於該近似模式（忽略源和目標上的非聚集索引），目前更新語句是：
UPDATE DT
SET DT.other_columns = DS.other_columns
FROM dbo.dwSource AS DS
JOIN dbo.dwTarget AS DT
   ON DT.ppw_id = DS.ppw_id
   AND DT.mytableid = DS.mytableid
WHERE DS.row_hash &lt;&gt; DT.row_hash;
給予：
如前所述，如果要更新的行數相對較少，則可能值得僅將鍵定位作為第一步。為了達到最佳效果，我們需要幾個非聚集索引，它們可能類似於現有的索引：
-- Narrower than the clustered primary key
CREATE UNIQUE INDEX [UQ dbo.dwTarget ppw_id, mytableid (row_hash)]
ON dbo.dwTarget (ppw_id, mytableid) 
INCLUDE (row_hash);

-- Not guaranteed to be unique    
CREATE INDEX [IX dbo.dwSource ppw_id, mytableid (loadkey, row_hash)]
ON dbo.dwSource (ppw_id, mytableid) 
INCLUDE (loadkey, row_hash);
然後我們可以編寫一個查詢來定位更新鍵並確保只有一個源行映射到每個目標行（任意選擇最高的行loadkey）：
-- Find keys for updated rows
SELECT
   DS.ppw_id,
   DS.mytableid,
   loadkey = MAX(DS.loadkey)
INTO #Delta
FROM dbo.dwSource AS DS
WHERE EXISTS
(
   SELECT 1 
   FROM dbo.dwTarget AS DT
   WHERE
       DT.ppw_id = DS.ppw_id
       AND DT.mytableid = DS.mytableid
       AND DT.row_hash &lt;&gt; DS.row_hash
)
GROUP BY
   DS.ppw_id,
   DS.mytableid;
如果測試表明此查詢將受益於並行性，您可以添加一個OPTION (USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE'))提示來給出：
現在我們有了密鑰，我們可以使用以下方法告訴優化器唯一性：
ALTER TABLE #Delta
ADD PRIMARY KEY CLUSTERED (ppw_id, mytableid);
最後的更新是：
UPDATE DT
SET DT.other_columns = DS.other_columns
FROM #Delta AS DEL
JOIN dbo.dwTarget AS DT WITH (INDEX(1))
   ON DT.ppw_id = DEL.ppw_id
   AND DT.mytableid = DEL.mytableid
JOIN dbo.dwSource AS DS
   ON DS.loadkey = DEL.loadkey;
這確保了非鍵列只查找實際將被更新的行。WITH (INDEX(1))提示確保可以使用行集共享（因此索引查找直接提供更新的位置）。如果測試表明優化器自然選擇的替代計劃在實踐中更好，則可以省略。請注意，在這裡選擇嵌套循環很重要。您可能需要使用類似OPTION (FAST 1). 如果更新的行數確實總是很小的一部分，那麼優化器應該自然地選擇嵌套循環計劃。
列儲存
關鍵位置計劃（使用右半合併連接）仍然非常昂貴，因為兩個表中的所有行都被讀取和測試。
如果您對索引有完全的自由（並且沒有其他重大缺點），則可以通過靜態（未更新）列上的幾個輔助列儲存索引獲得潛在的最佳計劃：
CREATE NONCLUSTERED COLUMNSTORE INDEX nccsi 
ON dbo.dwSource (ppw_id, mytableid, loadkey, row_hash);

CREATE NONCLUSTERED COLUMNSTORE INDEX nccsi 
ON dbo.dwTarget (ppw_id, mytableid, row_hash);
這使得關鍵位置計劃：
以散列的記憶體授予為代價，該計劃為所有運算符（並行插入除外）提供純批處理模式操作，並提供早期點陣圖半連接減少。這可能會執行得非常快。
使用批處理模式執行時，列儲存性能最令人印象深刻，並且數據以最佳方式排列在高度壓縮的段中。與行儲存相比，數據更改可能更慢，並且由於刪除的行點陣圖和行保存在行儲存增量段中，可能會導致性能下降。選擇和維護最佳的列儲存配置不一定是微不足道的，請參閱從列儲存索引 - 概述開始的文件。
這些是您根據自己的情況進行調查和應用（或不應用）的主要替代方案。
您還可以考慮將雜湊碼列設為固定長度binary而不是varbinary，假設您使用的任何雜湊實現都會產生固定長度的結果。我還假設您很樂意接受散列未檢測到更改的可能性很小。