為什麼沒有使用索引？我可以複製計劃嗎？

我一直在開發環境中調整查詢/索引，但在將相同的更改應用於 UAT 環境時無法複製新的查詢計劃。

具體來說，在 UAT 環境中，優化器選擇忽略特定索引，而是對現有唯一約束執行非聚集索引查找，然後對聚集索引執行鍵查找。

查詢的精簡版是：

select  d.dim_date_id 
      ,   f.dim_form_id
      ,   d.date

INTO #TMP
from    DWH.dbo.tbl_fact_outcome f
join    DWH.dbo.tbl_dim_date d      ON d.date >= DATEADD(DAY,-1,f.known_from) and d.date < f.known_to
join    DWH.dbo.tbl_dim_form df     ON  f.dim_form_id = df.dim_form_id
join    DWH.dbo.tbl_dim_question Q  ON f.dim_question_id = Q.dim_question_id

where   (d.flag_latest_day = 'Y' or d.flag_end_of_month = 'Y'  or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y'))
      and d.flag_future_day = 'N'
      and df.flag_latest_form = 'Y'
      and f.deleted = 0           
      and q.question_key like 'R%'

and d.date >= '14/07/2020'

有問題的索引是：

CREATE NONCLUSTERED INDEX [ix_tbl_dim_date_flag_future_day_includes] ON [dbo].[tbl_dim_date]
([flag_future_day] ASC)
INCLUDE([dim_date_id],[date],[flag_end_of_month],[flag_latest_day],[flag_end_of_week],[flag_latest_week])

它不是一個大表，應該只返回一個或兩個日期，相應地過濾來自後續連接的結果。將其他欄位添加到索引中的關鍵列對開發環境沒有任何影響——唯一的改進來自查詢計劃的順序/形狀。

• 開發查詢計劃（好的）在這裡：https ://www.brentozar.com/pastetheplan/?id=SyZjIGA1v
• UAT 查詢計劃（壞）在這裡：https ://www.brentozar.com/pastetheplan/?id=BkBBLzCyv

兩種環境都具有完全相同的表結構和索引，並且使用相同的查詢。開發環境確實有大約一半的整體數據量，但即使我修改其中任一日期範圍​​以返回相似數量的記錄，兩個環境中的查詢計劃仍保持原樣。自從使用了這個查詢計劃以來，開發環境中的性能有了顯著提高，我相當有信心在我的 UAT 環境中看到同樣的情況。

我曾嘗試使用查詢提示來強制索引和/或順序，但無法複製此計劃。

基本上我有兩個問題：

• 為什麼優化器會在這個索引上選擇一個鍵查找？
• 我能做些什麼來強制它遵循開發環境中看到的形狀嗎？

我正在使用 SQL Server 2014 企業版。

---

編輯 - 20/07/2020 - 添加了實際執行計劃

來自開發環境的實際良好執行計劃 - 執行此計劃需要 37 秒： https ://www.brentozar.com/pastetheplan/?id=BJkr-Q7gP

來自 UAT 的實際錯誤執行計劃 - 執行此計劃耗時 33m 42s：https ://www.brentozar.com/pastetheplan/?id=H1nsxX7ew

這完全是一種解決方法，但它對我有用。我有 4 台伺服器執行具有不同數據集的相同數據庫。他們中的三個人會選擇一個最佳計劃，但第四個永遠不會選擇那個計劃，而且總是花費更長的時間。就我而言，我可以看到好的計劃首先從大約 100,000 行中為少數幾行尋找一個表，然後再進行其他聯接。糟糕的計劃會在最後一次進行該搜尋，並且會搜尋該表 500,000 多次。我還嘗試了查詢提示並以不同的方式重新編寫查詢，但沒有成功。

所以我的工作是先手動查詢該表中的那幾行到一個臨時表中，然後加入該表而不是整個表。

因此，在您的情況下，您可以先在臨時表中執行這樣的東西，然後加入它而不是整個 DWH.dbo.tbl_dim_date 表：

select  d.dim_date_id 
  ,d.date
  ,d.flag_future_day 
from    DWH.dbo.tbl_dim_date d 
where   (d.flag_latest_day = 'Y' or d.flag_end_of_month = 'Y'  
         or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y'))
  and d.flag_future_day = 'N'
  and d.date >= '14/07/2020'

在您的情況下，不確定上述查詢是否具有足夠的選擇性以提供真正的幫助，我意識到這是一種解決方法。但有時變通辦法奏效！

另一個想法，你能修改你包含的 NCI 嗎？如果您將日期列添加到索引列而不是 INCLUDE 列，這可能會有所幫助。該查詢基於該日期列完成了許多過濾器。

這不是您問題的直接答案，但是很少有觀察可以使您找到解決問題的正確方向。實際計劃的前幾個觀察結果：

1. 表 tbl_dim_form 中的行數不同。在好的計劃中，行數是 6653269，而在壞計劃中是 9387471。這接近 40%。
2. 表 tbl_fact_outcome 也是如此，在好的計劃中，行數是 28011736，而對於壞計劃，它是 65679017。這是超過兩倍的值。
3. 我沒有檢查其他表，但似乎開發環境和 UAT 環境之間的數據量完全不同。

您可以在此連結上閱讀更多資訊，行數如何影響查詢計劃和其他連接條件。

我下載了 XML 的好壞計劃。如果您使用 SentryOne 計劃瀏覽器，以下是不良計劃中突出顯示的瀏覽器：

讀取的行數以十億計，是實際需要的 3 倍。

在良好計劃的情況下，I/O 殘留要少得多。

所以，你需要先解決這個問題，還要解決 David Browne 在表 tbl_fact_outcome 上提出的觀點。以下是一些可以幫助您的頁面：

https://sqlperformance.com/2016/06/sql-indexes/actual-rows-read-warnings-plan-explorer

“警告：操作導致殘留 I/O”與鍵查找

https://www.sqlshack.com/the-impact-of-residual-predicates-in-a-sql-server-index-seek-operation/

我希望這可以幫助你。

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