Sql-Server

更新相交掛起

  • June 21, 2019

我們剛開始使用 update intersect 語句時遇到了這種非常奇怪的行為。這些工作正常，但現在我們正在以列的形式攝取相當廣泛的數據源，並逐漸減慢直到無限期掛起。

當我們以 20K 行（非常小）的集合添加數據時，下面的查詢將變得越來越長，大約 70K 行。沒有使用索引，我們在攝取數據之前刪除它們。

這是聲明：

UPDATE Staging.[TdDailyPerformance]
SET [SYS_OPERATION] = 'U'
FROM (
   SELECT [HashCode]           
   FROM Staging.[TdDailyPerformance]
   INTERSECT
   SELECT [HashCode]
   FROM [IdMatch].[TdDailyPerformance]
) AS A

執行計劃：

執行計劃_1

在此處輸入圖像描述

現在這個查詢在我們伺服器上的許多其他地方都可以工作，但在這裡不行。有趣的是，無論是否INTERSECT返回行，查詢都會永遠掛起（我通過獨立執行 intersect 來測試這一點——它需要不到 2 毫秒。）。

根據 SQL 似乎它不應該工作，但它確實有效。如果表HasCode中的Staging已經存在於IdMatch表中，它會將表的 更新[SYS_OPERATION]為Staging“U”。我們在幾個地方使用了這幾個地方，它最近才開始在這個數據集上失敗。

有什麼想法可能導致這種情況嗎？

據我們所知，沒有阻塞。交易中唯一的等待類型CXPACKET是我對 QP 的期望。我查詢過sp_who2，查看了所有事務和活動監視器以辨識塊，但一無所獲。我沒有追查。

大多數情況下，我們的測試現在可以正常工作，因此掛起時它有 0 行。但我們已經驗證它還掛起 1-100 行INTERSECT。

IdMatch中不HashCodes存在Staging，但兩個表在掛起時都有大約 70K 行。所以要清楚，兩個表都有大約 70K，但交叉點HashCode是 0 行。

我們已經使用索引進行了測試。在我們點擊有問題的查詢之前，我們的整體性能很差。索引只是碎片太快而沒有幫助。

表定義

Staging.TdDailyPerformance

