sqlsql-serversql-server-2017clustered-indexindexed-view

Querying a view after creating a clustered index on it still yields the same query plan


I have the following view

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

ALTER VIEW web.vGridHotelBooking
WITH SCHEMABINDING
AS
    SELECT 
        HBK_ID,
        COF_ID,
        COF_CST_ID,
        HTL_Name,
        COF_Data
    FROM 
        web.HotelBooking
    INNER JOIN 
        web.CustomerOfferBundle ON COF_ID = HBK_COF_ID
    INNER JOIN 
        web.Hotel ON COF_HTL_ID = HTL_ID;
GO

CREATE UNIQUE CLUSTERED INDEX [CLI_vGridHotelBooking__HBK_ID] 
ON [web].[vGridHotelBooking] ([HBK_ID]) ON [PRIMARY]
GO

When I execute the statement SELECT * FROM web.vGridHotelBooking I expect to see a single clustered index scan, but instead I get this

enter image description here

This is the same plan I get when executing the SELECT statements directly.

What is that I am doing wrong here? I've used materialized views many times and I did not have that problem before.

EDIT 1

Running the query with a WHERE clause doesn't help either.

SELECT COF_ID
FROM web.vGridHotelBooking
WHERE COF_ID = '06A41DB5-8F14-4E6C-9084-3009E0626DAA';

enter image description here

EDIT 2

SELECT HBK_ID
FROM web.vGridHotelBooking
WHERE HBK_ID = 1801151518187788

enter image description here

EDIT 3

SELECT HBK_ID
FROM web.vGridHotelBooking WITH (INDEX(CLI_vGridHotelBooking__HBK_ID))
WHERE HBK_ID = 1801151518187788;

enter image description here

EDIT 4 Running the query with NOEXPAND yielded the correct plan this time.

SELECT *
FROM web.vGridHotelBooking WITH (NOEXPAND)
WHERE HBK_ID = 1801151518187788;

enter image description here

So the question then is - Why is that? Do I have to worry about this one. Because CustomerOfferBundle table has aprx 500 000 rows in it and the Hotel table aprx 100 000

EDIT 5 enter image description here


Solution

  • As discussed in the comments you can force the use of the indexed view using the WITH (NOEXPAND) hint.

    When you do so it shows that the forced plan is estimated at about 10% of the cost of the original plan so you might expect this to be chosen on cost grounds .

    However the way that compilation works is that the view definition is first expanded out and then may or may not be matched back to the indexed view later in the optimisation process. For cheap plans optimisation may end without it ever getting to that step.

    See Paul White's answer here for more about that. This also mentions

    indexed view matching is not available in optimization phase 0 (transaction processing).

    the transaction processing step is concerned with queries referencing at least 3 tables and nested loops joins so it is entirely possible for you that optimisation just ended there.

    If you increase the size of the tables (especially HotelBooking) and the original plan becomes more expensive more time will be spent on optimisation and the indexed view will probably end up matched.

    You can always use the hint to be sure.