Sql-server – Receiving “Query processor could not produce a query plan because of the hints defined in this query” when using FORCESEEK but can use FORCESCAN

filtered-indexhintsindex-tuningsql serversql-server-2016

I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total).
One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that table within a certain date range.
Seems like a good candidate for a filtered index to me.

This is my filtered index:

CREATE NONCLUSTERED INDEX IX_Filtered_Table1_DateField1 
ON Table1 (DateField1, PrimaryKeyField) 
WHERE (DateField1 >= '2/24/20' AND DateField1 < '4/14/20')

This is my query:

DECLARE @MinPrimaryKeyId BIGINT = 2854868995

SELECT TOP 500000 PrimaryKeyField
INTO #Results
FROM Table1 WITH(INDEX(IX_Filtered_Table1_DateField1))
WHERE DateField1 >= '2/24/20'
and DateField1 < '4/14/20'
and PrimaryKeyField > @MinPrimaryKeyId
ORDER BY PrimaryKeyField ASC

When checking out the execution plan, I notice it's using my filtered index but it's defaulting to an Index Scan operation instead of an Index Seek.

If I try try to use the FORCESEEK query hint (in addition to my index hint), I get the classic error:

Query processor could not produce a query plan because of the hints
defined in this query

Why would the query processor be unable to generate an execution plan using a Seek operation on this index when it can do a Scan operation with it? (If I switch out my FORCESEEK query hint with a FORCESCAN it works, which I know is no different than not using a hint at all here.)

Best Answer

SQL Cannot "Seek" as the order of this index is by date first and then by PrimaryKey. As you are selecting with a date that match the full content of the index and as the PrimaryKey are not ordered (they are but only within each specific date), SQL have to scan it.

Try to change your index for this one:

CREATE NONCLUSTERED INDEX IX_Filtered_Table1_PrimaryKeyField
ON Table1 (PrimaryKeyField) 
WHERE (DateField1 >= '2/24/20' AND DateField1 < '4/14/20')

Sql Should then be able to use it (as the date match) and seek into the PrimaryKeyField (and the hint will probably not be required as this will probably what SQL will decide to do anyway)

Related Question