CREATE TABLE [Staging].[TdDailyPerformance]
(
   [ID] INT NOT NULL,
   [SYS_OPERATION] CHAR(8) NULL,
   [HashCode] BINARY(65) NULL,
   [Ad Environment] NVARCHAR(1024) NULL,
   [Ad Format] NVARCHAR(10) NULL,
   [Ad Group] NVARCHAR(1024) NULL,
   [Ad Group ID] NVARCHAR(32) NULL,
   [Ad Server Creative Placement ID] NVARCHAR(1024) NULL,
   [Ad Server Name] NVARCHAR(1024) NULL,
   [Advertiser] NVARCHAR(1024) NULL,
   [Advertiser Currency Code] NVARCHAR(32) NULL,
   [Advertiser ID] NVARCHAR(32) NULL,
   [App] NVARCHAR(1024) NULL,
   [Audience] NVARCHAR(1024) NULL,
   [Audience ID] NVARCHAR(32) NULL,
   [Browser] NVARCHAR(30) NULL,
   [Campaign] NVARCHAR(1024) NULL,
   [Campaign ID] NVARCHAR(32) NULL,
   [Carrier ID] INT NULL,
   [Carrier Name] NVARCHAR(1024) NULL,
   [Category ID] NVARCHAR(1024) NULL,
   [Category Name] NVARCHAR(1024) NULL,
   [City] NVARCHAR(50) NULL,
   [Country] NVARCHAR(50) NULL,
   [Creative] NVARCHAR(1024) NULL,
   [Creative Duration In Seconds] INT NULL,
   [Creative ID] NVARCHAR(32) NULL,
   [Date] NVARCHAR(1024) NULL,
   [Deal ID] NVARCHAR(128) NULL,
   [Device Make] NVARCHAR(32) NULL,
   [Device Type] NVARCHAR(15) NULL,
   [Fold] NVARCHAR(128) NULL,
   [Language] NVARCHAR(1024) NULL,
   [Market Type] NVARCHAR(32) NULL,
   [Media Type] NVARCHAR(32) NULL,
   [Metro] NVARCHAR(128) NULL,
   [Metro Code] INT NULL,
   [Operating System] NVARCHAR(32) NULL,
   [Operating System Family] NVARCHAR(1024) NULL,
   [Partner ID] NVARCHAR(32) NULL,
   [Partner Name] NVARCHAR(32) NULL,
   [Recency Group] NVARCHAR(32) NULL,
   [Recency Group End In Minutes] INT NULL,
   [Recency Group Start In Minutes] INT NULL,
   [Region] NVARCHAR(128) NULL,
   [Site] NVARCHAR(128) NULL,
   [Site List Name] NVARCHAR(1024) NULL,
   [Site/Category Bid Factor] NVARCHAR(1024) NULL,
   [Supply Vendor] NVARCHAR(25) NULL,
   [Supply Vendor Publisher Id] NVARCHAR(64) NULL,
   [Timezone] NVARCHAR(1024) NULL,
   [Video Playback Type] NVARCHAR(32) NULL,
   [Whitelist Site] NVARCHAR(32) NULL,
   [Partner Currency Code] NVARCHAR(1024) NULL,
   [Additional Fee Cost (Adv Currency)] DECIMAL(37,15) NULL,
   [Additional Fee Cost (Partner Currency)] DECIMAL(37,15) NULL,
   [Additional Fee Cost (USD)] DECIMAL(37,15) NULL,
   [Advertiser Cost (Adv Currency)] DECIMAL(37,15) NULL,
   [Advertiser Cost (Partner Currency)] DECIMAL(37,15) NULL,
   [Advertiser Cost (USD)] DECIMAL(37,15) NULL,
   [All Last Click + View Conversions] INT NULL,
   [Bids] INT NULL,
   [Clicks] INT NULL,
   [Companion Clicks] INT NULL,
   [Companion Impressions] INT NULL,
   [Data Cost (Adv Currency)] DECIMAL(37,15) NULL,
   [Data Cost (Partner Currency)] DECIMAL(37,15) NULL,
   [Data Cost (USD)] DECIMAL(37,15) NULL,
   [Fee Features Cost (Adv Currency)] DECIMAL(37,15) NULL,
   [Fee Features Cost (Partner Currency)] DECIMAL(37,15) NULL,
   [Fee Features Cost (USD)] DECIMAL(37,15) NULL,
   [IAS Display Fully In View 0 Seconds] INT NULL,
   [IAS Display Fully In View 1 Second] INT NULL,
   [IAS Display Fully In View 15 Seconds] INT NULL,
   [IAS Display Fully In View 5 Seconds] INT NULL,
   [IAS Display In View 1 Second] INT NULL,
   [IAS Display In View 15 Seconds] INT NULL,
   [IAS Display In View 5 Seconds] INT NULL,
   [IAS High Risk Impression Count] INT NULL,
   [IAS Low Risk Impression Count] INT NULL,
   [IAS Moderate Risk Impression Count] INT NULL,
   [IAS Non GVIT Impression Count] INT NULL,
   [IAS Suspicious Activity] INT NULL,
   [IAS Total Impression Count] INT NULL,
   [IAS Very High Risk Impression Count] INT NULL,
   [IAS Video 25% Complete] INT NULL,
   [IAS Video 50% Complete] INT NULL,
   [IAS Video 75% Complete] INT NULL,
   [IAS Video Completed Views] INT NULL,
   [IAS Video Muted] INT NULL,
   [IAS Video Viewable 25% Complete] INT NULL,
   [IAS Video Viewable 50% Complete] INT NULL,
   [IAS Video Viewable 75% Complete] INT NULL,
   [IAS Video Viewable Completed Views] INT NULL,
   [Impressions] INT NULL,
   [In-banner Player Impressions] INT NULL,
   [Large Player Impressions] INT NULL,
   [Media Cost (Adv Currency)] DECIMAL(37,15) NULL,
   [Media Cost (Partner Currency)] DECIMAL(37,15) NULL,
   [Media Cost (USD)] DECIMAL(37,15) NULL,
   [Medium Player Impressions] INT NULL,
   [MOAT Display In View Time 10 Seconds] INT NULL,
   [MOAT Display In View Time 15 Seconds] INT NULL,
   [MOAT Display In View Time 30 Seconds] INT NULL,
   [MOAT Display In View Time 5 Seconds] INT NULL,
   [MOAT Display On Screen] INT NULL,
   [MOAT Display Universal Interaction] INT NULL,
   [MOAT Display Universal Interaction Time 10 Seconds] INT NULL,
   [MOAT Display Universal Interaction Time 15 Seconds] INT NULL,
   [MOAT Display Universal Interaction Time 30 Seconds] INT NULL,
   [MOAT Display Universal Interaction Time 5 Seconds] INT NULL,
   [MOAT Video Audible and Visible on Complete] INT NULL,
   [MOAT Video In View Time 3 Seconds] INT NULL,
   [MOAT Video In View Time 5 Seconds] INT NULL,
   [MOAT Video Visible on Complete] INT NULL,
   [Non-USD Currency Cost (Adv Currency)] DECIMAL(37,15) NULL,
   [Non-USD Currency Cost (Partner Currency)] DECIMAL(37,15) NULL,
   [Non-USD Currency Cost (USD)] DECIMAL(37,15) NULL,
   [Partner Cost (Adv Currency)] DECIMAL(37,15) NULL,
   [Partner Cost (Partner Currency)] DECIMAL(37,15) NULL,
   [Partner Cost (USD)] DECIMAL(37,15) NULL,
   [Player 25% Complete] INT NULL,
   [Player 50% Complete] INT NULL,
   [Player 75% Complete] INT NULL,
   [Player Audible Event] INT NULL,
   [Player Close] INT NULL,
   [Player Collapse] INT NULL,
   [Player Completed Views] INT NULL,
   [Player Engaged Views] INT NULL,
   [Player Errors] INT NULL,
   [Player Expansion] INT NULL,
   [Player Full Screen] INT NULL,
   [Player Invitation Accept] INT NULL,
   [Player Mute] INT NULL,
   [Player Pause] INT NULL,
   [Player Playing Event] INT NULL,
   [Player Resume] INT NULL,
   [Player Rewind] INT NULL,
   [Player Skip] INT NULL,
   [Player Starts] INT NULL,
   [Player Total Playing Seconds] INT NULL,
   [Player Unmute] INT NULL,
   [Player Views] INT NULL,
   [Predictive Clearing Savings (Adv Currency)] DECIMAL(37,15) NULL,
   [Predictive Clearing Savings (Partner Currency)] DECIMAL(37,15) NULL,
   [Predictive Clearing Savings (USD)] DECIMAL(37,15) NULL,
   [Profit (Adv Currency)] DECIMAL(37,15) NULL,
   [Profit (Partner Currency)] DECIMAL(37,15) NULL,
   [Profit (USD)] DECIMAL(37,15) NULL,
   [Sampled Tracked Impressions] INT NULL,
   [Sampled Viewed Impressions] INT NULL,
   [Small Player Impressions] INT NULL,
   [Total Audible Seconds] INT NULL,
   [Total Bid Amount (Adv Currency)] DECIMAL(37,15) NULL,
   [Total Bid Amount (Partner Currency)] DECIMAL(37,15) NULL,
   [Total Bid Amount (USD)] DECIMAL(37,15) NULL,
   [Total Custom CPA Conversions] INT NULL,
   [Total Seconds In View] INT NULL,
   [TTD Cost (Adv Currency)] DECIMAL(37,15) NULL,
   [TTD Cost (Partner Currency)] DECIMAL(37,15) NULL,
   [TTD Cost (USD)] DECIMAL(37,15) NULL,
   [TTD Margin (Adv Currency)] DECIMAL(37,15) NULL,
   [TTD Margin (Partner Currency)] DECIMAL(37,15) NULL,
   [TTD Margin (USD)] DECIMAL(37,15) NULL,
   [Video In View Event] INT NULL,
   [White Ops SIVT Bids Avoided] INT NULL,

   [CreatedOn] DATETIME2 NULL CONSTRAINT df_Staging_TdDailyPerformance_CreatedOn DEFAULT SYSUTCDATETIME(),
   [ModifiedOn] DATETIME2 NULL CONSTRAINT df_Staging_TdDailyPerformance_ModifiedOn DEFAULT SYSUTCDATETIME(),
   [Retired] BIT NULL CONSTRAINT df_Staging_TdDailyPerformance_Retired DEFAULT 0
)

