使用雜湊流不同運算符查詢調整更新

UPDATE需要一些幫助來理解以下語句之一的緩慢性：-

UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK)
on xyz.id=abc.id
WHERE xyz.flag = 0

表1有大約。50 萬行，表 2 大約有 50 萬行。500 萬行

慢計劃

Hash Match distinct flow operator 顯示黃色警報，消息為：

操作員使用 Tempdb 溢出數據以執行溢出級別 4 和 1 個溢出執行緒"

建構殘差：

database.dbo.table2.id as abc.id = database.dbo.table2.id as abc.id

我截圖了。不幸的是，由於安全原因，我無法提供更多資訊，甚至沒有匿名計劃。從我的工作站我無法訪問網際網路，所以我無法讓計劃資源管理器在那裡執行。

通常對於較小的行子集，它低於 sec，就像我們剛剛匹配 10K 行或其他東西一樣。但是隨著數據量的增加，這似乎是一個臨界點，應用程序無法承受 1 分鐘的執行時間。從 SSMS 我得到 30 秒，但從應用程序我們有 avg。約 50 秒 RCSI 處於測試階段。

我的好計劃沒有顯示 Hash Match Flow Distinct 運算符，如我的螢幕截圖所示，而其餘計劃保持不變。好的一個在 3 秒左右完成。正如所見，該運算符花費了近 16 秒。我們可以通過適當的索引或查詢重寫來消除它嗎？

表架構

CREATE TABLE dbo.table1
(
   Recid  VARCHAR(128) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
   Cdate DATETIME NULL,
   flag BIT   NULL DEFAULT (0),
   Rcode INT NULL,
   EDR VARCHAR(255) COLLATE SQL_Latin1_general_CP1_CI_AS NULL,
   id BIGINT NULL
);

CREATE TABLE dbo.table2
(
   ENID BIGINT IDENTITY(1,1) NOT NULL,
   EID VARCHAR(50) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
   CID VARCHAR(350) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
   CDate DATETIME NOT NULL DEFAULT(getdate()),
   id BIGINT NOT NULL,

   CONSTRAINT PK_ENID PRIMARY KEY (ENID ASC, EID ASC),
);

-- table1
CREATE INDEX ix_Cdate on dbo.table1 (Cdate) WITH (FILLFACTOR=100);
CREATE CLUSTERED INDEX ix_Recid on dbo.table1 (Recid) WITH (FILLFACTOR=80);

-- table2
CREATE INDEX ix_ENID_id on dbo.table2 (ENID,id) WITH (FILLFACTOR=100);

變化

我所做的更改和一些數字：

• 添加提示OPTION (QUERYTRACEON 4138)- 平均。執行比原來的 50 秒減少了 7 秒，但應用程序團隊似乎無權在程式碼中執行此操作。需要進一步檢查這一點。
• OPTION (ORDER GROUP)給出了相同的平均結果。50秒，所以那裡沒有改善。
• 按照建議添加索引：

CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);

那裡沒有太大的改進。平均 45 秒，計劃與此問題中所附的類似（頂部計劃）。

在每次測試之前和之後，我確保計劃不是從以前的記憶體計劃中生成的。

快速計劃

對於兩個表中相同數量的行，附加更快且數據或查詢沒有任何更改的計劃仍然很快。應用程序團隊全天不斷送出上述查詢，以通過完成前 100 名來完成批次。有一個基於一些小費數字的計劃更改，以下是好的計劃的外觀：

編輯： - 一切都沒有改變，沒有程式碼更改或添加任何索引，正如我嘗試添加提示（FORCESEEK）時所建議的那樣，它給了我以下錯誤

由於此查詢中定義的提示，查詢處理器無法生成查詢計劃。在不指定任何提示且不使用 SET FORCEPLAN 的情況下重新送出查詢。

你有三個主要問題：

1. 沒有有用的索引來支持加入id。
2. TOP (100)引入了一個行目標，因此估計可能太低。
3. UPDATE是非確定性的。

來自 table2 的多行可以匹配 on id，因此不清楚應該使用來自 table2 的哪個匹配行來為OUTPUT子句提供值。聚合用於對 table2 進行分組id並為其他列選擇ANY匹配值。由於行目標，聚合是Flow Distinct。

需要非常小心ANY非確定性UPDATE語句中的聚合，因為您可能會得到不正確的結果。

問題中沒有足夠的細節來提出高質量的建議，但是：

1. 添加一個索引，如CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);
2. 用於OPTION (QUERYTRACEON 4138)禁用行目標，或OPTION (ORDER GROUP)使用Stream Aggregate而不是 Hash。
3. 如何修復不確定性UPDATE取決於數據關係。關鍵點是從源中辨識出與每個目標行匹配的最多一行。通常，這將涉及唯一索引或約束，或使用ROW_NUMBERor TOP (1)。

第 2 步可能需要也可能不需要。我添加它是為了完整性。

您可能會發現通過以這種形式編寫查詢更容易視覺化問題和調整查詢：

UPDATE TOP (100) 
   xyz WITH (UPDLOCK, READPAST)
SET xyz.flag = 1
OUTPUT 
   inserted.Rcode, inserted.EDR, inserted.id, 
   abc.EID, abc.CID, abc.ENID, abc.Cdate
FROM dbo.table1 AS xyz
CROSS APPLY
(
   -- At most one source row per target row
   SELECT TOP (1) 
       abc.* 
   FROM dbo.table2 AS abc
   WHERE
       abc.id = xyz.id
   -- ORDER BY something to choose the one row
) AS abc
WHERE 
   xyz.flag = 0;

執行計劃：

我可能不會為 table1 上的過濾索引而煩惱，但如果您確實想嘗試一下，這似乎是合適的：

CREATE INDEX i 
ON dbo.table1 (Recid) 
INCLUDE (id, flag) 
WHERE flag = 0;

如果您想繼續使用問題中給出的更新語法而不正確解決所有潛在問題，您可能會發現這更快：

UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK, FORCESEEK)
on xyz.id=abc.id
WHERE xyz.flag = 0;

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