Why Query Optimizer totally ignores indexed view indexes?

Question

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 ?

Answer 1

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)