IdMatch.TdDailyPerformance

CREATE TABLE [IdMatch].[TdDailyPerformance]
(
   [ID] INT IDENTITY(1,1) NOT NULL,
   [HashCode] BINARY(65) NOT NULL,
   [Ad Environment] NVARCHAR(1024) NULL,
   [Ad Format] NVARCHAR(10) NULL,
   [Ad Group] NVARCHAR(1024) NULL,
   [Ad Group ID] NVARCHAR(32) NULL,
   [Ad Group Integer ID] INT NULL,
   [Ad Server Creative Placement ID] NVARCHAR(1024) NULL,
   [Ad Server Name] NVARCHAR(1024) NULL,
   [Advertiser] NVARCHAR(1024) NULL,
   [Advertiser Currency Code] NVARCHAR(1024) NULL,
   [Advertiser ID] NVARCHAR(32) NULL,
   [App] NVARCHAR(1024) NULL,
   [Browser] NVARCHAR(30) NULL,
   [Campaign] NVARCHAR(1024) NULL,
   [Campaign ID] NVARCHAR(32) NULL,
   [Carrier ID] INT NULL,
   [Carrier Name] NVARCHAR(1024) NULL,
   [Category ID] NVARCHAR(1024) NULL,
   [Category Name] NVARCHAR(1024) NULL,
   [City] NVARCHAR(50) NULL,
   [Country] NVARCHAR(50) NULL,
   [Creative] NVARCHAR(1024) NULL,
   [Creative Duration In Seconds] INT NULL,
   [Creative ID] NVARCHAR(32) NULL,
   [Date] NVARCHAR(1024) NULL,
   [Device Make] NVARCHAR(32) NULL,
   [Device Type] NVARCHAR(15) NULL,
   [Fold] NVARCHAR(128) NULL,
   [Language] NVARCHAR(1024) NULL,
   [Market Type] NVARCHAR(32) NULL,
   [Media Type] NVARCHAR(32) NULL,
   [Metro] NVARCHAR(128) NULL,
   [Metro Code] INT NULL,
   [Operating System] NVARCHAR(32) NULL,
   [Operating System Family] NVARCHAR(1024) NULL,
   [Partner ID] NVARCHAR(32) NULL,
   [Partner Name] NVARCHAR(32) NULL,
   [Region] NVARCHAR(128) NULL,
   [Site] NVARCHAR(128) NULL,
   [Site List Name] NVARCHAR(1024) NULL,
   [Site/Category Bid Factor] NVARCHAR(1024) NULL,
   [Supply Vendor] NVARCHAR(25) NULL,
   [Supply Vendor Integer ID] INT NULL,
   [Supply Vendor Publisher Id] NVARCHAR(64) NULL,
   [Timezone] NVARCHAR(1024) NULL,
   [Video Playback Type] NVARCHAR(32) NULL,
   [Whitelist Site] NVARCHAR(32) NULL,
   [CreatedOn] DATETIME2 NOT NULL CONSTRAINT df_IdMatch_TdDailyPerformance_CreatedOn DEFAULT SYSUTCDATETIME(),
   [ModifiedOn] DATETIME2 NOT NULL CONSTRAINT df_IdMatch_TdDailyPerformance_ModifiedOn DEFAULT SYSUTCDATETIME(),
   [Retired] BIT NOT NULL CONSTRAINT df_IdMatch_TdDailyPerformance_Retired DEFAULT 0
)
GO

