SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view’s index

Improper use of indexed views in non-Enterprise or non-Developer editions of SQL Server can result in inefficient query performance.

Description

In SQL Server, an indexed view can optimize query performance by storing precomputed results as an index. However, in editions other than Enterprise or Developer, the query optimizer will not automatically consider these indexed views unless explicitly instructed using the WITH(NOEXPAND) hint.

For example:

SQL
Copy
1-- Example of a problematic query in SQL Server Standard edition
2SELECT * FROM MyIndexedView;
3-- Without using WITH(NOEXPAND)

This query might not leverage the performance benefits of an indexed view because the optimizer in non-Enterprise editions overlooks the index unless WITH(NOEXPAND) is specified. As a result:

  • Query performance may degrade because the optimizer processes the view as if it were a regular table, re-evaluating the computations unnecessarily.

  • It negates the benefits of having an indexed view, particularly in high read scenarios where performance gains are critical.

How to fix

This section provides guidance on optimizing the performance of queries using indexed views in SQL Server editions that are not Enterprise or Developer by employing the WITH(NOEXPAND) hint.

Follow these steps to address the issue:

  1. Identify queries that reference indexed views in SQL Server Standard or Datacenter editions. These queries may not automatically benefit from the indexed view without further action.

  2. Modify the query to include the WITH(NOEXPAND) hint. This ensures the query optimizer uses the index created for the view, enhancing performance.

  3. Review and test the query to confirm performance improvements, especially in scenarios with high read operations, where the indexed view should offer significant benefits.

For example:

SQL
Copy
1-- Example of a corrected query in SQL Server Standard edition
2SELECT * FROM MyIndexedView WITH (NOEXPAND);

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
SuppressInEditions

Parameter specifies a comma separated list of SQL Sever editions, for which the rule not to be applied.

Enterprise
Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix
3 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1SELECT  *
 2FROM  Person.vStateProvinceCountryRegion
 3
 4SELECT  *
 5FROM  Production.vProductAndDescription  WITH ( NOEXPAND )
 6
 7SELECT  *
 8FROM  Person.vStateProvinceCountryRegion AS e WITH ( NOEXPAND )
 9
10SELECT  *
11FROM  Production.vProductAndDescription
Analysis Results
  Message Line Column
1 SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view’s index. 2 13
2 SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view’s index. 11 17
See Also

Other Resources

Analysis Rules