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
After additional research I have discovered this is also occurring on cascade deletes as well. Enabling trace flag 2363 shows "Calculator failed. Replanning." and then coming up with "Selectivity: 1" (everything in child table). In-bounds data will seek, any key value higher than highest histogram key range will scan.
I have submitted this to MS as a bug through the following link: https://feedback.azure.com/forums/908035-sql-server/suggestions/39359128-cascade-deletes-and-indexed-view-updates-causing-f
Update: this was patched in SQL Server 2017 CU22: indexed view update is table scanning 110 million rows for 6 actual rows found out of histogram key bounds