0

I have a largish table (>126 million rows), and a need to optimize a specialized set of queries, so I created an Indexed view on the table, and coded a procedure that would select from the Indexed View when the conditions were favorable for it.

Execution of the procedure took longer than I expected, so I looked at the execution plan, and I see that the query that takes the longest is the one that selects from my view, and when I hover over the element that takes the longest it's a Clustered Index Seek of the CI of the underlying table. Not the Index on the view.

I understood that an Indexed View was a sort of virtual table that was stored on disk, and that when queried, did not pass the query through to the underlying table at all. Is this not the case?

1
2

The optimizer might choose to use the underlying table (it "expands" the view) based on how it chooses to implement your query. To force the query to use the indexed view, you could add a WITH (NOEXPAND) hint to the FROM clause of the query where the view is referenced.

This is useful for two reasons:

  1. It gets the query to use the view you want
  2. It might show you why the optimizer chose not to use the view (maybe the index definition needs to be tweaked to get better performance, etc)

By the way, it's recommended to use this hint regardless when referencing indexed views directly, in order to avoid other problematic behavior. See this article from Paul White for details: Indexed Views and Statistics

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.