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

Question

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

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';

EDIT 2

SELECT HBK_ID
FROM web.vGridHotelBooking
WHERE HBK_ID = 1801151518187788

EDIT 3

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

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

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

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

Why would you expect the query to be faster or the plan to differ? It's a `SELECT *` without a `WHERE`, the speed of the query will be as fast as a scan on the underlying tables. An `INDEX` isn't going to help with a query that effectively says "Return me all the data, regardless of order". — Thom A, Feb 18 '19 at 21:19
That's a so called materialized view, it should behave as a new physical table if I understand that correctly and it should avoid all the joins (nested loops) — Mihail Shishkov, Feb 18 '19 at 21:21
It's a clustered index, it should have every column that is in the select list of the view definition in it. — Mihail Shishkov, Feb 18 '19 at 21:25
I think you might be confused about how indexes work. Your index contains only a single column. But your where clause is a different column. As such your index is not a covering index for the query. — Sean Lange, Feb 18 '19 at 21:27
the Query optimizer will choose the best execution plan for your query. You can [explicitly force using an index](https://blog.sqlauthority.com/2009/02/07/sql-server-introduction-to-force-index-query-hints-index-hint/) but i think it is not recommended at all — Hadi, Feb 18 '19 at 21:27
Selecting only HBK_ID yields the same plan. I don't know how smart is query optimizer but it seems pretty strange for me not to select directly on the index. — Mihail Shishkov, Feb 18 '19 at 21:29
@MihailShishkov the query optimizer found that scanning a single index has higher cost than scanning three different indexes in parallel. Why you find it strange?? — Hadi, Feb 18 '19 at 21:31
What edition of SQL Server? Automatic indexed view matching requires Enterprise. Even then it won't happen if it finds a cheap alternative before getting to it. You can use the NO EXPAND hint to force it — Martin Smith, Feb 18 '19 at 21:32
The index is clustered and does cover all columns exposed in the SELECT list of the view despite all the incorrect comments above — Martin Smith, Feb 18 '19 at 21:35
@MartinSmith I was starting to question my understanding on clustered indexes. Thanks man. — Mihail Shishkov, Feb 18 '19 at 21:36
Forcing the query to use the index still yields the same plan. — Mihail Shishkov, Feb 18 '19 at 21:37
Did you definitely use "WITH (NOEXPAND)"? You didn't just use NOEXPAND so it just became an alias? — Martin Smith, Feb 18 '19 at 21:40
@MartinSmith I used WITH (INDEX(CLI_vGridHotelBooking__HBK_ID)) see my edit 3. — Mihail Shishkov, Feb 18 '19 at 21:43
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 — Mihail Shishkov, Feb 18 '19 at 21:48
what is the estimated cost for the whole plan in the initial query? And in Edit 4 where it uses the view? — Martin Smith, Feb 18 '19 at 21:54
It may well be that it found a good enough plan in optimization phase 0 (transaction processing) so never got to it. https://dba.stackexchange.com/questions/26979/what-factors-go-into-an-indexed-views-clustered-index-being-selected/27039#27039. A three table nested loops join could well be from TP step — Martin Smith, Feb 18 '19 at 22:02
What do you mean by "where it uses the view'? The vGridHotelBooking is the view. The estimated cost for the whole plan is 100% in initial query. — Mihail Shishkov, Feb 18 '19 at 22:03
select the root node in both plans and look at estimated subtree cost in the properties window to get the estimated costs for the whole plans. — Martin Smith, Feb 18 '19 at 22:06
Got it. Estimated subtree cost using WITH (NOEXPAND) is 0.345108. On plain select with no hint is 0.354152 — Mihail Shishkov, Feb 18 '19 at 22:09
Sorry I've misstyped this one. WITH(NOEXAPND) is 0.03... opposing of 0.35. See edit 5 — Mihail Shishkov, Feb 18 '19 at 22:15
Ah OK - my guess is that it just bailed out optimizing at phase 0 still then so never even got onto considering indexed views. See the [dba.se] link above — Martin Smith, Feb 18 '19 at 22:16
So basically no worries here? Will it eventually switch to seeking the index? — Mihail Shishkov, Feb 18 '19 at 22:21
If the plan without the indexed view matching becomes significantly more expensive according to the optimiser cost model (e.g. because the booking table has more than 107 rows) it should spend more time on optimisation and get to it. You can use the hint to force it without waiting for that. — Martin Smith, Feb 18 '19 at 22:24

score 1 · Accepted Answer · answered Feb 18 '19 at 22:34

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.

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

1 Answers1