Is there benefit to index base tables of an indexed view?

Question

After I created the indexed view, I tried disabling all the indexes in base tables including the indexes for foreign key column (constraint is still there) and the query plan for the view stays the same.

It is just like magic to me that the indexed view would be able to optimize the query so much even without base table being indexed. Even without any index on the View, SQL Server is able to do an index scan on the primary key index of the indexed view to retrieve data like 1000 times faster than using the base table.

Something like SELECT * FROM MyView WITH(NOEXPAND) WHERE NotIndexedColumn = 5 ORDER BY NotIndexedColumn

So the first two questions are:

1. Is there any benefit to index base tables of indexed view?
2. What is Sql server doing when it is doing a index scan on the PK while the constraint is on a not indexed column?

Then I noticed that if I use full-text search + order by I would see a table spool (eager spool) in the query plan with a cost like 95%.

Query looks like SELECT ID FROM View WITH(NOEXPAND) WHERE CONTAINS(IndexedColumn, '"SomeText*"') ORDER BY IndexedColumn

Question n° 3:

3. Is there any index I could add to get rid of that operation?

Answer 1

It's important to understand that an indexed view is a "materialized view" and the results are stored onto disk.

So the speedup you are seeing is the actual result of the query you are seeing stored to disk.

To answer your questions:

1) Is there any benefit to index base tables of indexed view?

This is situational. If your view is flattening out data or having many extra aggregate columns, then an indexed view is better than the table. If you are just using your indexed view like such SELECT * FROM foo WHERE createdDate > getDate() then probably not.

But if you are doing SELECT sum(price),min(id) FROM x GROUP BY id,price then the indexed view would probably be better. Granted, you are doing a more complex query with joins and other advanced options.

2) What is Sql server doing when it is doing a index scan on the PK while the constraint is on a not indexed column?

First we need to understand how clustered indexes are stored. The index is stored in a B-tree. So SQL Server is walking the tree finding all values that match your criteria when you are searching on a clustered index Depending on how you have your indexes set up i.e covering vs non covering and how your non-clustered indexes are set up will determine what the Pages and Extents look like. Without more knowledge of the table structure I can't help you understand what the scan is actually doing.

3)Is there any index I could add to get rid of that operation?

Just because something is taking 95% of the query's time doesn't make that a bad thing. The query time needs to add up to 100%, so no matter what you do there is always going to be something taking up a large percentage of time. What you need to check is the IO reads and how much time the query itself takes.

To determine this, you need to understand that SQL Server caches the results of queries. With this in mind, you can have a query take a long time the first time but afterward since the data itself is cached it would be much quicker. It all depends on the frequency of the query and how your system is set up.

For a more in-depth read on indexed view