On SQL Server 2017(RTM-CU17) with query optimizer hot fixes on I have an indexed view that is taking a considerable amount of time to update. I am at a loss and cannot figure out why a full table scan is occurring for the update. 

The indexed view has a sum and is joining two tables on primary key to foreign key with high selectivity (avg 5 foreign rows per primary key). If the primary table row is updated a seek **typically** occurs to the foreign key table for the aggregate data. If the row has a key that is out of bounds of the histogram (higher than the max value of the RANGE\_HI\_KEY) it decides to table scan the secondary table with the foreign key, even if the secondary table has the key value in the statistics. What I get in production is an estimated 110 million rows but only 6 actual rows...pretty far off. Since it is hot data in an ascending key it actually reads all 110 million rows before it finds the 6 it needs.

I found a Microsoft issue that was patched that is very similar to what is happening but does not correct it in this situation: <https://support.microsoft.com/en-us/help/3192154/a-non-optimal-query-plan-choice-causes-poor-performance-when-values>

As I cant share production code I was able to simply recreate it and the plans can be found here, one with an in bound seek, one with an out of bounds scan:
<https://www.brentozar.com/pastetheplan/?id=SknQqFzy8>

Here is the SQL I used to create the plans above...very simple. I am not sure why a scan is happening, any help would be very appreciated!

```
--SQL Server 2017 (TRM-CU17), compatability 140, Query Optimizer Hotfixes on

CREATE TABLE dbo.tblTrans
(id INT IDENTITY(1,1) NOT NULL,
CustID INT NOT NULL,
Flag SMALLINT NOT NULL)
GO

ALTER TABLE [dbo].[tblTrans] ADD CONSTRAINT [PK_tblTrans_ID] PRIMARY KEY CLUSTERED ([ID]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO

--insert random sample of data
INSERT INTO dbo.tblTrans (CustID, Flag)
VALUES (FLOOR(RAND()*1000),9)
GO 10

INSERT INTO dbo.tblTrans (CustID, Flag)
VALUES (FLOOR(RAND()*1000),3)
GO 225

INSERT INTO dbo.tblTrans (CustID, Flag)
VALUES (FLOOR(RAND()*1000),7)
GO 25

INSERT INTO dbo.tblTrans (CustID, Flag)
VALUES (FLOOR(RAND()*1000),4)
GO 185

INSERT INTO dbo.tblTrans (CustID, Flag)
VALUES (FLOOR(RAND()*1000),5)
GO 150

INSERT INTO dbo.tblTrans (CustID, Flag)
VALUES (FLOOR(RAND()*1000),8)
GO 15

INSERT INTO dbo.tblTrans (CustID, Flag)
VALUES (FLOOR(RAND()*1000),2)
GO 110

CREATE TABLE dbo.tblTrans_Detail
(id INT IDENTITY(1,1) NOT NULL,
transid INT NOT NULL,
Amount MONEY NOT NULL)
GO

ALTER TABLE [dbo].[tblTrans_Detail] ADD CONSTRAINT [PK_tblTrans_Detail_ID] PRIMARY KEY CLUSTERED ([ID]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO

--insert random data into detail table
INSERT INTO dbo.tblTrans_Detail (transid, Amount)
SELECT id, CustID+10 AS amount
FROM dbo.tblTrans

INSERT INTO dbo.tblTrans_Detail (transid, Amount)
SELECT id, CustID+12 AS amount
FROM dbo.tblTrans

INSERT INTO dbo.tblTrans_Detail (transid, Amount)
SELECT id, CustID+13 AS amount
FROM dbo.tblTrans

GO

CREATE VIEW [dbo].[ivw_Get_Trans]
WITH SCHEMABINDING
AS
SELECT
dbo.tblTrans.CustID ,
SUM(dbo.tblTrans_Detail.Amount) AS Amount,
COUNT_BIG(*) AS CBCount
FROM dbo.tblTrans
INNER JOIN dbo.tblTrans_Detail
ON tblTrans.id = tblTrans_Detail.TransID
WHERE ( dbo.tblTrans.Flag = 2 )
GROUP BY dbo.tblTrans.CustID
GO

CREATE UNIQUE CLUSTERED INDEX [idx_vw_Trans] ON [dbo].[ivw_Get_Trans] (
[CustID]
) WITH (FILLFACTOR=90, STATISTICS_NORECOMPUTE=OFF) ON [PRIMARY]
GO

--DBCC SHOW_STATISTICS ('dbo.tbltrans',pk_tbltrans_id)
--MAX RANGE_HI_KEY is 720 (also max id from table), key is selective

INSERT INTO dbo.tbltrans (CustID, Flag)
OUTPUT Inserted.id
VALUES (100, 5)
GO

--ID 721 is inserted
INSERT INTO dbo.tbltrans_detail (transid, Amount)
VALUES (721,13.00)
GO

--new ID is in stats of detail table but not stats for trans table
CREATE INDEX IX_tblTrans_Detail_TransID ON dbo.tbltrans_detail (TransID, Amount)
GO

--DBCC SHOW_STATISTICS ('dbo.tbltrans_detail',ix_tbltrans_detail_Transid)

--DBCC FREEPROCCACHE

--set showplan on

BEGIN TRANSACTION

UPDATE dbo.tblTrans
SET Flag = 2
WHERE ID = 720 --seek (highest value in histogram, tblTrans)
--WHERE ID = 721 --scan (out of bounds in histogram, tblTrans)

UPDATE dbo.tblTrans
SET Flag = 2
--WHERE ID = 720 --seek (highest value in histogram, tblTrans)
WHERE ID = 721 --scan (out of bounds in histogram, tblTrans)

ROLLBACK TRANSACTION

--DROP view dbo.ivw_Get_Trans
--drop table dbo.tblTrans
--drop table dbo.tblTrans_Detail

```