來自索引外鍵級聯刪除的過多記憶體授予
每天，商店都可以將銷售資訊輸入到我們的 OLTP 應用程序中。該應用程序呼叫 SQL 中的儲存過程來保存此資訊。根據使用者的活動，應用程序發送一個程式碼，指示 proc 是否應該執行插入、更新或刪除。
此保存儲存過程正在接收 60 GB 的記憶體授予，用於刪除一行。為了重複這個問題，我在和之間執行了一個臨時刪除查詢
begin tran，rollback並擷取了下面的實際計劃：https://www.brentozar.com/pastetheplan/?id=r189liBI4
架構如下：
Daily_Item_Sales_Headers -- ~100 million rows on this system ======================== DlyItmSlsHdr_Key decimal(15,0) primary key nonclustered, DlyItmSlsHdr_PaperworkBatch_Key decimal(15,0), -- FK to parent batch of data that contains other types of data UK_DlyItmSlsHdr_PaperworkBatchKey_Key clustered, unique, unique key located on PRIMARY (DlyItmSlsHdr_PaperworkBatch_Key, DlyItmSlsHdr_Key) Daily_Item_Sales -- ~790 million rows on this system ================ DlyItmSls_Key decimal(15,0) primary key nonclustered, DlyItmSls_DlyItmSlsHdr_Key decimal(15,0), -- FK to header table, cascade delete, UK_DlyItmSls_DlyItmSlsHdrKey_Key clustered unique constraint on (DlyItmSls_DlyItmSlsHdr_Key, DlyItmSls_Key) [columns about sales data]我執行的查詢很簡單：
delete Daily_Item_Sales_Headers where DlyItmSlsHdr_Key = 1該計劃顯示標題刪除正確地級聯到子銷售行。該計劃還顯示了對子表的聚集索引的索引查找。但是，這個對子表的聚集索引搜尋估計有 7.9 億行。實際行數約為 100。 高估計行導致約 60 GB 的記憶體授予。
使用
dbcc show_statistics子表索引，我可以看到昨晚更新了統計數據，樣本量為 2%。直方圖顯示每個父鍵估計在 1 到 ~33,000 行之間。所以統計數據似乎顯示估計應該低得多。為什麼這個刪除查詢會產生如此大的記憶體授權？
我看到了這個關於似乎是由錯誤引起的過多排序記憶體授予的問題，但它對我來說看起來不同，因為這個計劃中沒有排序。級聯到子表時，可能是應用於表線軸的相同錯誤？
由於外鍵級聯，我認為我不能通過在刪除父行之前先刪除子行來解決記憶體授予問題。這是一個 OLTP 系統，最多可同時執行 10,000 個儲存，因此我無法按需刪除外鍵以進行單次刪除。
編輯 2019 年 2 月 28 日下午 1:13 CST
SQL 實例有大約 400 GB 的記憶體分配給它。
該應用程序啟用了以下跟踪標誌：
- 1222：死鎖跟踪
- 4199：查詢處理器修復
- 2312：使用 2014 基數估計器
- 2453：@表變數基數
禁用跟踪標誌會為子表級聯刪除產生不同的估計：
Trace Flag 2312 Trace Flag 4199 Row Estimate =============== =============== ============= on on 790 million rows on off 608 rows (very accurate) off on 1 row off off 1 row
querytraceon為連結問題中提到的標誌 9130添加一個沒有區別。一位同事發現了這篇關於 SQL 2014 中的記憶體錯誤的有趣文章。連結的解決方案是添加
option (MAX_GRANT_PERCENT = 1)到查詢中。https://www.theregister.co.uk/2016/02/09/microsoft_sql_server_2014_bug/
編輯
確切的 SQL Server 版本是 SQL Server 2014 SP2 CU 12。
數據庫兼容級別為 110 - SQL Server 2012。我們暫時無法更改兼容級別。
我相信您在新的 Cardinality Estimator 中遇到了與我相同的錯誤。我在這裡寫了關於這個問題的文章，在這裡進行了更詳細的介紹。
簡而言之，新的基數估計器在估計級聯刪除時要刪除的行時存在錯誤。如果父表上的驅動值遠遠超出其直方圖，CE 將假定子表的每一行都被刪除。
不過幸運的是，我的伺服器接受了很高的估計並決定開始掃描 :(
同樣幸運的是，您正在處理 proc 而不是 ORM 瘋狂，因此有可行的解決方案。一種是添加
DBCC TRACEOFF( 4199)到您的程序中（可能會導致權限問題）。不過，這並不能保證您的未來，因為在 2016+ 4199 狀態對於 CE 錯誤無關緊要。另一種選擇可能是添加OPTION(OPTIMIZE FOR (@1 = <some middle value>)).由於您沒有獲得掃描計劃，因此您也可以使用MAX_GRANT_PERCENT 來加強記憶體授權。