Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
SELECT  TM.TimelineID,
           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,
          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

                   TimelineMaterialized (EmployeeID, CreateUTC, CultureCode, TimelineID)

Test SQL

-- NO HINT - - -  - - -  - - -  - - -  - - - 
SELECT  *                 --yes yes, star is bad ...just a test example
FROM    TimelineMaterialized TM 
            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)
            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

11 rows of output - same for both queries

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.
No Hint - huge execution plan

Simple plan when using a hint.

Using Hint - Simple Execution Plan

share|improve this question
In your test SQL, you have /* yes yes, star is bad */ - have you tried specifying a single field? –  Max Vernon Oct 15 '12 at 19:32
Did you see the comment I left on the other question? dba.stackexchange.com/questions/24250/… –  Paul White Oct 15 '12 at 23:55
@MaxVernon - a single field, no. Explicit fields, yes. –  EBarr Oct 16 '12 at 0:33
@MaxVernon - just for giggles I tried DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS before each run and multiple combos of 1,2,and 3 fields. No change to the above behavior. –  EBarr Oct 16 '12 at 0:47
@SQLKiwi - now we're getting somewhere! What makes the clustered index on a view more expensive to pick than 12 other indexes? On successive runs my sqlplan file does show StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound", but after DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS it shows StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut". Yet the path to materialization stays the same. –  EBarr Oct 16 '12 at 6:04

1 Answer 1

up vote 8 down vote accepted

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).

share|improve this answer
Well said -- that explains it. I'm working through the white papers. Since my view was just a test, I'm working through expanding my data sets significantly (16k rows to a few million in the bigger tables). I'm looking for a work factor where the decision starts to tip towards using the index -- i.e. a rule of thumb. –  EBarr Oct 16 '12 at 16:40
@EBarr There isn't an easy way to predict the behaviour (as far as I am aware anyway). The problem with rules of thumb based on observation is that behaviour can, and does, change with each release (full version, service pack, cumulative update...). Even in Enterprise Edition, the best (and only) way to ensure a specified indexed view is actually used is to include WITH (NOEXPAND). That hint also has other potential benefits related to statistics on the indexed view. –  Paul White Oct 17 '12 at 14:32
I get what you're saying about behavior & agree the only way to know what's happening is to test. It's not like there is a list of 300 optimizations & rules we can study. Yet, after working with the product 14 hours a day since SQL 4.2 I have a sense of it's personality, as I'm sure you do. Outside of indexed views, I can pretty much tell how adding/removing an index will affect a give query plan. I have a feel for the range of reads on a given query,etc... I'm just looking to become more familiar with the personality in this respect. –  EBarr Oct 17 '12 at 14:42

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.