SQL Server Query Plan – Hints Causing Query Plan Issues

execution-planfiltered-indexindexoptimizationsql-server-2016

There is a similar question, but it is not the same:

The query processor could not produce a query plan

I have the following query, and the following filtered index, and I cannot see any reason why the query cannot use my filtered index described below:

— the query – does not matter if I use the max or just the column, it does not like the index hint

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

— and this is my filtered index – this index is just to optimise the query above

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]

when I run the query, including the index hint, I get the following error message:

enter image description here

Msg 8622, Level 16, State 1, Line 455 Query processor could not
produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET
FORCEPLAN.

Have a missed something?

— adding the column newStatus to the index did not solve the problem, neither in the index nor in the include:

    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]

can it be the compatibility mode?

I noticed that if I remove the filter from the index, then the query accepts it fine. But that's not how I would like it.

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]

enter image description here

Here is the definition of the table in question:

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

Best Answer

as I have seen on this answer when I add option(recompile) to my query it runs fine accepting the index hint:

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

Still when I run the same query without the option(recompile) I get the same error

Msg 8622, Level 16, State 1, Line 479 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

On my test environment however, I could afford running the following operation and after that no more problems with this filtered index:

ALTER DATABASE [JUNOCORE] SET PARAMETERIZATION SIMPLE;

to corroborate to my decision to do this in test I read the following articles:

SQL Server Simple and Forced Parameterization

Blitz Result: Forced Parameterization

Now I feel like changing my [JUNOCORE] database in LIVE also to simple parameterization

This then brings me to the following question:

what elements of the workload or query plan cache I should have a look to decide Simple or Forced Parameterization in my databases?

Related Question