What factors go into an Indexed View's Clustered Index being selected?

Briefly

What factors go into they query optimizer's selection of an indexed view's index?

For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this asked before, but the OP wasn't too well received. I'm really looking for guideposts, but I'll concoct a pseudo example, then post real example with with a lot of DDL, output, examples.


Assume I'm using Enterprise 2008+, understand with(noexpand)

Pseudo Example

Take this pseudo example: I create a view with 22 joins, 17 filters, and a circus pony that crosses a bunch of 10 million row tables. This view is Expensive (yep, with a capital E) to materialize. I'll SCHEMABIND and Index the view. Then a SELECT a,b FROM AnIndexedView WHERE theClusterKeyField

• With Hint: 17 reads, 720 rows, 15 cpu over 4ms, and an

estimated subtree cost of 0.007253

So what's going on here? I've tried it in Enterprise 2008, 2008-R2 and 2012. By every metric I can think of using the view's index is vastly more efficient. I don't have parameter sniffing issue or skewed data, since this is ad hock.

A Real (Long) Example

Unless you are a touch masochistic you probably don't need or want to read this part.

The Version

Yep, enterprise.


Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on
Windows NT 6.2 (Build 9200: ) (Hypervisor)

The View

``
CREATE VIEW dbo.TimelineMaterialized WITH SCHEMABINDING
AS
SELECT TM.TimelineID,
TM.TimelineTypeID,
TM.EmployeeID,
TM.CreateUTC,
CUL.CultureCode,
CASE
WHEN TM.CustomerMessageID > 0 THEN TM.CustomerMessageID
WHEN TM.CustomerSessionID > 0 THEN TM.CustomerSessionID
WHEN TM.NewItemTagID > 0 THEN TM.NewItemTagID
WHEN TM.OutfitID > 0 THEN TM.OutfitID
WHEN TM.ProductTransactionID > 0 THEN TM.ProductTransactionID

Matching indexed views is a relatively expensive operation*, so the optimizer tries other quick and easy transformations first. If those happen to produce a cheap plan (0.05 units in your case) optimization ends early. The bet is that continued optimization would consume more time than it saved. Remember the optimizer's primary goal is a 'good enough' plan quickly.

Using the clustered index on the view isn't expensive in itself, but the process of matching a logical query tree to potential indexed views can be. As I mentioned in a comment on the other question, the view reference in the query is expanded before optimization, so the optimizer doesn't know you wrote the query against the view in the first place - it sees only the expanded tree (as if the view had been in-lined).

"Good Enough Plan" means the optimizer found a decent plan and stopped early in an exploration phase. "TimeOut" means it exceeded the number of optimization steps it set itself as a 'budget' at the start of the current phase.

The budget is set based on the cost of the best plan found in a previous phase. With such a low-cost query (0.05) the number of budgeted moves will be quite small, and quickly exhausted by regular transformation given the number of joins involved in your sample query (there are lots of ways to rearrange inner joins, for example).

If you are interested to know more about why indexed view matching is expensive, and therefore left for later stages of optimization and/or only considered for more costly queries, there are two Microsoft Research Papers on the topic here (pdf) and here (citeseer).

Another relevant factor is that indexed view matching is not available in optimization phase 0 (transaction processing).

Further reading:

Indexed Views and Statistics

* and only available in Enterprise Edition (or equivalent)

StackExchange Database Administrators Q#26979, answer score: 27