Combine Table Hints INDEX and FORCESEEK with Two Joins Not On PK

See first query below.

Can NOT combine table hint Index and forceseek with two joins and the joins are not on the PK.

How to make the first query compile?

Interesting

• if just one join or the other then can combine index and forceseek hints

• if the index is the PK then can combine 2 joins and have both hints

-- compiler fails 
-- 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 Count(Distinct([docSVsys].[sID])) 
From [docSVsys] with (nolock) 
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek ) 
       On [docSVtext].[sID] = [docSVsys].[sID]
Left Join [docMVtext] with (nolock, Index(ix_docMVtext_value_sID), forceseek) 
       On [docMVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'  or
      [docMVtext].[value] = 'doug' 

--  can do one join
Select Count(Distinct([docSVsys].[sID])) 
From [docSVsys] with (nolock) 
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek ) 
       On [docSVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'

--  can do the other join
Select Count(Distinct([docSVsys].[sID])) 
From [docSVsys] with (nolock) 
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek ) 
       On [docSVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'

-- if on the PK then can do forceseek on two join   
Select Count(Distinct([docSVsys].[sID])) 
From [docSVsys] with (nolock, INDEX(PK_docSVsys)) 
Left Join [docSVtext] with (nolock, Index(PK_docSVtext), forceseek ) 
    On [docSVtext].[sID] = [docSVsys].[sID]
Left Join [docMVtext]  with (nolock, Index(PK_docMVtext), forceseek) 
    On [docMVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'
   or [docMVtext].[value] = 'doug'

This does what I want and runs 100 times faster.

This has almost an identical query plan to the answer from y

I would also test this rewriting (aasuming that sID is the primary key of docSVsys):

SELECT COUNT(*)              
FROM [docSVsys] AS d
WHERE EXISTS
      ( SELECT * 
        FROM [docSVtext] AS sv
        WHERE sv.[sID] = d.[sID] 
          AND sv.[value] = 'doug'
      )
   OR EXISTS
      ( SELECT * 
        FROM [docMVtext] AS mv
        WHERE mv.[sID] = d.[sID] 
          AND mv.[value] = 'doug'
      ) ;

StackExchange Database Administrators Q#25811, answer score: 9