為什麼此 OPTIMIZE FOR UNKNOWN 將我的查詢提高了幾秒鐘？

好的，所以我有一個我們在 SSRS 報告中使用的非儲存過程查詢。這個查詢非常慢（我已經在過去兩個小時內執行了這個查詢的原始版本，但仍然沒有完成），為了改進它，我從頭開始重寫它，我想出了以下內容：

現在這是無聊的單詞問題部分：

我們希望為TOP 5每個銷售代表提取客戶列表，但從該列表中排除TOP 10總客戶。（所以如果 John Doe 有客戶端 A、B、C、D 和 E，並且客戶端 C 是前 10 名之一，那麼只拉 A、B、D 和 E。）

為此，第一個查詢使用了 a IN (... NOT IN ( ) )，所以我認為嵌套 ofIN是問題所在，為了重寫它，我做了一個OUTER APPLY真的破壞了一切。

無論如何，我修復了所有這些並執行了查詢，但仍然需要 10-15 秒，我認為這是參數嗅探。為了調查，我在 SSMS 中執行了查詢，添加了OPTION (RECOMPILE)（查看會生成什麼查詢計劃），並得到以下資訊：

可以在 Brent Ozar 的“粘貼計劃”中查看。產生這個的查詢是：

DECLARE @Top10Temp TABLE (Id INT)
INSERT INTO @Top10Temp
SELECT TOP 10 Id
FROM Object1
WHERE Column2  = @ReportId
 AND Column3  = 0
GROUP BY Id
ORDER BY SUM(Column4 + Column5) DESC

SELECT Object2.*
FROM Object1 AS Object2
OUTER APPLY (
   SELECT TOP 5
       Object3.Id,
       SUM(Object3.Column4 + Object3.Column5) AS Column6
   FROM Object1 AS Object3
   WHERE Object3.Column3 = 0
     AND Object3.Column7 = Object2.Column7
     AND Object3.Column2 = @ReportId
   GROUP BY
       Object3.Id
   ORDER BY
       SUM(Object3.Column4 + Object3.Column5) DESC
) AS Object4
WHERE Object2.Column2 =      @ReportId
 AND Object2.Column3 =      0
 AND Object2.Id      =      Object4.Id
 AND Object2.Id      NOT IN (SELECT Id FROM @Top10Temp)
ORDER BY Object2.Column7
OPTION (RECOMPILE)

現在相同的查詢但OPTION (OPTIMIZE FOR UNKNOWN)生成了以下計劃：

也可以在“粘貼計劃”中查看。該計劃在不到 1 秒的時間內執行完畢。

如果我添加OPTION (OPTIMIZE FOR (@ReportId = #))， where#與變數相同，@ReportId我會得到與第二個相同的查詢計劃。

我做錯什麼了嗎？我無法理解發生了什麼，因此非常感謝任何資訊。（我也真的不喜歡試圖通過提示來影響優化器，但如果有必要我會保留它。）

“為了調查，我在 SSMS 中執行了查詢……”這就是問題所在。局部變數使用統計的密度向量產生更好的行估計，因此已經針對 UNKNOWN 進行了優化。參數化動態 SQL 使用直方圖，它提取給定部分的整個行數。

查看每個粘貼計劃連結的估計行數與實際行數。第二個連結比第一個連結有更好的估計。

我會將您的 SSRS 查詢部署到開發實例並執行一些測試，因為我懷疑您可能存在性能問題。

順便說一句，如果可以的話，更新這些野獸表上的統計資訊或重建索引。

連結： 統計直方圖和密度向量內部

慢速計劃從節點 4 處的索引查找得出的基數估計較差。估計的行數為 1，但實際行數為 3261。這是查找謂詞：

Seek Keys[1]: Prefix: Database1.Schema1.Object1.Column2, Database1.Schema1.Object1.Column3 = Scalar Operator(ScalarString7), Scalar Operator(ScalarString2)

您正在過濾同一個表中的兩個不同列。通常 SQL Server 沒有足夠的資訊來為該場景提供精確的估計，因此它會根據您的 CE 版本、更新檔、跟踪標誌等做出建模假設。例如，它可能假設列沒有相關性並將選擇性相乘。如果過濾器之間存在某種相關性，這可能會導致估計值偏低。

總的來說，我會說，如果您在估計不佳的情況下獲得了良好的表現，那麼您可能很幸運，並且您的運氣可能會在某個時候耗盡。我會嘗試修正這個估計。我不能給你準確的說明，因為缺少太多資訊（由於智慧財產權問題，你將無法分享一些缺失的資訊），但我可以說多列統計或索引可能會有所幫助。在過濾到臨時表後儲存表的主鍵是一種應該始終有效的方法。通過更準確的估計，我希望看到一個類似於快速計劃的查詢計劃。

OPTION (RECOMPILE)通過添加提示，您沒有做錯任何事情。你可能只是因為運氣不好而表現不佳。參數嵌入優化通常有助於而不是導致問題。OPTIMIZE FOR UNKNOWN導致 SQL Server 以不同的方式使用統計對象，並且碰巧您在使用它時得到的估計更接近現實。

我不會OPTIMIZE FOR UNKNOWN用作長期解決方案。查詢計劃不會根據@ReportId您更改變數值時可能導致問題的值而改變。這也是一種間接修復，您承認您不了解它是如何工作的。最好通過修復基數估計或戰略性地將中間結果具體化到臨時表中來更直接地解決問題。作為一般規則，您應該避免使用表變數，因為它們沒有統計資訊。表變數的案例非常有限，我建議您僅在別無選擇時使用它們。

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