SQL Server 中 LOB 數據的緩慢刪除

我有一個用於記錄的表，以及一個用於清除 DELETE 性能非常慢的舊數據的儲存過程，這超出了我的理解範圍。我正在尋找如何修改表或 DELETE 語句以在 LOB 數據上執行得相當好。或者，如果 Microsoft 確認了該問題——例如“我們已使用 SQL Server 版本 x 解決了這個問題”，或者甚至“我們看到這表現不佳，但這不是優先事項”——這也可以.

這是在 Microsoft SQL Server 2012 (SP3) 上執行的。下面基本上是我的實際表格和程式碼，只是稍微簡化了：

CREATE TABLE [LOG_VALUE](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VALUE] [varchar](max) NOT NULL,
[CHECKSUM] [int] NOT NULL,
[VALUE_LEN] [int] NOT NULL,
CONSTRAINT [PK_LOG_REQUEST] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

有問題的刪除是通過以下方式完成的：

WHILE (@@ROWCOUNT > 0)
 DELETE TOP (100) [LOG_VALUE]
 OUTPUT DELETED.[VALUE_LEN] INTO @DELETED_ROWS
 WHERE [ID] IN (SELECT [ID] FROM @DELETE_IDS);

基本的儲存過程過程是：

1. 刪除引用要清除的行的值
2. 選擇$$ ID $$將上述日誌表中不存在的 LOB 表中的值放入表變數 @DELETE_IDS
3. 一次刪除前 100 行（以減少/防止爭用/鎖定）

顯然，LOB 數據必須載入到伺服器的記憶體中才能被刪除。支持：1）當我向表中插入 12000 行時，它幾乎是瞬時的 2）檢索 12000 行幾乎是瞬時的 3）一旦它們不再在記憶體中，選擇和刪除相同的 12000 行需要 ~40秒。

我能找到的最相關的問題是： https ://serverfault.com/questions/241893/delete-performance-for-lob-data-in-sql-server 從 2011 年開始，但遺憾的是沒有令人滿意的答案。我只是希望在 dba.stackexchange.com 上問這個問題會找到更多知識淵博的聽眾 :)。這個問題的作者肯定比我更了解這個問題，但當我讀到它時，中心點是：

1. 刪除行時，SQL Server 通常只是將它們標記為已刪除，並且稍後會通過“Ghost Cleanup Task”對其進行清理
2. 刪除 LOB 數據時，在刪除過程中對所有 LOB 數據頁進行排他鎖，並釋放這些頁
3. 當頁面不在緩衝區記憶體中時，這些排他鎖等待頁面載入到記憶體中
4. 這些被釋放的頁面是“預先”發生的，而不是在清理任務中。這意味著刪除操作總是在語句完成之前等待要載入的數據，從而解鎖相關行

似乎這種釋放應該發生在清理任務中，而不是預先發生。而且由於 LOB 數據沒有在謂詞（或 OUTPUT）中引用，因此根本不需要將其載入到記憶體中。

更多可能相關的資訊，但我認為不是：

• 這些值通常超過 100KB，但大小差異很大
• 指向的另一個表$$ LOG_VALUE $$.$$ ID $$不使用參照完整性強制執行
• @DELETED_ROWS 表累積記錄，在儲存過程結束時，刪除了多少行和字節
• 目前有 110 萬行，按 IDENTITY 的值計算，曾經只有 160 萬行
• 沒有觸發器
• 目前，幽靈記錄計數 = 0
• READ_COMMITTED_SNAPSHOT並且ALLOW_SNAPSHOT_ISOLATION都關閉。

除了刪除/更新 LOB 數據之外，整個數據庫（以及同一伺服器上的其他數據庫）在所有情況下都按照我的預期執行。

…一旦它們不再在記憶體中，選擇和刪除相同的 12000 行都需要大約 40 秒。

這似乎表明儲存子系統不足。如果這是原因，SQL Server 可能會使用其中一種PAGEIOLATCH_XX等待類型進行等待。

顯然，LOB 數據必須載入到伺服器的記憶體中才能被刪除。

據報導 Paul Randal 說過（更新 2，在您連結到的問題中），SQL Server必須遍歷 LOB 樹才能找到必須刪除的頁面。為此，SQL Server 需要將這些 LOB 樹頁面放入記憶體。真的沒有辦法解決這個問題。該資訊無法以任何其他方式獲得。

似乎這種釋放應該發生在清理任務中，而不是預先發生。

它的工作方式沒有很好的記錄。

在我的測試中，如果沒有特殊原因保留該行，SQL Server 會**立即刪除 LOB 數據。**這似乎是一種優化：畢竟，如果我們已經在接觸 LOB 數據，我們還不如在我們在那裡時將其刪除。將其推遲到幽靈清理 (GC) 只會增加成本，因為 GC 還需要遍歷鏈。此外，減少了日誌記錄，因為 SQL Server 只需要記錄釋放，而不是完整的 LOB 內容。

如果（例如）表上有觸發器，或者啟用了行版本控制隔離級別，則LOB 刪除操作將推遲到GC。這兩個功能都使用行版本，必須在 LOB 行上維護。在這些情況下，LOB 刪除是完全記錄的。當 GC 稍後執行時，它會執行 LOB 頁面的釋放。如果您想自己測試，可以使用（記錄的）全域跟踪標誌 661暫時禁用 GC ，以查看這些幻影 LOB 記錄。

考慮到這一切，很難看出（完全）記錄刪除和推遲對 GC 已經完成的大部分相同工作的重複對您有什麼幫助。在刪除期間記錄更少並刪除 LOB 數據可能會更快。

---

除了儲存系統不堪重負/指定不足的可能性之外，這個問題不足以推測刪除性能不佳的確切原因，但我可以說一般來說最好：

1. 避免在未送出讀隔離下訪問 LOB 數據
2. 執行刪除時避免在具有 LOB 數據的表上使用觸發器
3. 注意行版本控制的副作用
4. 確保儲存子系統足以用於日誌記錄和一般 I/O
5. 執行最新版本的 SQL Server（目前為 SQL Server 2012 的 11.00.6523）

有關第 1 點的更多資訊，請參閱Paul Randal的性能錯誤：涉及行外 LOB 數據的 NOLOCK 掃描。有關第 2 點和第 3 點的更多資訊，請參閱拆分頁面和轉發幽靈的刪除（由我撰寫）。

同樣可能是簡化的範例遺漏了關鍵細節，或者表變數產生了低效的執行計劃，或者您的表已經有數十億的幽靈 LOB 記錄。詳細的分析可能需要訪問系統本身。

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