SQL Server 有時會使用更多的 CPU 進行查詢

我有一個儲存過程，其中包含一個非常繁重的 SELECT 語句，其中包括五個連接並FieldName LIKE '%searchterm%'在多個列上使用。此查詢通常在大約 2-5 秒內完成。但有時（在看似隨機的時間點）伺服器會進入查詢需要大約 120 秒才能完成的狀態。這種狀態可以持續幾分鐘到幾個小時。我有跡象表明您可以通過呼叫來結束慢速狀態DBCC FREEPROCCACHE，但我不完全確定。我正在使用 SQL Server 11.0.3153。

我已經使用分析器工具來跟踪伺服器。大多數情況下，查詢需要 900 個 CPU 和 500 000 次讀取，但在慢速狀態下，它需要 100 000 個 CPU 和 7.5*10^6 次讀取。即使伺服器處於慢速狀態，它似乎只會影響我的 .NET 應用程序建立的連接，我仍然可以通過 SQL Server Management Studio 快速執行儲存過程。

查詢看起來像這樣：

SET @search = REPLACE(@search, ' ', '%');
SET @search = '%' + @search + '%';

SELECT
   ...
FROM
   Table1 t1,
   Table1 t2
   /* The real query uses 3 more joined tables left out here */
WHERE
   t1.id = t2.t1_id
   AND (
       (t1.col1 LIKE @search)
       OR (t2.col1 LIKE @search)
       OR (t1.id IN (SELECT t1_id FROM Table3 t3 WHERE t3.col1 LIKE @search)
       )
ORDER BY
   SomeCol DESC,
   SomeOtherCol DESC
OFFSET @row_offset ROWS FETCH NEXT @row_count ROWS ONLY

誰能告訴我這裡發生了什麼？不一致行為的原因可能是什麼？

這聽起來像是在使用不適當的查詢計劃。該查詢可以採用兩種明顯的路徑，並且可能會根據@row_offset 和@row_count 的值在它們之間做出決定：對於較小的偏移量，最好SomeCol,SomeOtherCol按順序掃描並應用複雜的過濾子句（在WHEREandJOIN ON子句中) 到每一行，直到找到足夠的滿足@row_count。對於許多其他情況，這將是一個非常糟糕的方法。

更具體地說，我們需要有關其他表、索引/鍵以及每個表內容的大小/分佈的類型的詳細資訊。

我懷疑正在發生的是，當該計劃不適合目前輸入值時，正在使用記憶體的查詢計劃。當您執行DBCC FREEPROCCACHE記憶體時，它會被清除，因此它被迫創建一個新計劃並使用它。

如果它是一個過程而不是臨時查詢，您可以通過附加到直接 SQL 和儲存過程定義來嘗試RECOMPILE提示- 這告訴 SQL Server 不要費心檢查過程記憶體並始終生成一個新計劃。這當然意味著在使用記憶體計劃而不是重新評估統計數據和重新計劃會更快的情況下，您會失去好處 - 這可能是一個可以接受的權衡，但顯然您需要進行基準測試並確保您不會降低整體性能超過可接受的範圍（如果是這樣，您將需要不同的策略））。OPTION(RECOMPILE)``WITH RECOMPILE

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