Consider the following scenario:

• You have a database that has PARAMETERIZATION FORCED enabled.
• You have a table using a filtered index.

Here is a demo setup so you can follow along:

USE [master]

GO

CREATE DATABASE [FI_PF_Error_Demo];

GO

-- Set new database to forced parameterization

ALTER DATABASE [FI_PF_Error_Demo]

SET PARAMETERIZATION FORCED WITH NO_WAIT;

GO

-- Create a demo table (and population will not be necessary to demonstrate)

USE [FI_PF_Error_Demo]

GO

CREATE TABLE dbo.FI_PF_Demo_T

(col01 int, col02 int, col03 int);

GO

-- Our filtered index referencing col01 as key and col03 in filter predicate

CREATE NONCLUSTERED INDEX idx_FI_PF_Demo_T_col03

ON dbo.FI_PF_Demo_T(col01)

WHERE (col03 = 1924);

GO

Now take the following query that uses the filtered index (using a hint to force this in the example, since there are no rows populated in this table):

-- Tested on version 10.50.1600

SELECT col01

FROM dbo.FI_PF_Demo_T

WITH (index = idx_FI_PF_Demo_T_col03 )

WHERE col03 = 1924;

The following error is raised upon execution:

Msg 8622, Level 16, State 1, Line 1

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.

Now try turning off forced parameterization:

ALTER DATABASE [FI_PF_Error_Demo] SET PARAMETERIZATION SIMPLE WITH NO_WAIT;

GO

Now if you re-run the SELECT query, you won’t get error 8622.

So what’s going on?

• First of all, you may see 8622 in various contexts – this isn’t just specific to this particular scenario.  Because I’m using a hint, the QP is telling me I’m forcing a non-viable plan that will not be compiled.  But in this demo – the root cause isn’t just about the hint I designated.
• With forced parameterization enabled, the SELECT query I executed is getting parameterized first.   So for example col03 = 1924 becomes col03 = @p1.
• This means that my original value of 1924 is not being considered when compiling the plan.  If I had a value not covered by the filtered index, then the filtered index I’m forcing in the hint will not potentially fulfill all potential values.
• Switching back to simple parameterization works because now the query isn’t being parameterized and is compiled based on the 1924 value for col03.