I'm trying to tune a rather annoying query where a number of the tables have something that looks like:

date = '9999-12-31 23:59:59.9999999'

At least a few of the tables are in the 300 mil row range and when I filter this I end up in the 2 mil range. It seemed reasonable to attempt a filtered index.

CREATE TABLE test (col1 int PRIMARY KEY, col2 int, col3 varchar(50), col4 datetime2(7), col5 int);
CREATE INDEX ix_filtered ON test(col2,col3) INCLUDE (col4) 
       WHERE col4 = '9999-12-31 23:59:59.9999999' ;

SELECT col2,col3 FROM test 
WHERE col4 = '9999-12-31 23:59:59.9999999';

However when I checked the query plan it wouldn't use the index. It just did a clustered index scan and a key lookup. Obviously without any data this makes sense but even with all of my data it did the same thing. The reason I haven't bothered supplying any data is this second problem that occurs regardless.

When I tried to force the index to see what the query plan would look like:

SELECT col2,col3 FROM test 
WITH (index (ix_filtered))
WHERE col4 = '9999-12-31 23:59:59.9999999'

I get this error:

Msg 8622, Level 16, State 1, Line 52 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.

I'm trying to figure out why I'm getting the error with the query hint. My guess is that the answer to this will also tell me why the filtered index isn't being used at all.

  • Sample code uses filtered index with or without hint just fine for me on SQL Server 2016, plus a 10.5 RTM instance I just fired up. What's your database collation?
    – kendra
    Jul 25 '16 at 17:24
  • Default is SQL_Latin1_General_CP1_CI_AS. And I just tried it on another instance and it worked fine. I'm going to start checking DB & instance settings. Jul 25 '16 at 17:30
  • Aha! Is forced parameterization on? I can get your errors if I enable that :) If so, you have to use OPTION RECOMPILE on the query / aka make it non-parameterized.
    – kendra
    Jul 25 '16 at 17:33
  • @KennethFisher What is the compatibility mode of the DB? I have run into this error before when the DB compat-level was set to a version prior to filtered indexes being introduced. The SQL syntax allows the creation as that is not affected by compat-level, but the Query Optimizer is sensitive to compat-level. Jul 25 '16 at 17:38
  • 1
    @srutzky It turned out it was the forced parameterization. I added OPTION RECOMPILE (as Kendra suggested) and it worked just fine. Jul 25 '16 at 17:41

We found that this error occurred because forced parameterization was on at the database level. For other readers who want to reproduce it, use Kenneth's sample code in a database with this setting on:

create database filterme;

alter database filterme set parameterization forced;

For parameterized queries, SQL Server doesn't want to cache an execution plan with a filtered index that might not work for other parameter values.

In later versions of SQL Server, the query plan for the "non-hinted" plan would (hopefully) show an "unmatched indexes" warning to alert you better to this. That just didn't exist in SQL Server 2008R2. (Note: I just tested on SQL 2016 and am not seeing the unmatched indexes warning, need to look into more why it isn't showing up with this sample code.)

To get the query to work without changing the database setting, you can use an "Option recompile" hint in queries. That works because it instructs SQL Server not to cache the plan for re-use.


Not the answer you're looking for? Browse other questions tagged or ask your own question.