在 SQL Server 中查找異常值統計資訊？

我有一個儲存過程，它在我們的應用程序數據庫（sql server 2016 enterprise，~500gb 數據）中搜尋“壞”數據。業務/流程意義上的“壞”。

執行通常需要 10-30 秒。幾天后，它突然需要 5 分鐘以上才能執行。

我目前的解決方案是重新計算所有統計資訊，並且執行時間再次變低：

EXECUTE dbo.IndexOptimize @Databases = 'myDB',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Ola Hallengren 的索引優化

顯然，重新生成統計資訊會導致新的更好的查詢計劃。是否有針對性的方法來辨識引入慢查詢計劃的誤導性統計數據？或者我怎樣才能找到造成這種情況的原因？表、索引、統計資訊和這個儲存過程很複雜，所以我猜不出來。我可以以程式方式比較更新“之前”和“之後”的統計資訊嗎？

我們有許多過濾索引，這些索引通常非常小，因此 20% 規則可能經常適用於它們。

索引每週優化一次。

在 2016 SP2 / 2017 中，您可以生成一個實際的計劃，它會告訴您使用了哪些統計資訊。在舊版本中，您可以這樣做，但使用Paul White 在此處描述的跟踪標誌。但我相信你是對的，更新統計數據會觸發一個新計劃，但更新它們並不是導致更好計劃的原因，它只是統計數據更新後第一次生成的新計劃。你應該首先弄清楚為什麼計劃會出錯。

既然你提到它是一個搜尋過程，我猜這是一個參數敏感性問題（查看過程定義和一些典型的呼叫將有助於確認）。這裡有兩個想法：

1. 如果問題是由於參數 A 有時提供而有時不提供，請考慮動態 SQL。WHERE您僅使用涉及提供的參數的子句來建構查詢。這讓 SQL Server 可以針對每個獨特的參數組合進行不同的優化（強烈建議與實例級optimize for ad hoc workloads設置結合使用）。
2. 如果問題是由於參數 A 有時會導致少量行，有時會導致大量行，請考慮OPTION (RECOMPILE). 這迫使 SQL Server 每次都重新檢查參數並根據這組參數可能產生的行數進行優化。重新編譯有一些成本，但它帶來的穩定性通常是值得的。

如果 1 和 2 有時都為真，請考慮組合 - 動態建構語句並添加OPTION (RECOMPILE)到末尾。

我在這裡更深入地談論這個：

• #BackToBasics：更新的“廚房水槽”範例

當然，最新的統計資訊會有所幫助，但如果您只是重新使用在更新統計資訊後編譯的第一個計劃，那麼它們毫無價值。如果您第一次搜尋姓氏，如“S%”，第二次搜尋薪水 < 70000，則第二次將使用錯誤的索引。至於過濾索引，是的，您肯定會希望手動更新這些索引，因為它們不太可能落入 20% 的門檻值（或模擬 TF2371 的新門檻值）。但至少如果您使用動態 SQL，您將有機會在（且僅在）合適的時候使用過濾索引計劃。

其他一些閱讀：

• 為什麼我的查詢突然比昨天慢？

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