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 ?

Norbert

Do you have Standard edition SQL Server? – Pumpernickel

Im using enterprise version... – Norbert

SQL Fiddle isn't. If you specify WITH (NOEXPAND) it uses the index just fine. – Milburn

Well, SELECT * FROM EntityView will need to get all the data (all the columns) so doing a table/clustered index scan is the typically much faster way than doing a huge number of expensive key lookups ...... – Murchison

@Murchison : there is such index with all fields, SELECT * FROM EntityView with (noexpand) - perfectly works; – Norbert

Such an index (which really just duplicates the table) really doesn't make a lot of sense in the first place ... you're basically just wasting (1) disk space by duplicating the data, and (2) processing time by having to maintain those ...$ALL indices whenever the base tables are updated .... – Murchison

There's so so so much redundancy on those indexes...hopefully this is just a test to understand how indexed views work – Sternum

@Murchison : but this does not explain why QO ignore them... – Norbert

You need to really that there is a cost associated with every index you make. Then there is a balancing act between performance gains by queries that run use those indexes as well as the time it takes to maintain and update those indexes. Just because you can make an index doesn't mean you should. – Execution

@Sternum : yes this just test – Norbert

See Paul White's answer to What factors go into an Indexed View's Clustered Index being selected? – Sporocyst

@Milburn : all sql server 2012 indexed view documentation that I just checked do not mention difference between Enterprise and other release types? Only SQL SERVER 2008. Is it still actual for 2012? – Norbert

Features Supported by the Editions of SQL Server 2012. See Automatic use of indexed view by query optimizer (as compared to Direct use which specifies the need for WITH (NOEXPAND)) – Milburn

@Milburn : thank you very much! but how therefore sqlfiddle can process hint noexpand if it uses "Microsoft Corporation Express Edition" which is not marked in this document as supporting "Direct query of indexed views (using NOEXPAND hint)" ? I can't understand this... – Norbert

NOEXPAND works in all editions of SQL server including Express: sqlservercentral.com/articles/editions/88074 – Freakish

Without NOEXPAND, the query optimizer has no idea that you are simply selecting from a view. It sees the expanded query, a five-way join with a bunch of casts. See my answer for more information. – Topcoat

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) 

Sporocyst

Thank you! Could I ask one more question: Where values of ROWCOUNT and PAGECOUNT are stored and how can I get values of them? dbcc show_statistics require statics name so I can't understand which name to use.. and dbcc show_statistics ( 't.Entity','PK_Entity') do not return such numbers... – Norbert

Another question: what kind of object is "the base table plan" and where it is stored? – Norbert

@RomanPokrovskij - It updates the columns exposed to us from SELECT data_pages,rows FROM sys.allocation_units au JOIN sys.partitions p on au.container_id = p.hobt_id WHERE p.object_id=object_id('Classificator1') – Sporocyst

By base table plan I just meant "the execution plan that uses the base tables" as opposed to "the execution plan that uses the view". – Sporocyst

Note, that "bumping" doesn't help with query SELECT Code1 FROM t.EntityView Where Code1 like 'NR1%' ... It could tell something interesting about how cost is calculated for queries with filters. – Norbert

Martin, how you get the plan cost ? the top select estimataed subtree cost of the plan? – Norbert

@RomanPokrovskij - This is just a quick and dirty way of increasing the cost for some operators. It is not the same as actually loading up that amount of testing data and may well behave differently. Just allowed me to illustrate Paul White's point about cost based in an easy way. And yes look at the root of the plan. You should probably just make all tables involved bigger (off course the number of fake rows in this answer would be complete overkill. Maybe try doubling the size of them all and so on) – Sporocyst

@RomanPokrovskij - Though I see Steve's point as well that this type of construct is making things very difficult for the optimiser. So maybe you will find queries that never seem to get matched. – Sporocyst

Answer 2

tl;dr answer: If you don't specify NOEXPAND, the query optimizer has no idea you are submitting a simple select from a view. It would have to match the expansion of your query (which is all it sees) with some view index. Probably won't bother when it's a five-way join with a bunch of casts.

