Ms sql view not updating

Posted by / 02-Feb-2017 11:00

The query below expresses the same logical requirement, but does not reference the view: This query produces the following execution plan: This plan features one less aggregation operation than before.

When view expansion was used, the query optimizer was unfortunately unable to remove a redundant aggregation operation, resulting in a less efficient execution plan.

The final cardinality estimate for the new query is also slightly better than when the indexed view was referenced without (repeated below for convenience): On an Enterprise Edition instance, the query optimizer may be able to use an indexed view even if the query does not mention the view explicitly.

If the optimizer is able to match part of the query tree to an indexed view, it can choose to do so, based on its estimation of the costs of using the view or not.

Starting with the rarer of the possibilities, there may be occasions where a query references an indexed view, but a better plan would be obtained by accessing the base tables instead.

SQL Server can create statistics automatically to assist with cardinality estimation and cost-based decision-making during query optimization.

The view-matching logic is reasonably clever, but it does have limits that are pretty easy to hit in practice.

Even where view matching is successful, the optimizer can still be misled by inaccurate cost estimations.

Providing the query optimizer with more accurate information using statistics on indexed views can be a useful technique to increase plan quality and robustness.

The query plan shown above (Standard Edition, without ) is actually less optimal than if we had written the query against the base tables ourselves, rather than allowing the query optimizer to expand the view.

ms sql view not updating-28ms sql view not updating-20ms sql view not updating-21

Lower editions of SQL Server require the is specified, automatic statistics are created on indexed views exactly as happens with ordinary tables.

One thought on “ms sql view not updating”

  1. The Conseil d'État decided that a public authority could use gross infringement on human dignity as a motive of public order to cancel a spectacle, and that dwarf-tossing constituted such a gross infringement.