2

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
  • 2
    ^ when using a filtered index it is always a good idea to also have the filter column in the index. But just to clarify, in your screen shot you are running the query with the index hint before you have created the index? Also see this probable duplicate. – Aaron Bertrand Nov 15 '18 at 15:17
  • Maybe it's just like that in your screenshot, but are you trying to force the index before creating it? – Tom V Nov 15 '18 at 15:33
  • No fellows, thanks for the heads up, but I have got the index created before I want to use it – Marcello Miorelli Nov 15 '18 at 15:35
  • Have you tried testing a different Filtered index that doesn't explicitly declare the Clustered Index Columns? – pacreely Nov 15 '18 at 16:46
2

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?

1

For people looking for other possible reasons of this error:

In my case I got the same error with my query where somebody used with forceseek hint.

Finally I figured out that table was a HEAP and the creation of a Clustered Index solved the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.