Quais fatores entram na seleção do índice agrupado de uma exibição indexada?

Resumidamente
, quais fatores entram na seleção do otimizador de consulta do índice de uma exibição indexada?

Para mim, exibições indexadas parecem desafiar o que eu entendo sobre como o Optimizer escolhe índices. Já vi isso ser perguntado antes , mas o OP não foi muito bem recebido. Estou realmente procurando por guias , mas vou inventar um pseudo-exemplo e, em seguida, postar um exemplo real com muito DDL, saída, exemplos.

Suponha que eu esteja usando o Enterprise 2008+, entendawith(noexpand)

Pseudo Exemplo

Veja este pseudo exemplo: eu crio uma visão com 22 junções, 17 filtros e um pônei de circo que cruza um monte de tabelas de 10 milhões de linhas. Essa visão é cara (sim, com E maiúsculo) para se materializar. Vou SCHEMABIND e Indexar a exibição. Então um SELECT a,b FROM AnIndexedView WHERE theClusterKeyField < 84. Na lógica do Optimizer que me escapa, as junções subjacentes são executadas.

O resultado:

• Nenhuma dica: 4825 leituras para 720 linhas, 47 cpu em 76 ms e um custo estimado de subárvore de 0,30523.
• Com dica: 17 leituras, 720 linhas, 15 cpu em 4ms e um custo estimado de subárvore de 0,007253

Então, o que está acontecendo aqui? Eu tentei no Enterprise 2008, 2008-R2 e 2012. Por cada métrica que posso pensar em usar o índice da exibição é muito mais eficiente. Não tenho problema de detecção de parâmetro ou dados distorcidos, pois isso é ad hock.

Um exemplo real (longo)

A menos que você seja um pouco masoquista, provavelmente não precisa ou não quer ler esta parte.

A versão
Sim, empresa.

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 10 de fevereiro de 2012 19:39:15 Copyright (c) Microsoft Corporation Enterprise Edition (64 bits) no Windows NT 6.2 (Build 9200: ) (Hypervisor)

A vista

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

índice agrupado

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

SQL de teste

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

Resultado = 11 Linhas de Saída

Saída do Profiler
As 4 primeiras linhas não contêm nenhuma dica. As 4 linhas inferiores estão usando a dica.

Planos de execução
GitHub Gist para ambos os planos de execução no formato SQLPlan

Nenhum plano de Hint Execution - por que não usar o índice clusterizado que lhe dei, Sr. SQL? Está agrupado nos 3 campos de filtro. Experimente, você pode gostar.

Plano simples ao usar uma dica.