首次執行大查詢的性能問題

我遇到了一個查詢問題，通常在第一次執行時很慢，以至於它超時，然後在載入到記憶體後在一秒鐘內執行。

查詢看起來像這樣

exec sp_executesql N'SELECT
   [Project1].RecordID AS [RecordId],
   ...
   FROM ( SELECT
       [Extent1].RecordId] AS [RecordId],
       ...
       FROM Items AS [Extent1]
       LEFT OUTER JOIN ItemParameters as [Extent2] 
       ON [Extent1].[RecordId] = [Extent2].[SampleId]
       WHERE ...
   ) as [Project1]
   ORDER BY [Project1].CreatedDate DESC, [Project1].RecordId ASC, ...

查詢是由 EntityFramework 為我生成的，並且只有一個連接。Items 表是 ~350 萬行和 ~2.5 GB 數據，ItemParameters 是 ~2360 萬行和 ~19.2 GB 數據。該查詢返回 11400 行，據我所知，它正確地命中了索引。

我已經編寫了一個測試程序，它永遠循環執行此操作：

• 重新啟動 SQL Server 服務 (MSSQLSERVER)。
• 睡6分鐘。讓它啟動。
• 執行上面的查詢。
• 將執行時間寫入文件。

結果令人不安。有時需要 4 分鐘，有時需要 4 秒，我不明白為什麼。它在 VM Ware 虛擬機上執行。在我的開發人員筆記型電腦上，相同的查詢（針對三分之一的數據）在 3 秒內完成。

我用set statistics io onand執行它，set statistics time on輸出如下所示：

(11409 rows affected)
Table 'ItemParameters'. Scan count 3803, logical reads 19689, physical reads 2724, read-ahead reads 4261, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Items'. Scan count 109, logical reads 40423, physical reads 4, read-ahead reads 4089, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 546 ms,  elapsed time = 92636 ms.

SQL Server Execution Times:
  CPU time = 702 ms,  elapsed time = 94012 ms.

我為提高性能所做的事情：

• sp_blitzFirst從https://www.brentozar.com/blitz/執行。什麼都沒給。
• 根據https://www.brentozar.com/blitz/max-memory/將 SQL Max 記憶體設置為我的 32 GB 的 90% 。
• 重建所有索引和列統計資訊。
• 禁用 SQL 全文服務。我們沒有使用它。
• 多次重啟伺服器。

我能做些什麼來確保查詢每次都足夠快地執行？任何幫助是極大的讚賞。

編輯

• 性能不佳查詢的執行計劃（132 秒）：https ://www.brentozar.com/pastetheplan/?id=Syxbf-q5m Ex
• 針對記憶體的執行計劃（1 秒）：https ://www.brentozar.com/pastetheplan/?id=ByegNZcqX

我能做些什麼來確保查詢每次都足夠快地執行？

這不太可能是查詢性能問題。您執行的 IO 非常少，使用的 CPU 時間也非常少，而且查詢處理的數據不多。可能的解決方案是解決導致查詢有時執行這麼長時間的硬體或配置問題。在您的一次執行中，查詢經過了 92636 毫秒的時間，但只使用了 546 毫秒的 CPU 時間，這意味著 SQL Server 在查詢執行期間等待的時間超過 92 秒。根據您對問題的描述，伺服器上可能存在嚴重的 IO 問題。11000 次物理讀取並沒有那麼多，而且您所看到的也不正常。

您沒有包括您的 SQL Server 版本，但為了獲得有關該問題的更多資訊，我將查看等待統計資訊。您似乎可以控制伺服器，因此您可以嘗試拍攝sys.dm_os_wait_stats DMV的前後快照，並查看查詢完成執行後哪些等待增加。該 DMV 是伺服器範圍的，因此如果可能，您將希望避免執行其他查詢。您還可以通過執行以下 T-SQL 在執行查詢之前立即重置 DMV：

DBCC SQLPERF ("sys.dm_os_wait_stats" , CLEAR);

等待統計資訊應該讓您確認 IO 是否確實是問題所在。您可能需要與負責 VM 或儲存的管理員合作來解決此問題。

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