Categories
Mastering Development

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

I have a view which comprises 4 yearly tables:

USE [BGT]
GO

/****** Object:  View [dbo].[BGT_BETWAYDETAILS]    Script Date: 22/5/2020 5:15:40 μμ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BGT_BETWAYDETAILS]
WITH SCHEMABINDING
AS
SELECT *
FROM   [dbo].[BGT_BETWAYDETAILS_2020]
UNION ALL
SELECT *
FROM   [dbo].[BGT_BETWAYDETAILS_2019]
UNION ALL
SELECT *
FROM   [dbo].[BGT_BETWAYDETAILS_2018]
UNION ALL
SELECT *
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_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]
GO

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.

Leave a Reply

Your email address will not be published. Required fields are marked *