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

Question

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 < 84. In Optimizer logic that eludes me the underlying joins are performed.

The result:

• No Hint: 4825 reads for 720 rows, 47 cpu over 76ms, and an estimated sub tree cost of 0.30523.
• 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
           ELSE 0 END  As HrefId,
        CASE 
          WHEN TM.CustomerMessageID    > 0 THEN IsNull(C.Name, 'N/A')   
          WHEN TM.CustomerSessionID    > 0 THEN IsNull(C.Name, 'N/A')
          WHEN TM.NewItemTagID         > 0 THEN IsNull(NI.Title, 'N/A')
          WHEN TM.OutfitID             > 0 THEN IsNull(O.Name, 'N/A')
          WHEN TM.ProductTransactionID > 0 THEN IsNull(PT_PL.NameLocalized, 'N/A')
                 END as HrefText

FROM       dbo.Timeline TM
INNER JOIN dbo.CustomerSession    CS    ON TM.CustomerSessionID    = CS.CustomerSessionID
INNER JOIN dbo.CustomerMessage    CM    ON TM.CustomerMessageID    = CM.CustomerMessageID
INNER JOIN dbo.Outfit             O     ON PO.OutfitID             = O.OutfitID
INNER JOIN dbo.ProductTransaction PT    ON TM.ProductTransactionID = PT.ProductTransactionID
INNER JOIN dbo.Product            PT_P  ON PT.ProductID            = PT_P.ProductID
INNER JOIN dbo.ProductLang        PT_PL ON PT_P.ProductID          = PT_PL.ProductID
INNER JOIN dbo.Culture            CUL   ON PT_PL.CultureID         = CUL.CultureID
INNER JOIN dbo.NewsItemTag        NIT   ON TM.NewsItemTagID        = NIT.NewsItemTagID
INNER JOIN dbo.NewsItem           NI    ON NIT.NewsItemID          = NI.NewsItemID
INNER JOIN dbo.Customer           C     ON  C.CustomerID = CASE 
                                             WHEN TM.TimelineTypeID = 1 THEN CM.CustomerID 
                                             WHEN TM.TimelineTypeID = 5 THEN CS.CustomerID
                                             ELSE 0 END

WHERE        CUL.IsActive = 1

Clustered Index

CREATE UNIQUE CLUSTERED INDEX PK_TimelineMaterialized  ON 
                   TimelineMaterialized (EmployeeID, CreateUTC, CultureCode, TimelineID)

Test SQL

-- NO HINT - - -  - - -  - - -  - - -  - - - 
SELECT  *                 --yes yes, star is bad ...just a test example
FROM    TimelineMaterialized TM 
WHERE 
            TM.EmployeeID   = 2
        AND TM.CultureCode  = 'en-US'
        AND TM.CreateUTC    > '9/10/2012'
        AND TM.CreateUTC    < '9/11/2012'

-- WITH HINT - - -  - - -  - - -  - - -  - - - 
SELECT  *               
FROM    TimelineMaterialized TM with(noexpand)
WHERE 
            TM.EmployeeID   = 2
        AND TM.CultureCode  = 'en-US'
        AND TM.CreateUTC    > '9/10/2012'
        AND TM.CreateUTC    < '9/11/2012'

Result = 11 Rows of Output

Profiler Output
The top 4 lines are without a hint. The bottom 4 lines are using the hint.

Execution Plans
GitHub Gist for both Execution Plans in SQLPlan format

No Hint Execution plan -- why not use the clustered index I gave you Mr. SQL? It's clusterd on the 3 filter fields. Try it, you might like it.

Simple plan when using a hint.

Answer 1

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)