SQL Server 2016 Bad Query Plan 每週鎖定一次數據庫

每週一次，在過去的 5 週內，大約在一天中的同一時間（清晨，可能基於人們開始使用它時的使用者活動），SQL Server 2016（AWS RDS，鏡像）開始大量超時查詢。

所有表上的 UPDATE STATISTICS 總是立即修復它。

第一次之後，我讓它每晚（而不是每週）更新所有表上的所有統計資訊，但它仍然發生（在更新統計資訊執行後大約 8 小時，但不是每天執行）。

上次，我啟用了查詢儲存，看看我是否可以找到它是哪個特定的查詢/查詢計劃。我想我可以把它縮小到一個：

找到該查詢後，我添加了一個推薦的索引，該索引從這個不常用的查詢中失去（但它確實觸及了很多常用的表）。

錯誤的查詢計劃正在執行索引掃描（在只有 10k 行的表上）。其他以毫秒為單位返回的查詢計劃過去常常執行相同的掃描。最新的查詢計劃，在創建新索引後只會尋找。但即使沒有該索引，在 99% 的情況下，它也會在幾毫秒內返回，但每週，它需要超過 40 秒。

• 超時的壞事：http ://brentozar.com/pastetheplan/?id=rymaWt56e
• 以前不會超時的計劃：http ://brentozar.com/pastetheplan/?id=HyN7ftcpe
• 帶有新索引的最新計劃：http ://brentozar.com/pastetheplan/?id=ryLuGKcag

從 2012 年遷移到 SQL Server 2016 後，這種情況開始發生。

DBCC CHECKDB 不返回錯誤。

1. 新索引會解決問題，使其不再選擇糟糕的計劃嗎？
2. 我應該“強制”現在行之有效的計劃嗎？
3. 我如何確保這不會發生在另一個查詢/計劃中？
4. 這是更大問題的徵兆嗎？

---

我剛剛添加的索引：

CREATE NONCLUSTERED INDEX idx_AppointmetnAttendee_AttendeeType
ON [dbo].[AppointmentAttendee] ([UserID],[AttendeeType])

