Ms sql view not updating

posted by | Leave a comment

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

ms sql view not updating-50ms sql view not updating-37

Since we are using SQL Server Standard Edition, we must specify the view explicitly and use a The execution plan produced shows a seek on the indexed view to find rows for the products of interest followed by an aggregation to compute the total quantity per customer: The Plan Tree view of SQL Sentry Plan Explorer shows cardinality estimation is exactly correct for the indexed view seek, and very good for the result of the aggregate: As part of the compilation and optimization process for this query, SQL Server created an additional statistics object on the Customer ID column of the indexed view.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 combination of Customer and Product ID is guaranteed to be unique in the view (by definition) so we will use that as the key.We could specify the two columns either way round in the index, but assuming we expect more queries to filter by product, we make Product ID the leading column.

Leave a Reply

Xxx rated webcams