对我来说,索引视图似乎无法理解我对Optimizer如何选择索引的理解.我见过this asked before,但OP并不太受欢迎.我真的在寻找路标,但我会编写一个伪示例,然后使用大量的DDL,输出和示例发布真实示例.

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

伪示例

以这个伪示例为例：我创建了一个包含22个连接,17个过滤器和一个跨越一堆1000万行表的马戏团小马的视图.这个观点是昂贵的(是的,有大写字母E)来实现.我将SCHEMABIND和索引视图.然后是SELECT a,b FROM AnIndexedView WHERE theClusterKeyField< 84.在优化器逻辑中,我无法执行基础连接. 结果：
>没有提示：4825读取720行,47 cpu超过76ms,和
估计子树成本为0.30523.
>使用提示：17个读取,720行,超过4ms的15个cpu,以及一个
估计子树成本为0.007253

那么这里发生了什么？我已经在Enterprise 2008,2008-R2和2012中尝试过了.我可以想到使用视图索引的每个指标都非常有效.我没有参数嗅探问题或倾斜数据,因为这是广告.

真实(长)的例子

除非你是一个自虐的人,否则你可能不需要或不想阅读这部分内容.

版本
是的,企业.

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)

风景

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

聚集指数

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

测试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'

结果= 11行输出

Profiler输出
前4行没有提示.底部的4行是使用提示.

执行计划
GitHub Gist for both Execution Plans in SQLPlan format

没有提示执行计划 – 为什么不使用我给你的sql先生的聚集索引？它集中在3个过滤器字段上.尝试一下,你可能会喜欢它.

使用提示时的简单计划.