SQL Server 中的合併行大小溢出 - “無法創建大小的行..”
我試圖將數據合併到的目標表有大約 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 Server
MERGE
實現在執行計劃的中間步驟中添加了計算資訊(作為額外的列)。出於技術原因需要這些額外資訊,以跟踪每一行是否應該導致插入、更新或刪除;並且還與 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
.