Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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?

share|improve this question

migrated from stackoverflow.com Mar 14 '13 at 11:05

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Martin Smith, Marian, AlexKuznetsov, Paul White, Jon Seigel Mar 14 '13 at 17:54

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer

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