Today I ran into the following SQL Server error while trying to run some Filtered Indexes:

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.

In this post I go through the possible reasons for this Query Processor error, and how to solve each of those.

I ran into this issue while I was looking at optimising a query that does a select (it was actually a mental CROSS-TAB query) and UNION across multiple big (HUGE) tables, so I decided to build a Filtered Index in order to speed up access to the underlying tables, as well as create/update some statistics to get SQL to produce an efficient execution plan, anyway thing were going well, I built the Index and wrote the query forcing the utilisation of the newly created Filtered Index, you can check out my post on forcing a sql server index utilization for more details on that.

Anyway, when I tried to run the query I ran into the error shown above, the error is self explanatory, basically when SQL Server Query Processor engine tried to generate an execution plan for the query, it was unable to do so because of the hints used in the query.

If you are forcing a Filtered Index on the query, the error above could be caused by the one of the following reasons:

Different Filter Criteria Between Index and Query

The query returns results that have been filtered from the forced Filtered Index: Basically if you are using a Filtered Index that has a criteria different from the WHERE clause of your query, which means SQL cannot use this index to return all the query rows, hence it fails. Note this is different from the index being a “Covering Index”, which relates to the efficiency of this index in relation to the query it is being used for.

For example, you have a Filtered Index which filters on the DateStamp column, by value of >= ’2011-01-01′, where as your query SELECTs from the table with a WHERE clause filtering on DateStamp >= ’2010-01-01′

o resolve the filter criteria issue, you will need to create a Filtered Index that covers all the required data to satisfy the query, or limit your query to only return the data covered by the index’s WHERE clause.

SQL Server “Thinks” Filter Criteria Between Index and Query is Different

This mainly happens if you wrap your query in a Stored Procedure, and use Stored Procedure Parameters as filters to your query. Because SQL Server needs to create an execution plan for the Stored Procedure that is generic enough to be used across a multitude of Stored Procedure Parameter values, it cannot assume that whatever is calling this procedure will stick to the Filtered Index criteria, hence, to be on the safe side, SQL Server tries to use another Index/Heap to get the data, and since you are forcing the Filtered Index on the table, SQL Server throws an error.

In order to resolve this issue, you need to add the RECOMPILE query option.

By forcing SQL Server to recompile an execution plan every time the stored procedure runs, SQL Server will evaluate at run time whether the Filtered Index is sufficient to return all the required data.

If you are using older versions of SQL Server, you might need to create and run your query using Dynamic SQL, which forces SQL to re-evaluate a new plan each time the query is executed.