13

I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and index.

I want to leverage on the advantage of indexed view, wherein if I've a similar query or the exact same query somewhere else, the query optimizer chooses the indexed view so that performance is improved. I tried using the exact same query that was used to create index (with an additional date filter) but query plan doesn't seem to change. Index on the view is still not used even if I explicitly use the view in the query. I could only get the query optimizer to use the index with noexpand hint. Note that I'm using SQL Server enterprise edition.

Any suggestions?

Improve this question
1
  • 1
    Indeed, I have seen cases where a simple query select col from vwindexedfoo decides it would be better to expand out the view definition rather than use the covering index.
    – Ed Avis
    Commented Aug 18, 2016 at 13:54

1 Answer 1

Reset to default
29

This happens often if the optimiser thinks it can do better with the base tables.

If I create an indexed view, I tend to always use NOEXPAND to make it use it. I also compare the query statistics to ensure that it adds some benefit. That is, if I identify a useful indexed view I want the optimiser to always use it.

Example:

SET STATISTICS IO ON;
SET STATISTICS PROFILE ON;
SET STATISTICS TIME ON;
SELECT * FROM MySchema.myIndexedView ACB WITH (NOEXPAND) WHERE ...;
SELECT * FROM MySchema.myIndexedView ACB WHERE ...;

Note that depending on version, you may suffer from predicate pushing issues.

Note that this answer is generic because we don't have code and queries to look at

Improve this answer

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