Jaa

Forced Parameterization with Filtered Indexes

  • Artikkeli

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

  • Anonymous
    March 29, 2011
    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.

  • Anonymous
    March 29, 2011
    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.