SQL 估計在 DELETE 語句上與大型表上的觸發器相差甚遠

我正在使用 Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 版權所有 (c) Microsoft Corporation Standard Edition (64-bit) o​​n Windows Server 2012 R2標準 6.3（內部版本 9600：）

此伺服器位於 SSD 驅動器上，最大記憶體為 128 GB。CostTheshold for Parallelism 是 70，MaxDegree of Parallelism 是 3。

我有一個“Trips”表，它被 23 個外鍵和 ON DELETE CASCADE 選項引用。

這個表本身並沒有那麼大（530 萬行，1.3 GB 的數據）。但是在 23 個引用的表中，有兩個表非常大（超過 10 億行，每個 54 和 69 GB）。

問題是當我們嘗試刪除“Trips”表中的少量行（比如說 4 行）時，SQL 估計要刪除這麼多行，它需要 10gb 的 RAM，估計數百萬行將被刪除返回，並鎖定表。一切都停止，其他查詢阻塞，應用程序超時。

以下是 1 個刪除語句的主表和行數：

• 行程（4 行）
• Segments（27 行，與 Trips by SegmentId 相關）
• 配置文件（2012 行，與 SegmentId 相關的 Segments）
• ProfileRanges（2337 行，通過 ProfileId 與 Profiles 相關）
• 事件（7750 行，與 SegmentId 相關的 Segments）
• EventConditions（9230 行，通過 EventId 與事件相關）

表 EventConditions 和 ProfileRanges 各有超過 10 億行。

這是計劃記憶體：https ://www.brentozar.com/pastetheplan/?id=HJNg5I0BU

當我查看 SentryOne 計劃資源管理器時，我可以看到 SQL 正在讀取整個表，即使“表假離線”隨後過濾並僅保留 2012 行 ProfileRanges 和 EventConditions 大致相同。

當我使用 Brent Ozar 的 sp_blitzCache 過程查看查詢的記憶體授予時，我可以看到該查詢要求大約 10gb 的 RAM。

之後，查詢要麼等待 SOS_SCHEDULER_YIEL（因此等待 4ms 後輪到使用 CPU）或 MEMORY_ALLOCATION_EXT。程序超時並失敗。

我能做些什麼來完成這項工作？

我正在考慮的一件事是刪除兩個最大表上的外鍵並在而不是觸發器中刪除它們的行。但我不喜歡使用觸發器而不是外鍵來強制數據庫一致性。

任何建議或幫助將不勝感激

---

ProfileRanges 的主鍵是

• ProfileId int
• ProfileRangeDefId1 int
• ProfileRangeDefId2 int

EventConditions 的主鍵是

• EventId 大整數
• EventConditionDefId int

假設所有相關表都有正確的刪除路徑索引，您可以嘗試：

DELETE [Trips]
WHERE [ISAFileName]='ID_774199_20200311_133117.isa'
OPTION (LOOP JOIN, FAST 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

如果可行，請嘗試將其減少到最少的提示數。

這類計劃對於基數估計非常具有挑戰性，而“預設”的 CE 模型通常會造成混亂。

一旦你有一個工作良好的計劃形狀，你應該能夠在必要時使用計劃指南等強制該形狀。

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