Forced Parameterization with Filtered Indexes


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:


-- Create demo database

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.


Comments (2)

Cancel reply

  1. Kendra Little says:

    Thanks for posting this– and I love you clear explanation.

    This is something I learned recently, and then promptly forgot it. I think it'll stick this time.

  2. josephsa says:

    Thanks Kendra!

    I hear you on that forgetting part.  So much to know it's like Lucy Ricardo and the conveyor belt.  I'm picking things up and then dropping them on the floor.

Skip to main content