December 11, 2019 at 8:27 pm
Hello,
On SQL Server 2017 I have an indexed view with aggregation referencing two tables (inner join). When a table column referenced by the indexed view is updated the index view is updated utilizing an nested loop (inner join) operator and an index seek between the two tables. This works fine but when the seek value is outside the bounds of the histogram it does a full scan/Merge Join. The value is highly selective and the estimated number of rows is 1 throughout the entire plan except for the index scan/Merge Join which estimates it will read the full index.
Has anyone seen issues like this before? I can update stats on the index but newly inserted rows (ascending ID) are immediately out of bounds and will cause full index scans if updated.
Here are the plans of the recreated scenario but at a smaller scale...the prod scenario scans over 100 million rows: https://www.brentozar.com/pastetheplan/?id=S18AA6ATB
Any thoughts or ideas? It is hard to optimize as it is an indexed view that automatically updates when the base table is updated.
December 12, 2019 at 2:05 pm
Hey Ben. This isn't a direct answer to your question, but I think it's best to respond after understanding the context of a problem. Can you not get adequate performance without materializing the view? Have you considered columnstore indexes? Do you need real-time data (from the queries hitting the materialized view)?
December 12, 2019 at 3:33 pm
It is an OLTP environment. We have considered column-store indexes but based frequent base table updates decided against it. The data does need to be real-time. We are not able to get the performance we need without the indexed view. The example plans are simplified and scaled down considerably. I am wondering why the plan would chose to scan instead of seek only for values outside of the histogram bounds. If I rebuild stats with 100 percent it will always seek but as soon as a new row is inserted with an ID higher than the highest histogram range value it scans. If I run the base table update with (optimize for unknown) it will seek out of bound IDs which is also strange. Plus, the merge join says many to many is true even though the calling operator only estimates 1 row.
December 12, 2019 at 4:11 pm
I'm not sure that frequent updates are a reason to rule out columnstore indexes. I'll have to check that out.
Are you able to share your table DDL and query?
December 12, 2019 at 5:08 pm
Here is what I used to create the plan scenario, I am unable to share what is running in production but it is more complex with much more data. I guess I am just trying to figure out why as soon as I update a newly inserted row it switches to an index scan. On a small scale like this it isn't a big deal but in production when it scans over 100 million rows it becomes a problem. If I change compatibility level to SQL2014 it will seek out of bound ranges.
CREATE TABLE dbo.tblTrans
(id INT IDENTITY(1,1) NOT NULL,
CustID INT NOT NULL,
Flag SMALLINT NOT NULL)
ALTER TABLE [dbo].[tblTrans] ADD CONSTRAINT [PK_tblTrans_ID] PRIMARY KEY CLUSTERED ([ID]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
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 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
CREATE INDEX IX_tblTrans_Detail_TransID ON dbo.tbltrans_detail (TransID, Amount)
GO
DBCC SHOW_STATISTICS ('dbo.tbltrans',pk_tbltrans_id)
--MAX RANGE_HI_KEY is 720 (also max id from table)
DBCC SHOW_STATISTICS ('dbo.tbltrans_detail',ix_tbltrans_detail_Transid)
--index is selective
SELECT 1/0.001388889 --720 unique key values in table, 2160 rows (avg 3 rows per transid)
SELECT 1/0.000462963 --2160 unique values in table, 2160 rows (avg 1 rows per transid, Amount)
INSERT INTO dbo.tbltrans (CustID, Flag)
OUTPUT Inserted.id
VALUES (100, 5)
--ID 721 is inserted
INSERT INTO dbo.tbltrans_detail (transid, Amount)
VALUES (721,13.00)
--transid 721 is inserted into detail table
--DBCC FREEPROCCACHE
--set showplan on
BEGIN TRANSACTION
UPDATE dbo.tblTrans
SET Flag = 2
WHERE ID = 720 --seek (highest value in histogram)
--WHERE ID = 721 --scan (out of bounds in histogram)
ROLLBACK TRANSACTION
--DROP view dbo.ivw_Get_Trans
--drop table dbo.tblTrans
--drop table dbo.tblTrans_Detail
December 13, 2019 at 1:10 pm
Thanks Ben. I'm looking at this now. For sure I can say you have put together one of the most detailed and cohesive forum posts I have seen here on SQL Server Central. Good job on that!
December 31, 2019 at 2:53 pm
Yes, I know if I turn Legacy Cardinality Estimation on it works - unfortunately since its on SQL 2014 compatibility it would possibly introduce regressions. I found what appears to be the same issue reported as a bug:
January 3, 2020 at 3:22 pm
After more research I found it is also affecting 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 submitted this to MS as bug: https://feedback.azure.com/forums/908035-sql-server/suggestions/39359128-cascade-deletes-and-indexed-view-updates-causing-f
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply