Strange query plan on max(date) query on a View

Question

I have a view which comprises 4 yearly tables:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BGT_BETWAYDETAILS]
WITH SCHEMABINDING
AS
SELECT [bwd_BetTicketNr] ,
    [bwd_LineID] [int] ,
    [bwd_ResultID] [bigint] NOT NULL,
    [bwd_DateModified]  ,
    [bwd_DateModifiedTrunc]  ,
    [bwd_LineMaxPayout] 
FROM   [dbo].[BGT_BETWAYDETAILS_2020]
UNION ALL
SELECT [bwd_BetTicketNr] ,
    [bwd_LineID] [int] ,
    [bwd_DateModified]  ,
    [bwd_DateModifiedTrunc]  ,
    [bwd_LineMaxPayout] 
FROM   [dbo].[BGT_BETWAYDETAILS_2019]
UNION ALL
SELECT [bwd_BetTicketNr] ,
    [bwd_LineID] [int] ,
    [bwd_DateModified]  ,
    [bwd_DateModifiedTrunc]  ,
    [bwd_LineMaxPayout] 
FROM   [dbo].[BGT_BETWAYDETAILS_2018]
UNION ALL
SELECT [bwd_BetTicketNr] ,
    [bwd_LineID] [int] ,
    [bwd_DateModified]  ,
    [bwd_DateModifiedTrunc]  ,
    [bwd_LineMaxPayout] 
FROM   [dbo].[BGT_BETWAYDETAILS_2017];
GO

Each table has the following structure:

CREATE TABLE [dbo].[BGT_BETWAYDETAILS_2020]
(
    [bwd_BetTicketNr] [bigint] NOT NULL,
    [bwd_LineID] [int] NOT NULL,
    [bwd_ResultID] [bigint] NOT NULL,
    [bwd_DateModified] [datetime] NULL,
    [bwd_DateModifiedTrunc] [date] NULL,
    [bwd_LineMaxPayout] [decimal](18, 4) NULL,

    CONSTRAINT [CSTR__BGT_BETWAYDETAILS_2020_CKEY] 
        PRIMARY KEY CLUSTERED ([bwd_BetTicketNr] ASC, [bwd_LineID] ASC, [bwd_ResultID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I have added an non-clustered index on

CREATE NONCLUSTERED INDEX [NCI__DATEMODIFIED] 
ON [dbo].[BGT_BETWAYDETAILS_2020] ([bwd_DateModifiedTrunc] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
                          ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I am running the following 3 queries:

SELECT COALESCE(MAX([bwd_DateModifiedTrunc]), '2019-01-01') AS next_date
FROM [dbo].[BGT_BETWAYDETAILS_2020]

SELECT COALESCE(MAX([bwd_DateModifiedTrunc]), '2019-01-01') AS next_date
FROM [dbo].[BGT_BETWAYDETAILS]

SELECT COALESCE(CAST(MAX([bwd_DateModified]) AS date), '2019-01-01') AS next_date
FROM [dbo].[BGT_BETWAYDETAILS]

The first one, when run on each yearly table, runs instantly.

The second one, seems to take forever. The query plan for this, seems very strange.

The plan shows two index scans on each yearly table.

The plan for each yearly table is what I expected to see:

Finally, the plan on the non-indexed date column is also what I expected to see (a clustered index scan). A clustered index scan on each table. This query runs in ~3mins which is expected.

What is the issue here? Some anti-pattern I am missing? Why the index scan on the non-clustered index is done 2 times according to the live plan? I expected the view to respond as fast as the individual tables.

For the record, I am running this on SQL Server 2017.

user_73226 · Answer

This just looks like an optimiser limitation. I have submitted a suggestion that this should be improved.

A simpler example is

CREATE TABLE T1(X INT NULL UNIQUE CLUSTERED);
CREATE TABLE T2(X INT NULL UNIQUE CLUSTERED);

INSERT INTO T1 
OUTPUT INSERTED.X INTO T2
SELECT TOP 100000 NULLIF(ROW_NUMBER() OVER (ORDER BY 1/0),1) 
FROM sys.all_objects o1, 
     sys.all_objects o2;

And then

WITH CTE AS
(
SELECT X FROM T1
UNION ALL
SELECT X FROM T2
)
SELECT MAX(X)
FROM CTE
OPTION (QUERYRULEOFF ScalarGbAggToTop)

This disables the query optimizer rule ScalarGbAggToTop and the query plan does a MAX on each individual table then computes a MAX of the MAX-es - so the same as

SELECT MAX(MaxX)
FROM 
(
SELECT MAX(X) AS MaxX FROM T1 
UNION ALL
SELECT MAX(X) AS MaxX FROM T1 
) T

With the ScalarGbAggToTop rule enabled the plan now looks like this

It is effectively doing the following...

SELECT MAX(MaxX)
FROM   (SELECT MAX(X) AS MaxX
        FROM   (SELECT TOP 1 X
                FROM   T1
                WHERE  X IS NULL
                UNION ALL
                SELECT TOP 1 X
                FROM   T1
                WHERE  X IS NOT NULL
                ORDER  BY X DESC) T1
        UNION ALL
        SELECT MAX(X) AS MaxX
        FROM   (SELECT TOP 1 X
                FROM   T2
                WHERE  X IS NULL
                UNION ALL
                SELECT TOP 1 X
                FROM   T2
                WHERE  X IS NOT NULL
                ORDER  BY X DESC) T2) T0

... but in a very inefficient way. Running the SQL above would give a plan with seeks and each branch only reading a single row.

The plan produced by ScalarGbAggToTop only has minimal changes to the stream aggregate plan. It looks like it takes the scan from that and applies a backwards ordering to it and then uses the backwards ordering for both the NOT NULL and NULL branches. And does not perform any additional exploration to see if there is a more efficient access path.

This means that in the pathological case that all of the rows are either NULL or NOT NULL one of the scans will end up reading all of the rows in the table (5 billion in your case if applicable to all 4 tables). Even if there is a mix of NULL and NOT NULL the fact that the IS NULL branch is doing a backwards scan is sub optimal because NULL is ordered first in SQL Server so would be at the beginning of the index.

The addition of a NOT NULL branch in the first place seems largely unnecessary as the query would return the same results without it. I imagine it is only needed so that it knows whether or not to display the message

Warning: Null value is eliminated by an aggregate or other SET operation.

but I doubt you care about that. In which case adding an explicit WHERE ... NOT NULL resolves the issue.

WITH CTE AS
(
SELECT X FROM T1
UNION ALL
SELECT X FROM T2
)
SELECT MAX(X)
FROM CTE
WHERE X IS NOT NULL
;

It now has a seek into the NOT NULL part of the index and reads backwards (stopping after the first row is read from each table)

Strange query plan on max(date) query on a View

1 Answers