为什么查询优化器完全忽略索引视图索引? [英] Why Query Optimizer totally ignores indexed view indexes?

问题描述

SQL Fiddle:

虽然 8 的因数可能听起来很多，但您的示例数据非常小，直接从基表中选择的成本是 0.0267122 与 0.003293 的估计成本从观点来看.

Paul White 在他在这里的回答中解释说，如果低首先找到足够的计划.

人为地提高所有涉及的桌子的成本

UPDATE STATISTICS Classificator1 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000UPDATE STATISTICS Classificator2 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000UPDATE STATISTICS Classificator3 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000UPDATE STATISTICS Classificator4 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000UPDATE STATISTICS Classificator5 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000UPDATE STATISTICS Entity WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000

将基表计划的成本增加到 29122.6

除非您另有明确提示，否则您现在应该看到匹配的视图(在企业版/开发者版/评估版上).

SELECT * FROM EntityView;SELECT * FROM EntityView 选项(扩展视图)

SQL Fiddle: http://sqlfiddle.com/#!6/d4496/1 (data is pre-generated for your experiments)

There is obvious table:

CREATE TABLE Entity 
(
  ID int,
  Classificator1ID int,
  Classificator2ID int,
  Classificator3ID int,
  Classificator4ID int,
  Classificator5ID int
);

and the view :

CREATE VIEW dbo.EntityView (ID, Code1, Code2, Code3, Code4, Code5) 
WITH SCHEMABINDING

where entities fields Classificator1ID..Classificator5ID resolved to classificators values Code1..Code5

and there are a lot of indexes on this view:

CREATE UNIQUE CLUSTERED INDEX [IXUC_EntityView$ID] ON EntityView
  ([ID]);
CREATE UNIQUE NONCLUSTERED  INDEX [IXU_EntityView$ID$include$ALL] ON EntityView
  ([ID]) INCLUDE (Code1, Code2, Code3, Code4,  Code5);
CREATE UNIQUE NONCLUSTERED  INDEX [IXU_EntityView$ALL] ON EntityView
  ([ID],Code1, Code2, Code3, Code4,  Code5);  
CREATE UNIQUE NONCLUSTERED  INDEX [IXU_EntityView$ID$Code1] ON EntityView
  ([ID],Code1);
CREATE UNIQUE NONCLUSTERED  INDEX [IXU_EntityView$ID$include$Code1] ON EntityView
  ([ID])INCLUDE (Code1);
CREATE NONCLUSTERED  INDEX [IX_EntityView$Code1] ON EntityView
  (Code1);
CREATE NONCLUSTERED  INDEX [IX_EntityView$Code1$include$ID] ON EntityView
  (Code1) INCLUDE (ID);

But QO never use them! Try this:

SELECT * FROM EntityView;

SELECT ID, Code1 FROM EntityView;

SELECT ID, Code1, Code2, Code3, Code4, Code5 FROM EntityView;

SELECT ID, Code1, Code2, Code3, Code4, Code5 FROM EntityView WHERE ID=1;

SELECT ID, Code1 FROM EntityView Where Code1 like 'NR%';

Why? And especially What is wrong with "include" indexes? index created , has all fields and still unused...

ADDED: THIS IS JUST TEST! Please do not be so angry and do not push me to analyze those indexes maitinence problems.

In my real project I can't explain why QO ignores indexed views (very-very usefull indexed views). But sometimes I see it utilize them in other places. I have created this db snippet to experiment with index formulas but may be I should do something more: tune statistcs somehow ?

解决方案

Running on 2012 Developer Edition the unhinted query is costed at approx 8 times more than the hinted query

Whilst a factor of 8 might sound a lot your example data is pretty small and the cost for selecting directly from the base tables is 0.0267122 vs 0.003293 for the estimated cost from the view.

Paul White explains in his answer here that automatic indexed view matching won't even be considered if a low enough plan is found first.

Artificially bumping up the costs for all the tables involved

UPDATE STATISTICS Classificator1 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 
UPDATE STATISTICS Classificator2 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 
UPDATE STATISTICS Classificator3 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 
UPDATE STATISTICS Classificator4 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 
UPDATE STATISTICS Classificator5 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 
UPDATE STATISTICS Entity         WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 

Increases the cost of the base table plan to 29122.6

You should now see the view being matched (on Enterprise/Developer/Evaluation editions) unless you explicitly hint otherwise.

SELECT * FROM EntityView;

SELECT * FROM EntityView OPTION (EXPAND VIEWS) 

这篇关于为什么查询优化器完全忽略索引视图索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！