Query processor could not produce a query plan because of the hints defined in this query
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?