恢復日誌投遞到備伺服器後，第一個儲存過程執行慢

我們已將日誌傳送設置到備用/只讀的輔助 SQL 伺服器，以解除安裝所有 SSRS 報告生成。

這在以下規定的限制範圍內工作正常：

1. 在事務日誌恢復期間踢出使用者（我們通過設置多個實例並使用循環調度恢復最近的事務日誌來解決這個問題）
2. 數據最多在計劃的事務日誌備份/恢復作業指示的時間範圍內過期。

不幸的是，第一次執行任何/所有儲存過程時，在事務日誌恢復後，完成所需的時間比正常情況要長得多。同一儲存過程的所有後續執行都在預期時間內完成。如果我們然後執行另一個儲存過程，第一次它很慢，並且所有後續執行都在預期的時間內完成。

作為參考，執行的差異通常是 ~00:02 與第一次執行時 ~01:00 相比。

我認為這與伺服器執行統計資訊或儲存過程參數嗅探/儲存執行計劃有關。

有沒有辦法解決這個問題？或者這是事務日誌恢復所固有的？

如果它只是第一次執行任何儲存過程，我們可以通過在還原時執行任何儲存過程來輕鬆解決這個問題，但它似乎會影響所有儲存過程的第一次執行。

我嘗試count( * )在 11 個表上執行我用於測試觸摸的儲存過程。第一次執行時間為 00:32，隨後的 count(*) 時間為 00:00。不幸的是，這對儲存過程的第一次執行沒有任何影響。

is_temporary在執行儲存過程之前或之後， 我在主伺服器或輔助伺服器上都看不到任何統計結果。

我目前正在使用 SQL Server 2012

查詢
執行計劃：乍一看，查詢執行計劃似乎有很大不同，但是，在保存執行計劃並打開生成的 .sqlplan 文件後，它們完全相同。差異似乎來自我使用的不同版本的 SSMS，主伺服器上的 2014 和輔助伺服器上的 2018。在輔助節點上查看執行計劃時，它會顯示在每個節點的百分比和時間成本 ### of ### (##%) 下方——這些數字和實際執行計劃都不會在進一步執行時發生變化。
我還包括了客戶端統計數據，它們顯示幾乎完全相同，唯一的區別是主伺服器執行伺服器回复的等待時間為 1.4 秒，而輔助伺服器需要 81.3 秒。

正如您所預測的，我確實在第一次執行時看到了大量 PAGEIOLATCH_SH 鎖：

diff after first exec vs diff after second exec
waiting_tasks_count    10903    918  
wait_time_ms          411129  12768  

關於這種情況的一個奇怪的事情是，除了設置的循環多實例部分之外，我們已經讓我們的生產 SSRS 伺服器從備用/只讀數據庫讀取，該數據庫由定期事務日誌提供並且沒有經歷這些在第一次執行儲存過程時會減慢速度。但是，每次恢復事務日誌時，我們的使用者都會被踢出，這是上述設置應該解決的問題。

這裡有一些可能發生的事情，這裡有一個非詳盡的列表：

• 執行計劃記憶體被日誌恢復清除，因此第一次需要重新編譯計劃。如果您的計劃有很長的編譯時間，這可以解釋差異。與後續執行相比，您沒有確切提到第一次執行的延遲時間

  • 這似乎是最不可能的 - 您可以在實際執行計劃 XML 中看到您的計劃編譯時間

• 緩衝池在恢復過程中也被清除，因此必須在第一次執行時從磁碟讀取所有數​​據

  • 如果是這種情況，PAGEIOLATCH*如果您檢查等待統計資訊，您可能會在初始執行期間看到高等待

您可以採取一些措施來緩解這種情況

• “預熱”緩衝區記憶體（通過使用 將重要表中的所有數據讀取到記憶體中SELECT COUNT(*) FROM dbo.YourTable），
• 通過執行所有關鍵儲存過程作為恢復後步驟來“預熱”proc 記憶體

為我們提供執行計劃的“快”和“慢”範例可以幫助我們準確追踪正在發生的事情。

---

如果您使用的是 SQL Server 2012 或更高版本，則同步統計資訊更新可能會導致延遲。這些“可讀的輔助統計資訊”在 TempDB 中創建，因為日誌傳送輔助是只讀的。您可以在此處閱讀更多相關資訊（這篇文章是關於 AG 的，但同樣適用於這種情況）：

AlwaysOn：在可讀輔助、只讀數據庫和數據庫快照上提供最新統計資訊

如果這是導致您速度變慢的問題，那麼一種解決方案是查找這些統計資訊，然後在生產數據庫中創建它們，以便它們是最新的並且在恢復後可用。您可以使用以下查詢查找臨時統計資訊：

SELECT * FROM sys.stats WHERE is_temporary = 1;

---

根據您提供的等待統計資訊以及計劃相同的事實，這是非常確定的，因為緩衝池已被日誌還原清除。

在正常執行中，您將獲得 12,768 毫秒（幾乎 13 秒）的 IO 等待。

在第一次執行時，您將獲得 411,129 毫秒（幾乎 7分鐘）的 IO 等待。

由於實際過程與查詢使用的索引不同，您嘗試的SELECT COUNT(*)方法可能沒有幫助。COUNT(*)你有幾個選擇：

1. 遍歷每個執行計劃並記下正在使用的索引，然後將這些索引作為還原後步驟拉入記憶體 - 這次使用索引提示 ( SELECT COUNT(*) FROM dbo.YourTable WITH (INDEX (IX_Index_Being_Used_By_Proc)))
2. 完成編寫腳本的過程以將每個過程作為恢復後步驟執行（這似乎比選項 1 容易一些）
3. 調整查詢，使它們不需要進行如此多的讀取（不確定這是否可行）
4. 加速 I/O 子系統 - 獲得更快的磁碟、本地 SSD、更多的 SAN 通道等（這可能是最困難和最昂貴的選擇

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