由於此查詢中定義的提示，查詢處理器無法生成查詢計劃

有一個類似的問題，但不一樣：

查詢處理器無法生成查詢計劃

我有以下查詢和以下過濾索引，我看不出查詢不能使用下面描述的過濾索引的任何原因：

– 查詢——不管我使用的max是列還是只使用列，它不喜歡索引提示

SELECT -- MAX(AC1.changeDate)   
         AC1.changeDate
         FROM [dbo].[applicationStateChange]  AS ac1  WITH(INDEX(FI_ASC_ChangeDate))  
         WHERE ac1.applicationID = 130002
         AND AC1.newStatus = 'PLC'  

– 這是我的過濾索引——這個索引只是為了優化上面的查詢

CREATE NONCLUSTERED INDEX FI_ASC_ChangeDate  
ON [dbo].[applicationStateChange] (   applicationID DESC)  
INCLUDE ( [changeDate] )
WHERE newStatus = 'PLC'  
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = On, 
DROP_EXISTING = ON, 
DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) 
ON [NONCLUSTERED_INDEXES]

當我執行查詢時，包括index hint，我收到以下錯誤消息：

消息 8622，級別 16，狀態 1，行 455 由於此查詢中定義的提示，查詢處理器無法生成查詢計劃。在不指定任何提示且不使用 SET FORCEPLAN 的情況下重新送出查詢。

有什麼遺漏嗎？

– 將列添加newStatus到索引中並沒有解決問題，無論是在索引中還是在包含中：

   CREATE NONCLUSTERED INDEX FI_ASC_ChangeDate  
   ON [dbo].[applicationStateChange] (   applicationID DESC, newStatus ASC)  
   INCLUDE ( [changeDate] )
   WHERE newStatus = 'PLC'  
   WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = On, 
   DROP_EXISTING = ON, 
   DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) 
   ON [NONCLUSTERED_INDEXES]


CREATE NONCLUSTERED INDEX FI_ASC_ChangeDate  
ON [dbo].[applicationStateChange] (   applicationID DESC)  
INCLUDE ( [changeDate],newStatus )
WHERE newStatus = 'PLC'  
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = On, 
DROP_EXISTING = ON, 
DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) 
ON [NONCLUSTERED_INDEXES]

可以是兼容模式嗎？

我注意到，如果我filter從索引中刪除 ，那麼查詢會很好地接受它。但這不是我想要的。

CREATE NONCLUSTERED INDEX FI_ASC_ChangeDate  
ON [dbo].[applicationStateChange] (   applicationID DESC,newStatus
)  
INCLUDE ( [changeDate])
--WHERE newStatus = 'PLC'  
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = On, 
DROP_EXISTING = ON, 
DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) 
ON [NONCLUSTERED_INDEXES]

以下是相關表的定義：

IF OBJECT_ID('[dbo].[applicationStateChange]') IS NOT NULL 
DROP TABLE [dbo].[applicationStateChange] 
GO
CREATE TABLE [dbo].[applicationStateChange] ( 
[applicationID]      INT                              NOT NULL,
[changeDate]         DATETIME                         NOT NULL,
[oldStatus]          CHAR(3)                              NULL,
[newStatus]          CHAR(3)                              NULL,
[oldStatusReasonID]  INT                                  NULL,
[newStatusReasonID]  INT                                  NULL,
[oldStatusReason]    VARCHAR(60)                          NULL,
[newStatusReason]    VARCHAR(60)                          NULL,
[oldOnHold]          BIT                                  NULL,
[newOnHold]          BIT                                  NULL,
CONSTRAINT   [PK_applicationStateChange]  PRIMARY KEY CLUSTERED    ([applicationID] asc, [changeDate] asc) WITH FILLFACTOR = 90,
CONSTRAINT   [FK_applicationStateChange_application]                             FOREIGN KEY ([applicationID]) REFERENCES [application]([applicationID]))

GO

CREATE NONCLUSTERED INDEX [IX_applicationStateChange_ChangeDate] 
  ON [dbo].[applicationStateChange] ([changeDate] desc)

CREATE NONCLUSTERED INDEX [FI_ASC_ChangeDate] 
  ON [dbo].[applicationStateChange] ([applicationID] desc, [changeDate] asc, [newStatus] asc)
  WHERE ([newStatus]='PLC')
  WITH FILLFACTOR = 100

正如我在這個答案中看到的那樣，當我添加option(recompile)到我的查詢中時，它執行良好，可以接受索引提示：

SELECT   MAX(AC1.changeDate)   
        -- AC1.changeDate
         FROM [dbo].[applicationStateChange]  AS ac1  WITH(INDEX(FI_ASC_ChangeDate))  
         WHERE AC1.newStatus = 'PLC'  
OPTION (RECOMPILE)

仍然當我執行相同的查詢時without，option(recompile)我得到相同的錯誤

消息 8622，級別 16，狀態 1，行 479 由於此查詢中定義的提示，查詢處理器無法生成查詢計劃。在不指定任何提示且不使用 SET FORCEPLAN 的情況下重新送出查詢。

然而，在我的測試環境中，我可以負擔得起執行以下操作，之後這個過濾索引不再有問題：

ALTER DATABASE [JUNOCORE] SET PARAMETERIZATION SIMPLE;

為了證實我在測試中這樣做的決定，我閱讀了以下文章：

SQL Server 簡單和強制參數化

閃電戰結果：強制參數化

現在我想將我[JUNOCORE] database的 in LIVE 也更改為簡單的參數化

這讓我想到了以下問題：

我應該看看工作負載或查詢計劃記憶體的哪些元素來決定我的數據庫中的簡單參數化還是強制參數化？

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