你寫了一個意外的交叉連接：

UPDATE Staging.[TdDailyPerformance]
SET [SYS_OPERATION] = 'U'
FROM (
   SELECT [HashCode]           
   FROM Staging.[TdDailyPerformance]
   INTERSECT
   SELECT [HashCode]
   FROM [IdMatch].[TdDailyPerformance]
) AS A

在使用T-SQL 的擴展時，這是一個很常見的錯誤。FROM``UPDATE

期望是兩個引用都Staging.[TdDailyPerformance]標識對象的同一實例，但這不是它的工作方式。

上面的語句實際上指定瞭如果派生表產生任何行，則應該更新目標的所有行。的兩個實例分別綁定。A``Staging.[TdDailyPerformance]

由於我在此答案末尾單獨討論的原因，該查詢似乎掛起。

編寫此類更新最安全的方法是為表設置別名並始終使用別名作為目標。（您還應該確定性地編寫查詢，以便每個目標行最多只能更新一次。）

如果我們嘗試使用上面的語句遵循別名規則：

UPDATE S_TDP
SET SYS_OPERATION = 'U'
FROM
(
   SELECT S_TDP.HashCode
   FROM Staging.TdDailyPerformance AS S_TDP
   INTERSECT
   SELECT I_TDP.HashCode
   FROM IdMatch.TdDailyPerformance AS I_TDP
) AS A;

