From the past experience using the schema bound and indexed view in a query with (noexpand) hint usually dramatically improves performance if the view is designed well.

Apparently using the indexed views is not implicit, as it requires a table hint.

But can there be any scenarios where supplying (noexpand) hint may be undesirable while the view remains schema bound and indexed?

In other words, should schema bound indexed views be "not expanded" implicitly at all times instead of a developer having to remember to supply the hint to turn off view expansion?

  • 1
    Depends on how up to date your SQL server is. I know this probably isn't what you mean but it's the only negative I could think of. – Zane Mar 9 '16 at 20:17
  • 2013, but still an interesting read. I must have totally missed that incident years ago. This is actually a good candidate for an answer. – ajeh Mar 9 '16 at 22:08

Firstly, NOEXPAND is an enterprise edition feature of SQL Server, that is why you have to specify it manually if you are not on this edition.

Based on the optimizer's prediction there may be a situation, where NOEXPAND would be harmful. But it's pretty rare and most likely NOEXPAND isn't the issue in such cases. Most likely the issue is poorly chosen indexes. For instance, an index on a column with low cardinality.

Additionally, have a look at this noexpand hint for non-enterpise edition and performance post as it answers the rest of your questions. And also provides you with a workaround.

| improve this answer | |
  • Correct me if I am wrong, but the way I read BOL it states that Enterprise is supposed to imply (noexpand). But I know for certain that it does not, as I am on Enterprise and adding (noexpand) provides dramatic performance improvement, it never failed to. So it is not correct to state that noexpand is an Enterprise feature. – ajeh Mar 9 '16 at 22:07
  • msdn.microsoft.com/en-us/library/ms187373.aspx I would guess based on this article that the following condition is not sufficed in your situation: If a query contains references to columns that are present both in an indexed view and base tables, and the query optimizer determines that using the indexed view provides the best method for executing the query, the query optimizer uses the index on the view. – Vladimir S. Mar 11 '16 at 8:19
  • If you want to dig deeper, you have to find out why optimizer does not choose to use your indexes. Are all the estimations in your actual execution plan are realistic? Could be missing\outdated statistics on a column is the issue. This looks like a good article to start with (read the first user comment in there, this sounds like your case) sqlperformance.com/2014/01/sql-plan/… – Vladimir S. Mar 11 '16 at 8:35

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.