CREATE NONCLUSTERED INDEX [idx_appointment_start] ON [dbo].[Appointment]
(
   [ProjectID] ASC,
   [Start] ASC
)
INCLUDE (   [ID],
   [AllDay],
   [End],
   [Location],
   [Notes],
   [Title],
   [CreatedByID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

完整的查詢文本：

https://pastebin.com/Z5szPBfu（LINQ 生成，我可以/應該能夠優化選擇的列，但應該與這個問題無關）

我將以與您提出問題不同的順序回答您的問題。

4. 這是更大問題的徵兆嗎？

SQL Server 2016 中的新基數估計器可能會導致該問題。SQL Server 2012 使用舊版 CE，您在該版本上沒有遇到問題。新的基數估計器對您的數據做出不同的假設，並且可以為相同的 SQL 生成不同的查詢計劃。根據您的查詢和數據，您可能會在使用舊版 CE 的某些查詢時體驗到更好的性能。因此，您的數據模型的某些部分可能不是新 CE 的最佳匹配。沒關係，但您現在可能需要解決新的 CE。

即使每天更新統計資訊，我也會擔心查詢性能不一致。需要注意的重要一點是，收集所有表的統計資訊將有效地清除記憶體中的所有查詢計劃，因此您可能會遇到統計資訊問題，或者可能與參數嗅探有關。如果沒有關於數據模型、數據更改率、統計更新策略、如何呼叫程式碼等的大量資訊，很難做出決定。SQL Server 2016 確實提供了一些用於參數嗅探的數據庫級別設置，這可能會有所幫助，但這可能會影響您的整個應用程序，而不僅僅是一個有問題的查詢。

我將拋出一個可能導致這種行為的範例場景。你說：

一些使用者可能有 1 條權限記錄，有的最多 20k。

假設您收集了所有表的統計資訊，這些表清除了所有查詢計劃。根據上面提到的因素，如果當天的第一個查詢是針對只有 1 條權限記錄的使用者，那麼 SQL Server 可能會記憶體一個計劃，該計劃適用於有 1 條記錄的使用者，但適用於有 20k 條記錄的使用者。如果一天中的第一個查詢是針對具有 20k 條記錄的使用者，那麼您可能會為 20k 條記錄製定一個好的計劃。當程式碼針對具有 1 條記錄的使用者執行時，它可能不是最佳查詢，但仍可能在毫秒內完成。這聽起來確實像參數嗅探。它解釋了為什麼您並不總是看到問題，或者為什麼有時需要幾個小時才能出現。

1. 新索引能否解決問題，使其不再選擇糟糕的計劃？

我認為您添加的索引之一將防止出現問題，因為通過索引訪問所需數據將比對錶執行聚集索引掃描更便宜，尤其是當掃描無法提前終止時。讓我們放大查詢計劃的錯誤部分：

[Permission]SQL Server 估計在和上的連接只會返回一行[Project]。對於外部輸入中的每一行，它將對[Appointment]. 將從該表中掃描所有行，但只有與過濾匹配的行[Start]才會返回給連接運算符。在連接運算符中，結果會進一步減少。

如果確實只有一行發送到連接的外部輸入，則上述查詢計劃可能沒問題。但是，如果連接的基數估計錯誤並且我們得到 1000 行，那麼 SQL Server 將對[Appointment]. 查詢計劃的性能對估計問題非常敏感。

不再獲得該查詢計劃的最直接方法是針對該[Appointment]表創建一個覆蓋索引。像索引之類的東西[ProjectId]，[Start]應該這樣做。看起來這正是[idx_appointment_start]您為解決該問題而創建的索引。阻止 SQL Server 選擇查詢計劃的另一種方法是修復連接上的基數估計[Permission]和[Project]。執行此操作的典型方法包括更改程式碼、更新統計資訊、使用舊版 CE、創建多列統計資訊、為 SQL Server 提供有關局部變數的更多資訊（例如帶有RECOMPILE提示）或將這些行具體化到臨時表中。當您需要毫秒級別的響應時間或必須通過 ORM 編寫程式碼時，其中許多技術都不是一個好方法。

您創建的索引[AppointmentAttendee]不是解決問題的直接方法。但是，您將獲得有關索引的多列統計資訊，這些統計資訊可能會阻止錯誤的查詢計劃。索引可能會提供一種更有效的方式來訪問數據，這也可能會阻止錯誤的查詢計劃，但我認為沒有任何形式的保證它不會再次發生僅使用索引打開[AppointmentAttendee].

3. 我如何確保這不會發生在另一個查詢/計劃中？

我理解你為什麼要問這個問題，但這是一個非常廣泛的問題。我唯一的建議是嘗試更好地了解查詢計劃不穩定的根本原因，驗證您是否為您的工作負載創建了正確的索引，並仔細測試和監控您的工作負載。微軟對如何處理 SQL Server 2016 中的新 CE 導致的查詢計劃回歸有一些一般性建議：

將查詢處理器升級到最新版本程式碼的推薦工作流程是：

1. 在不更改數據庫兼容性級別的情況下將數據庫升級到 SQL Server 2016（保持在之前的級別）
2. 在數據庫上啟用查詢儲存。有關啟用和使用查詢儲存的更多資訊，請參閱使用查詢儲存監控性能。
3. 等待足夠的時間來收集工作負載的代表性數據。
4. 將數據庫的兼容級別更改為130
5. 使用 SQL Server Management Studio，評估兼容性級別更改後特定查詢是否存在性能回歸
6. 對於存在回歸的情況，在查詢儲存中強制執行先前的計劃。
7. 如果存在無法強制執行的查詢計劃或性能仍然不足，請考慮將兼容性級別恢復為之前的設置，然後聯繫 Microsoft 客戶支持。

我並不是說您需要降級到 SQL Server 2012 並重新開始，但所描述的一般技術可能對您有用。

2.我應該“強制”現在行之有效的計劃嗎？

這完全取決於你。如果您認為您的查詢計劃適用於所有可能的輸入參數，對查詢儲存的功能感到滿意，並且希望通過強制執行查詢計劃讓您高枕無憂，那麼就去做吧。畢竟，強制執行具有回歸的查詢計劃是 Microsoft 推薦的 SQL Server 2016 升級策略的一部分。

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