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.