Jakie czynniki wpływają na wybór indeksu klastrowanego widoku indeksu?

W skrócie
Jakie czynniki wpływają na wybór przez indeksator indeksu indeksu widoku indeksowanego?

Wydaje mi się, że indeksowane widoki są sprzeczne z tym, co rozumiem na temat tego, jak Optymalizator wybiera indeksy. Widziałem to wcześniej , ale OP nie został zbyt dobrze przyjęty. Naprawdę szukam drogowskazów , ale wymyślę pseudo przykład, a następnie opublikuję prawdziwy przykład z dużą ilością DDL, danych wyjściowych, przykładów.

Załóżmy, że używam Enterprise 2008+, rozumiem with(noexpand)

Pseudo przykład

Weźmy ten pseudo przykład: tworzę widok z 22 złączeniami, 17 filtrami i kucykiem cyrkowym, który przecina kilka 10 milionów tabel wierszy. Ten widok jest kosztowny (tak, z dużą literą E), aby się zmaterializować. Będę SCHEMABIND i indeksować widok. Następnie a SELECT a,b FROM AnIndexedView WHERE theClusterKeyField < 84. W logice Optimizera, która mi się wymyka, wykonywane są podstawowe sprzężenia.

Wynik:

• Bez podpowiedzi: 4825 odczytów dla 720 wierszy, 47 jednostek centralnych na 76 ms i szacunkowy koszt subdrzewa 0,30523.
• Z podpowiedź: 17 odczytów, 720 wierszy, 15 procesorów w ciągu 4 ms i szacunkowy koszt poddrzewa 0,007253

Co tu się dzieje? Wypróbowałem to w Enterprise 2008, 2008-R2 i 2012. Pod każdym pomiarem, o którym mogę myśleć, użycie indeksu widoku jest znacznie bardziej wydajne. Nie mam problemu z wąchaniem parametrów ani wypaczonych danych, ponieważ jest to ad hock.

Prawdziwy (długi) przykład

Jeśli nie jesteś masochistą dotykowym, prawdopodobnie nie potrzebujesz lub nie chcesz przeczytać tej części.

Wersja
Tak, przedsiębiorstwo.

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 10 lutego 2012 19:39:15 Prawa autorskie (c) Microsoft Corporation Enterprise Edition (64-bit) w systemie Windows NT 6.2 (kompilacja 9200:) (Hypervisor)

Widok

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

Indeks klastrowy

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

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

Wynik = 11 rzędów wyniku

Wyjście profilera
4 górne wiersze nie zawierają podpowiedzi. Dolne 4 linie używają podpowiedzi.

Plany wykonania
GitHub Gist dla obu planów wykonania w formacie SQLPlan

Brak planu wykonania podpowiedzi - dlaczego nie skorzystać z indeksu klastrowanego, który przekazałem panu SQL? Jest skupiony na 3 polach filtrów. Spróbuj, może ci się spodobać.

Prosty plan przy użyciu podpowiedzi.

Dopasowywanie widoków indeksowanych jest stosunkowo kosztowną operacją *, dlatego optymalizator próbuje najpierw innych szybkich i łatwych transformacji. Jeśli zdarzy się, że opracują tani plan (w twoim przypadku 0,05 jednostki), optymalizacja kończy się wcześnie. Założeniem jest to, że dalsza optymalizacja zużyłaby więcej czasu niż zaoszczędzono. Pamiętaj, że głównym celem optymalizatora jest szybko „wystarczająco dobry” plan.

Korzystanie z indeksu klastrowego w widoku samo w sobie nie jest drogie, ale proces dopasowania logicznego drzewa zapytań do potencjalnych widoków indeksowanych może być. Jak wspomniałem w komentarzu do drugiego pytania, odwołanie do widoku w zapytaniu jest rozszerzane przed optymalizacją, więc optymalizator nie wie, że napisałeś zapytanie przeciwko widokowi - widzi tylko rozwinięte drzewo (jakby widok został wstawiony).

„Dobry wystarczający plan” oznacza, że ​​optymalizator znalazł porządny plan i zatrzymał się wcześnie w fazie eksploracji. „TimeOut” oznacza, że ​​przekroczył liczbę kroków optymalizacji, które określił jako „budżet” na początku bieżącej fazy.

Budżet jest ustalany na podstawie kosztu najlepszego planu znalezionego w poprzedniej fazie. Przy tak niedrogim zapytaniu (0,05) liczba ruchów budżetowych będzie dość niewielka i szybko wyczerpana przez regularną transformację, biorąc pod uwagę liczbę złączeń biorących udział w przykładowym zapytaniu (na przykład istnieje wiele sposobów zmiany kolejności połączeń wewnętrznych) .

Jeśli chcesz dowiedzieć się więcej o tym, dlaczego indeksowane dopasowywanie widoków jest drogie, a zatem pozostawione na późniejsze etapy optymalizacji i / lub rozważane tylko w przypadku bardziej kosztownych zapytań, istnieją dwa dokumenty badawcze Microsoft na ten temat tutaj (pdf) i tutaj (cytat) ).

Innym istotnym czynnikiem jest to, że indeksowane dopasowanie widoku nie jest dostępne w fazie optymalizacji 0 (przetwarzanie transakcji).

Dalsza lektura:

Indeksowane widoki i statystyki

* i dostępne tylko w wersji Enterprise Edition (lub równoważnej)