未選擇過濾索引，並拒絕提示

我試圖理解為什麼非聚集索引在過濾時不適用於給定查詢。我的（大）查詢的相關部分是這樣的：

) results
JOIN BE_Insurance ins
ON results.PayorId = ins.Id

在選擇中，我只抓取 ins.name。我最初創建的索引是這樣的：

CREATE INDEX IX_BE_Insurance_PayorId_PayorName
ON      BE_Insurance (Id) INCLUDE (Name)
WHERE ParentId IS NULL

我的查詢是這樣的，只會選擇具有 NULL parentId 的 payorIds，但我理解優化器不願意選擇它。但是當我添加一個提示來嘗試強制索引時，整個事情就出錯了。

由於此查詢中定義的提示，查詢處理器無法生成查詢計劃。在不指定任何提示且不使用 SET FORCEPLAN 的情況下重新送出查詢。

我認為它會按照我的提示進行，如果我得到一些錯誤的數據並且索引缺少一些需要的值，可能會在執行時出錯。從索引中刪除過濾器會導致它被查詢成功選擇（即使沒有提示）。

帶有 WHERE 子句的索引是否不適合這樣的查詢？只有當優化器可以保證過濾器有效並且不會產生任何缺失值時，它們才符合條件？

---

根據要求，這是整個查詢，這仍在進行中。

SELECT results.*
FROM (
       SELECT auths.*,
              worked.WorkedHours,
              worked.WorkedUnits,
              worked.WorkedAmount,
              worked.ActiveClients
       FROM (
               SELECT PayorId,
                      SUM(AuthHours) AuthHours, 
                      SUM(AuthUnits) AuthUnits, 
                      SUM(AuthAmount) AuthAmount
               FROM (
                       --DYNAMIC TEMPLATE ----------------------------------------------------------------------------------
                       SELECT PayorId,
                              PayorName,
                              AuthHours AuthHours, 
                              AuthUnits AuthUnits, 
                              AuthAmount AuthAmount
                       FROM PayorAuthorizations_Level1Data_Authorizations auths WITH(NOEXPAND)
                       WHERE OrganizationId = @organizationId AND StartDate <= @endDate AND @startDate <= EndDate
                       --/DYNAMIC TEMPLATE----------------------------------------------------------------------------------

                       --INTERSECT / EXCEPT dynamically generated queries 
               ) q
               GROUP BY PayorId
       ) auths
       JOIN (
               SELECT PayorId,
                      SUM(ISNULL(TotalWorkedHours, 0)) WorkedHours,
                      SUM(ISNULL(TotalWorkedUnits, 0)) WorkedUnits,
                      SUM(ISNULL(TotalWorkedAmount, 0)) WorkedAmount,
                      COUNT(DISTINCT clientId) ActiveClients
               FROM (
                       --DYNAMIC TEMPLATE ----------------------------------------------------------------------------------
                       SELECT PayorId,                   
                              TotalWorkedHours,
                              TotalWorkedUnits,
                              TotalWorkedAmount,
                              clientId clientId
                       FROM PayorAuthorizations_Level1Data_CurrentlyWorked worked WITH(NOEXPAND)
                       WHERE OrganizationId = @organizationId AND StartDate <= @endDate AND @startDate <= EndDate
                       --/DYNAMIC TEMPLATE----------------------------------------------------------------------------------

                       --INTERSECT / EXCEPT other dynamically generated queries 
               ) q
               GROUP BY PayorId
       ) worked
       ON auths.payorId = worked.payorId
) results
JOIN BE_Insurance ins WITH (INDEX(IX_BE_Insurance_PayorId_PayorName))
ON results.PayorId = ins.Id
OPTION(FORCE ORDER, MERGE JOIN)

我認為它會按照我的提示進行，如果我得到一些錯誤的數據並且索引缺少一些需要的值，可能會在執行時出錯。

如果查詢優化器可以保證（在其推理框架內）可以從索引中提供所有可能的匹配，則查詢優化器將僅在查詢計劃中使用過濾索引。這是設計使然，以避免您描述的那種執行時錯誤。

未能從我的非聚集索引中針對聚集索引鍵查找導致嵌套循環連接，大概是為了獲取 parentId。包含父 ID 消除了這一點，並給我留下了一個很好的非聚集索引掃描。

這是一個已知的電流限制。將過濾的列添加到鍵或包含列表是標準的解決方法，也是出於各種半相關原因的目前最佳實踐。

強制命令，合併連接是絕對需要的。

除非您完全了解所有後果，否則請務必小心使用此類提示（指令） 。FORCE ORDER特別是一個非常強大且範圍廣泛的提示，具有許多不明顯的副作用，包括聚合運算符的放置，以及子查詢和公用表表達式的評估順序。

在大多數情況下，您應該嘗試編寫為查詢優化器提供足夠優質資訊的查詢，以便在沒有提示的情況下做出正確的決定。暗示的計劃今天可能是“最佳的”，但隨著數據量和/或分佈隨著時間的推移而變化，它可能不會保持不變。

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