Why is there no predicate applied to the Index Scans?

We're having problems with some queries which are similar to this:

SELECT COUNT('A')  FROM  [dbo].[OINV] T0  
INNER  JOIN [dbo].[OCRD] T2  ON  T2.[CardCode] = T0.[CardCode]   
WHERE T0.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

Query Plan

The indexes that it's hitting are defined as:

NONCLUSTERED INDEX [OCRD_FATHER] ON [dbo].[OCRD]
(
    [FatherCard] ASC
) INCLUDE (CardCode)

NONCLUSTERED INDEX [OINV_CUSTOMER] ON [dbo].[OINV]
(
    [CardCode] ASC
)

They're currently taking 1-2 seconds to run, and returns a count of 0 (which is what we're expecting).

I'm incredibly surprised that it's not filtering the NonClustered Indexes before it feeds into the Hash Match - it's feeding every single row.
These are vendor-software queries, so unfortunately there's no way for us to rewrite them.

Why is this, and is there any way to change it to filter before it does the Hash Match without rewriting the query?

Example Data Setup

CREATE TABLE [dbo].[OCRD]
  (
     [FatherCard] NVARCHAR(50),
     [CardCode]   NVARCHAR(50)
  );

INSERT INTO [dbo].[OCRD]
SELECT TOP (2076000) NEWID(),
                     NEWID()
FROM   master..spt_values v1,
       master..spt_values v2,
       master..spt_values v3

CREATE TABLE [dbo].[OINV]
  (
     [CardCode] NVARCHAR(50)
  )

INSERT INTO [dbo].[OINV]
SELECT TOP (5175460) NEWID()
FROM   master..spt_values v1,
       master..spt_values v2,
       master..spt_values v3

CREATE NONCLUSTERED INDEX [OCRD_FATHER]
  ON [dbo].[OCRD] ( [FatherCard] ASC )
  INCLUDE (CardCode)

CREATE NONCLUSTERED INDEX [OINV_CUSTOMER]
  ON [dbo].[OINV] ( [CardCode] ASC )

is there any way to change it to filter before it does the Hash Match without rewriting the query?

In Enterprise Edition, an indexed view can be used. For example:

CREATE VIEW dbo.OINV_OCRD
WITH SCHEMABINDING
AS
SELECT
    INV.CardCode,
    CRD.FatherCard,
    COUNT_BIG(*) AS cnt
FROM dbo.OINV AS INV
JOIN dbo.OCRD AS CRD
    ON CRD.CardCode = INV.CardCode
GROUP BY
    INV.CardCode,
    CRD.FatherCard;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.OINV_OCRD (CardCode, FatherCard);
CREATE NONCLUSTERED INDEX i ON dbo.OINV_OCRD (FatherCard) INCLUDE (cnt);

Then a query like:

DECLARE 
    @P2 nvarchar(50) = N'D20B5DD1-C729-4B7A-A276-950CE7DCF128',
    @P3 nvarchar(50) = N'6A0DBEAB-FECB-40DB-86C5-AAFA612DA691';

SELECT COUNT_BIG(*)
FROM [dbo].[OINV] T0
JOIN [dbo].[OCRD] T2
    ON T2.[CardCode] = T0.[CardCode]
WHERE 
    T0.[CardCode] = (@P2)  
    OR T2.[FatherCard] = (@P3);

Produces an execution plan using index intersection on the indexed view:

Table 'OINV_OCRD'.
Scan count 2,
logical reads 8, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

All the usual caveats around indexed view usage apply. Automatic indexed view matching is only available in Enterprise Edition (or equivalent). Using COUNT('A') does not affect the core mechanism, though a Compute Scalar is added to convert bigint to integer.

For more details around the indexed view matching feature see my answer to a related question.

If you add tables to the query, the indexed view matching is likely to continue to work (assuming the match is valid) since the optimizer is capable of matching parts of a query to one or more indexed views. This answer can only address the details actually presented in the question.

If the vendor queries follow a set pattern, you could force the indexed view plan to be used via a Plan Guide.

StackExchange Database Administrators Q#193362, answer score: 6