Sql-server – Why can’t the query processor produce a query plan with a FORCESEEK hint when I’m INNER JOINing by the clustered index on both indexed views

index-tuningmaterialized-viewperformancequery-performancesql serversql-server-2016

I'm getting the following error when I try to use the FORCESEEK hint with the query below:

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

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1 AS A WITH (FORCESEEK)
INNER JOIN dbo.IndexedView2 AS B
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2

IndexedView1 and IndexedView2 are both schemabound indexed views, and there's a unique clustered index on both views on the fields IndexedField1, IndexedField2.
Not sure if relevant, but the clustered index is 40 bytes big (per row).

Here's an example of exactly what both views look like under the hood:

SELECT 
    IndexedField1,
    RIGHT(CONVERT(VARCHAR(34), HASHBYTES('MD5', OtherField1 + '||' OtherField2)), 32) AS IndexedField2
FROM dbo.Table1

Nothing too fancy, and no hints inside the view's query.

This is the estimated execution plan (without trying to use a query hint):
Estimated Execution Plan

Best Answer

Here's a repro. It's documented that you can't use FORCESEEK on an indexed view without NOEXPAND.

EG

drop table if exists table1
drop table if exists table2
go
create table Table1(IndexedField1 int, OtherField1 nvarchar(200), OtherField2 nvarchar(200))
create table Table2(IndexedField1 int, OtherField1 nvarchar(200), OtherField2 nvarchar(200))

go

create view IndexedView1
with schemabinding
as
SELECT 
    IndexedField1,
    RIGHT(CONVERT(VARCHAR(34), HASHBYTES('MD5', OtherField1 + '||' + OtherField2)), 32) AS IndexedField2
FROM dbo.Table1

go

create view IndexedView2
with schemabinding
as
SELECT 
    IndexedField1,
    RIGHT(CONVERT(VARCHAR(34), HASHBYTES('MD5', OtherField1 + '||' + OtherField2)), 32) AS IndexedField2
FROM dbo.Table2

go

create unique clustered index pk_IndexedView1 on IndexedView1(IndexedField1,IndexedField2)
create unique clustered index pk_IndexedView2 on IndexedView2(IndexedField1,IndexedField2)

go

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1 AS A WITH (FORCESEEK)
INNER JOIN dbo.IndexedView2 AS B
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2

    /*
Msg 8622, Level 16, State 1, Line 34
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.
*/
go
drop table if exists #TEMP

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1  AS A with (noexpand)
INNER JOIN dbo.IndexedView2 AS B with (noexpand)
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2

executes with plan:

enter image description here

And after applying NOEXPAND, and specifying that that the plan should only involve the view indexes, you can then use the FORCESEEK hint, or specify the join style (MERGE, LOOP, or HASH) if you want.

EG

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1 AS A WITH (NOEXPAND)
INNER MERGE JOIN dbo.IndexedView2 AS B
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2
Related Question