我們收到一個綁定錯誤，提醒我們注意錯誤：

消息 208，級別 16，狀態 1，第 xxx 行

無效的對象名稱“S_TDP”。

您已經知道可行的替代方案，因此我不會在這一點上費力，只是提到您可能應該按照聊天室中的建議考慮將這兩個更新結合起來。

相關問答：

計劃分析

不知道大家對錯誤更新語句的執行計劃有多大興趣，但以防萬一，這裡簡單分析一下串列版的計劃。

頂部下方的計劃部分涉及查找由 產生的第一行（如果有）INTERSECT：

頂部子樹

Flow Distinct是一種行目標優化，旨在快速生成第一個不同的雜湊碼值。選擇嵌套循環連接是因為優化器只希望掃描內部堆表一次以找到匹配的雜湊碼。

當雜湊碼不匹配時，會公開此策略。在這種情況下，外側的每一行都將完全掃描內側——總共 70,000 次完全掃描。這可能需要一段時間。您可以使用記錄的跟踪標誌 4138 （例如通過查詢提示）來測試刪除行目標的效果。更新語句仍然不正確，但至少它不會出現掛起。OPTION (QUERYTRACEON 4138)

單獨執行時您不會看到此問題，INTERSECT因為優化器在搜尋合理計劃時引入了行目標（與 Top 一起）。您可以使用如下查詢來模擬它：

SELECT TOP (1) 1
FROM 
(
   SELECT S_TDP.HashCode
   FROM Staging.TdDailyPerformance AS S_TDP
   INTERSECT
   SELECT I_TDP.HashCode
   FROM IdMatch.TdDailyPerformance AS I_TDP
) AS A

或者：

SELECT DISTINCT 
   TDP.HashCode
FROM Staging.TdDailyPerformance AS TDP
CROSS JOIN 
(
   SELECT [HashCode]           
   FROM Staging.[TdDailyPerformance]
   INTERSECT
   SELECT [HashCode]
   FROM [IdMatch].[TdDailyPerformance]
) AS A;

如果找到一行，則計劃的其餘部分會更新整個目標表：

更新

嵌套循環連接沒有連接謂詞。按堆 RID排序和流*聚合組記錄。*這是沒有意義的，但它是用於折疊計劃的一般邏輯的一部分，這些計劃可能將同一目標行多次更新為每行單個（非確定性）更新。

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

相關問答

Sql-Server

如何使用 SQL Server 2014 的 UPDATE 合併和刪除 SQL 中的重複行？

  • December 31, 2020
檢視問答
Sql-Server

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

  • August 10, 2020
檢視問答
Sql-Server

更新包含 Image 數據類型列的表

  • May 13, 2020
檢視問答
Sql-Server

如何使用觸發器中同一新插入行的值更新列？

  • May 8, 2020
檢視問答
Sql-Server

如果不存在則更新語句

  • February 20, 2019
檢視問答
Sql-Server

使用 SQL Server 中另一個表中的值更新表

  • February 17, 2019
檢視問答