Sql-Server

SQL Server 中的合併行大小溢出 - “無法創建大小的行..”

  • August 11, 2015

我試圖將數據合併到的目標表有大約 660 列。合併的程式碼:

MERGE TBL_BM_HSD_SUBJECT_AN_1 AS targetTable
USING
       (                
SELECT * 
FROM TBL_BM_HSD_SUBJECT_AN_1_STAGING
WHERE [ibi_bulk_id] in (20150520141627106) and  id in(101659113)
   ) AS sourceTable 
ON (...)
WHEN MATCHED AND ((targetTable.[sampletime] <= sourceTable.[sampletime]))
       THEN UPDATE SET ...
WHEN NOT MATCHED 
       THEN INSERT (...)
   VALUES (...)

我第一次執行它(即當表為空時)它導致成功,並插入了一行。

我第二次使用相同的數據集執行此命令時,返回錯誤:

無法創建大小為 8410 的行,該行大於允許的最大行大小 8060。

為什麼我第二次嘗試合併已經插入的同一行會導致錯誤。如果該行超過了最大行大小,則預計不可能首先將其插入。

所以我嘗試了兩件事,(並且成功了!):

  • 從合併語句中刪除“WHEN NOT MATCHED”部分
  • 使用我嘗試合併的同一行執行更新語句

為什麼使用合併更新不成功,而插入成功,直接更新也成功?

更新:

設法找到實際的行大小 - 4978。我創建了一個只有這一行的新表,並以這種方式找到行大小: 在此處輸入圖像描述

而且我仍然沒有看到超出允許限制的東西。

更新(2):

完全再現

努力使這種複制不需要任何額外的輔助對象,並且數據將(某種程度上)被混淆。

從 2012 版和 2008 版開始在多台伺服器上進行了嘗試,並且能夠在所有伺服器上完全重現。

為什麼我第二次嘗試合併已經插入的同一行會導致錯誤。如果該行超過了最大行大小,則預計不可能首先將其插入。

首先,感謝您提供複製腳本。

問題不在於 SQL Server 無法插入或更新特定的使用者可見 行。正如您所指出的,已經插入到表中的行肯定不能從根本上說太大,SQL Server 無法處理。

出現此問題的原因是 SQL ServerMERGE實現在執行計劃的中間步驟中添加了計算資訊(作為額外的列)。出於技術原因需要這些額外資訊,以跟踪每一行是否應該導致插入、更新或刪除;並且還與 SQL Server 通常在更改索引期間避免臨時鍵衝突的方式有關。

SQL Server 儲存引擎要求索引始終是唯一的(在內部,包括任何隱藏的唯一標識符)——在處理每一行時——而不是在整個事務的開始和結束時。在更複雜MERGE的場景中,這需要拆分(將更新轉換為單獨的刪除和插入)、排序和可選的折疊(將同一鍵上的相鄰插入和更新轉換為更新)。更多資訊

順便說一句,請注意,如果目標表是堆,則不會出現此問題(刪除聚集索引以查看此內容)。我不建議將此作為修復方法,只是提及它以強調始終保持索引唯一性(在本例中為集群)與拆分-排序-折疊之間的聯繫。

簡單的 MERGE查詢中,具有合適的唯一索引以及源行和目標行之間的直接關係(通常使用ON具有所有關鍵列的子句進行匹配),查詢優化器可以簡化大部分通用邏輯,從而產生相對簡單的計劃不需要 Split-Sort-Collapse 或 Segment-Sequence Project 來檢查目標行是否只被觸及一次。

在具有更多不透明邏輯的複雜 MERGE查詢中,優化器通常無法應用這些簡化,從而暴露出更多正確處理所需的基本複雜邏輯(儘管存在產品錯誤,並且有很多)。

您的查詢當然很複雜。該ON子句與索引鍵不匹配(我明白為什麼),並且“源表”是一個涉及排名視窗函式的自聯接(再次說明原因):

MERGE MERGE_REPRO_TARGET AS targetTable
USING
(
   SELECT * FROM 
   (
       SELECT 
           *, 
           ROW_NUMBER() OVER (
               PARTITION BY ww,id, tenant 
               ORDER BY 
               (
                   SELECT COUNT(1) 
                   FROM MERGE_REPRO_SOURCE AS targetTable
                   WHERE 
                       targetTable.[ibi_bulk_id] = sourceTable.[ibi_bulk_id] 
                       AND targetTable.[ibi_row_id] <> sourceTable.[ibi_row_id] 
                       AND 
                       (
                           (targetTable.[ww] = sourceTable.[ww]) 
                           AND (targetTable.[id] = sourceTable.[id]) 
                           AND (targetTable.[tenant] = sourceTable.[tenant])
                       ) 
                       AND NOT ((targetTable.[sampletime] <= sourceTable.[sampletime]))
               ),
               sourceTable.ibi_row_id DESC
           ) AS idx
       FROM MERGE_REPRO_SOURCE sourceTable 
       WHERE [ibi_bulk_id] in (20150803110418887)
   ) AS bulkData
   where idx = 1
) AS sourceTable 
ON 
   (targetTable.[ww] = sourceTable.[ww]) 
   AND (targetTable.[id] = sourceTable.[id]) 
   AND (targetTable.[tenant] = sourceTable.[tenant])
...

這會產生許多額外的計算列,主要與拆分和更新轉換為插入/更新對時所需的數據相關聯。這些額外的列導致中間行超過了早期排序中允許的 8060 字節 - 就在過濾器之後:

問題排序

請注意,過濾器在其輸出列表中有1,319 列(表達式和基本列)。附加調試器會顯示引發致命異常時的呼叫堆棧:

堆棧跟踪

請注意,問題不在 Spool 上 - 那裡的異常被轉換為關於行可能太大的警告

為什麼使用合併更新不成功,而插入成功,直接更新也成功?

直接更新的內部複雜性與MERGE. 這是一個從根本上更簡單的操作,傾向於更好地簡化和優化。刪除該NOT MATCHED子句還可以消除足夠多的複雜性,從而在某些情況下不會產生錯誤。然而,複製品不會發生這種情況。

最終,我的建議是避免MERGE更大或更複雜的任務。的經驗是,與MERGE.

引用自:https://dba.stackexchange.com/questions/102382