刪除查詢在 Azure SQL 數據庫中使用 100% CPU
我有一個相對較小的數據庫，可能有幾十萬行，我們有一個儲存過程，旨在清除“經銷商”（應用程序聚合根）及其所有相關數據。該數據庫位於 v12 Azure SQL 實例上，當它訪問一個特定的表時，它會消耗該實例 100% 的 DTU/CPU，並且需要很長時間才能完成。
我認為問題在於該表有大量外鍵，可能大約 30 個。查看執行計劃，您可以看到它正在執行大量
Nested Loop索引掃描以查找相關行。在嘗試從該特定表中刪除行之前，所有這些行都已被刪除，因此這些循環實際上毫無意義。我的問題是，禁用這些依賴表上的外鍵約束，刪除，然後重新啟用約束會更有效嗎？這樣做會不會產生一些負面影響，除了在技術上可能會在禁用約束時插入一些壞數據這一事實。還是有更好的方法來完成我想要完成的事情？
更新 這是一個 v12 Azure SQL 數據庫，它不是 VM 中完整的 SQL Server 2012。我沒有批量刪除任何刪除，有問題的語句是更大的 SPROC 的一部分。我通過撕開儲存過程並分別執行它的零碎部分來辨識這個特定的表刪除。當它到達這張桌子時，實際上需要 15 分鐘才能完成。它將影響數百行的順序，具體取決於我們正在清除的特定“經銷商”的狀態。
在這種情況下我們不能進行軟刪除，因為通常我們會清除這個經銷商以便可以替換它（軟刪除會導致重複並且需要大量程式碼來解決）。我調查了其他類型的阻塞和等待狀態，沒有發現其他似乎對此產生負面影響的東西。正如我提到的，這個數據庫相對較小，主要用於非常簡單的 CRUD 操作。
這是執行計劃，非常龐大。
執行計劃的 XML 版本可以在這裡找到：https ://gist.github.com/CodingGorilla/6cf7a87df9257d5f93e0d545af9839c2
正如您所注意到的，該計劃顯示了許多索引掃描
$$ 1 $$, 幾個索引多次，例如
Facilities.IX_CustomerID. 這就是它旋轉 CPU 資源的原因：這些索引將位於緩衝池中，因此需要很少的 IO，並且 CPU 正在反复咀嚼它們，可能是全部。 如果這是來自一個簡單的DELETE語句並且所有其他活動是執行外鍵約束的引擎，那麼我將在不支持索引的情況下檢查外鍵。例如，其中一個掃描[Facilities]" Index="[IX_CustomerID]具有搜尋謂詞[DataMateWeb].[dbo].[Facilities].[SurfaceCleanerID]=[DataMateWeb].[dbo].[Chemical].[ChemicalID]，這表明索引覆蓋[Facilities].[SurfaceCleanerID]不存在或由於某種原因不能使用。當您定義一個外鍵時，不會自動創建一個索引來配合它，這可能會讓人們感到驚訝，這將解釋對錶/集群的掃描，而不是對索引的多次搜尋。在這些索引是外鍵的支持索引的任何情況下，請確保它們被正確定義並且沒有嚴重過時的統計資訊。如果刪除操作不是那麼簡單，那麼請確保在決定哪些行將被刪除時使用的任何連接和子查詢都受到適當索引的支持，並且連接和過濾謂詞的排列方式使得它們在可能的情況下是可搜尋的。
如果上述情況失敗（即，所有 FK 和任何其他過濾和連接活動在具有它們可能尋求的索引方面看起來都很好），我要抓住的下一根稻草是：如果您一次刪除許多行（
DELETE WHERE IN (<a result that could be a long list>)）然後我看到讓 SQL Server 掃描索引而不是使用多次搜尋。這個感覺有點噁心$$ 2 $$但是：通過嘗試僅使用一行來確認行為來檢查這一點，如果這至少將某些掃描更改為搜尋（並因此提高了性能），請考慮在需要刪除的行中進行游標並在其中執行一次時間。 $$ 1 $$因為它們是聚集索引掃描，所以實際上是表掃描，對於大表來說，這意味著大量數據被觸及。
$$ 2 $$因為它很討厭。但如果它很討厭並且有效，它仍然有效！