View index matching to a query is a hard problem, and I believe your view is too complicated for the query engine to match to an index. Consider this one of your queries:

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

It's obvious to you that this can use a view index, but this is not the query the query engine sees. Views are automatically expanded if you don't specify NOEXPAND, so this is what goes to the query engine:

SELECT ID, Code1 FROM (
    SELECT e.ID, 'NR'+CAST(c1.CODE as nvarchar(11)) as Code1, 'NR'+CAST(c2.CODE as nvarchar(11)) as Code2, 'NR'+CAST(c3.CODE as nvarchar(11)) as Code3, 'NR'+CAST(c4.CODE as nvarchar(11)) as Code4, 'NR'+CAST(c5.CODE as nvarchar(11)) as Code5
    FROM dbo.Entity e
        inner join  dbo.Classificator1 c1 on e.ID = c1.ID
        inner join  dbo.Classificator2 c2 on e.ID = c2.ID
        inner join  dbo.Classificator3 c3 on e.ID = c3.ID
        inner join  dbo.Classificator4 c4 on e.ID = c4.ID
        inner join  dbo.Classificator5 c5 on e.ID = c5.ID;
) AS V;

The query engine sees this complicated query, and it has information (but probably not SQL of view definitions) that describe view indexes that have been defined. Given that this query and the view indexes both have multiple joins and casts, matching is a hard job.

Keep in mind that you know the joins and matches are identical in this query and the view indexes, but the query processor doesn't know that. It treats this query just the same as if it joined five copies of Classificator3, or if one of the columns was 'NQ'+CAST(c2.CODE as varchar(12)). The view index matcher (assuming it made any attempt to match this complicated a query) would have to match every detail of this query to the details of view indexes on the tables involved.

The query engine has as its #1 goal to figure out a way to execute the query efficiently. It's probably not designed to spend a lot of time trying to match every detail of a five-way join and CASTs to a view index.

If I had to guess, I suspect the view index matcher sees that the result columns of the query are not even columns of any underlying table (because of the CAST) and simply doesn't bother trying anything. Added: I'm wrong. I just tried Martin's suggestion of updating statistics to make the query expensive, and a view index was matched for some of these queries without NOEXPAND. The view matcher is cleverer than I thought! So the issue is that the view matcher probably tries harder to match a complicated query if its cost is very high.

Use the NOEXPAND hint instead of expecting the query engine to be able to figure out what matches here. NOEXPAND is absolutely your friend, because then the query engine gets to see

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

and it's then immediately obvious to the view index matcher that there is a useful index.

(Note: Your SQL Fiddle code has all 5 foreign key references to the same table, which is probably not what you want.)

Topcoat

Thank you for finding the error! It interesting that I make heavy SELECT * part to try to force QO use indexed views, when Martin enlarge pagecount... I will continue experiments. – Norbert

Answer 3

Use the WITH (NOExpand) hint if you are on SQL Server Enterprise

Your query would be SELECT * FROM EntityView with (noexpand)

Airport

if they're using Enterprise (or Developer), they shouldn't need to use the WITH (NOEXPAND) hint – Milburn

The difference is that Enterprise edition without the hint may decide not to use the indexed view but the base tables instead. – Airport

But if you want to force it to use the indexed view, you always need to use WITH (NOEXPAND) - that's not something conditional to them using Enterprise edition. At lower editions, it can only use the index if this hint it applied. – Milburn

then it could also be that the optimizer reaches the tipping point. Use the FORCESEEK in combination with the NOEXPAND hint instead – Airport

This is a valid workaround but does not address the deeper issue. Why doesn't view matching work and how can it be enabled? – Fungiform

See my answer. All NOEXPAND does is prevent the expansion of view definitions when it sends the query to the engine. In this example, the suitability of an index is immediately obvious only if the view definitions are not expanded. If the views are expanded, the query engine sees a five-way join with a bunch of casts, and probably doesn't even bother trying to match it with some view index on hand. – Topcoat