1

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?

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
ajeh
  • 911
  • 5
  • 14
  • 31

2 Answers2

3

How view expansion and matching work

The documentation for the NOEXPAND table hint is confusingly worded.

The fact of the matter is that references to indexed views are always expanded into the view definition early on in the query compilation process, unless this hint is provided.

On some editions (Enterprise, Developer, Evaluation, Azure SQL Database) the optimizer may match the expanded definition, or any other part of a potential plan tree, to one or more indexed views later in the process.

This might sound counter-productive and redundant, but it has important benefits. The expanded form may admit simplifications or other optimization opportunities that are simply not possible or evident with the indexed view reference itself. In addition, the ability to match arbitrary parts of plan alternatives to indexed views can be a very powerful feature.

When this works well, the database administrator can provide a selection of indexed views that benefit a wide range of queries, without the query writer needing to specify those views explicitly. Moreover, the optimizer can explore plan alternatives (e.g. introducing or splitting aggregates) that match one or more indexed views, where the original query would not.

When to use NOEXPAND

You can use the NOEXPAND hint in any edition. You should use it when you are certain that accessing the indexed view is the best possible option. Be aware that you are taking choices away from the query optimizer by doing this.

You must use NOEXPAND in any edition not mentioned in the prior section to access the indexed view directly (else it will be expanded).

You should also use NOEXPAND — even in Enterprise-equivalent or Azure SQL Database — if you want SQL Server to automatically create statistics on the indexed view for better cardinality estimates. See my article for more details.

When not to use NOEXPAND

You might choose not to use this hint when:

  • You create & maintain indexed view statistics manually; and
  • You trust SQL Server to decide whether to use the indexed view or not, based on its cost estimates.

This option only applies to Enterprise-equivalent and Azure SQL Database, because only those versions are capable of matching plan sections to indexed views for you.

Advice

In most cases, people write queries with explicit indexed view references because those objects are designed to provide optimal performance. It makes sense to use NOEXPAND in those cases for reliability. You wouldn't want SQL Server to mistakenly expand the view just because a cardinality mis-estimate makes that option seem cheaper, when it is not.

So saying, there can be times when allowing SQL Server to expand the view at its discretion provides a better outcome. This typically occurs when the base tables provide indexes the indexed view does not, or other table-level constraints and query information means the expanded query can be greatly simplified.

There is an inherent bias in the question when it says, "if the view is designed well". This might be taken to mean that the indexed view is necessarily optimal for all designed cases. People create all sorts of indexed views, not all of which are so clear-cut winners. They can still be useful in some cases — perhaps to avoid several joins — without being optimal for all cases.

Paul White
  • 94,921
  • 30
  • 437
  • 687
0

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.

Community
  • 1
Vladimir S.
  • 505
  • 5
  • 13