Plan wykonania NIE korzysta z INDEKSU, wykorzystuje skanowanie tabeli

Wiem, że jeśli chodzi o użycie indeksu lub skanowania tabeli, SQL Server używa statystyk, aby zobaczyć, który jest lepszy.

Mam stół z 20 milionami wierszy. Mam indeks na (SnapshotKey, Measure) i to zapytanie:

select Measure, SnapshotKey, MeasureBand
from t1
where Measure = 'FinanceFICOScore'
group by Measure, SnapshotKey, MeasureBand

Kwerenda zwraca 500 000 wierszy. Tak więc zapytanie wybiera tylko 2,5% wierszy tabeli.

Pytanie brzmi, dlaczego SQL Server nie używa indeksu nieklastrowanego, który mam, i zamiast tego używa skanowania tabeli?

Statystyki są aktualizowane.

Warto wspomnieć, że wydajność zapytania jest jednak dobra.

Skanowanie tabeli

Wymuszony indeks

Struktura tabeli / indeksu

CREATE TABLE [t1](
    [SnapshotKey] [int] NOT NULL,
    [SnapshotDt] [date] NOT NULL,
    [Measure] [nvarchar](30) NOT NULL,
    [MeasureBand] [nvarchar](30) NOT NULL,
    -- and many more fields
) ON [PRIMARY]

Brak PK na stole, ponieważ jest to hurtownia danych.

CREATE NONCLUSTERED INDEX [nci_SnapshotKeyMeasure] ON [t1]
(
    [SnapshotKey] ASC,
    [Measure] ASC
)

Wyszukiwanie indeksu może nie być najlepszym wyborem, jeśli zwrócisz wiele wierszy i / lub rzędy są bardzo szerokie. Wyszukiwanie może być kosztowne, jeśli Twój indeks nie obejmuje. Zobacz # 2 tutaj .

W twoim scenariuszu optymalizator zapytań szacuje, że wykonanie 50 000 pojedynczych wyszukiwań będzie droższe niż pojedynczy skan. Wybór optymalizatora między skanowaniem a wyszukiwaniem (z wyszukiwaniem RID dla kolumn wymaganych przez zapytanie, ale nieobecnych w indeksie nieklastrowanym) jest oparty na szacowanym koszcie każdej alternatywy.

Optymalizator zawsze wybiera najtańszą alternatywę, którą bierze pod uwagę. Jeśli spojrzysz na właściwość Szacowany koszt poddrzewy w węźle głównym dwóch planów wykonania, zobaczysz, że plan skanowania ma niższy szacowany koszt niż plan wyszukiwania. W rezultacie optymalizator wybrał skan. To jest zasadniczo odpowiedź na twoje pytanie.

Teraz model kosztów stosowany przez optymalizator opiera się na założeniach i „magicznych liczbach”, które raczej nie pasują do parametrów wydajnościowych twojego systemu. W szczególności, jednym z założeń przyjętych w modelu jest to, że zapytanie zaczyna się wykonywać, gdy żadna z wymaganych danych lub stron indeksowych nie jest już w pamięci. Innym jest to, że sekwencyjne operacje we / wy (oczekiwane dla skanu) są tańsze niż losowy wzorzec operacji we / wy przyjęty dla wyszukiwania RID. Istnieje wiele innych takich założeń i zastrzeżeń, o wiele za dużo, aby je szczegółowo omówić.

Niemniej jednak wykazano , że model kosztów jako całości generuje ogólnie „wystarczająco dobre” plany dla większości zapytań, na większości schematów baz danych, na większości konfiguracji sprzętowych, przez większość czasu, wszędzie. To spore osiągnięcie, jeśli się nad tym zastanowić.

Ograniczenia modelu i inne czynniki będą czasem oznaczać, że optymalizator wybiera plan, który w rzeczywistości nie jest „wystarczająco dobry”. Mówisz, że „wydajność jest dobra”, więc nie wydaje się, aby tak było w tym przypadku.

W rzeczywistości masz 595,947 pasujących wierszy, co stanowi około 3% Twoich danych. Koszt wyszukiwania sumuje się szybko. Załóżmy, że masz w tabeli 100 wierszy na stronę, czyli 200 000 stron do odczytania podczas skanowania tabeli. To o wiele tańsze niż 59 5947 wyszukiwań.

Z GROUP BYklauzulą ​​zawartą w pytaniu myślę, że lepiej Ci będzie mieć złożony klucz złożony (Measure, SnapshotKey, MeasureBand).

Spójrz na sugestię „brakującego indeksu”. Mówi, aby dołączyć kolumny, aby uniknąć wyszukiwania. Mówiąc bardziej ogólnie, jeśli odwołujesz się do innych kolumn w zapytaniu, będą one musiały znajdować się w kluczach lub INCLUDEklauzuli nowego indeksu. W przeciwnym razie nadal będzie trzeba wykonać 595,947 wyszukiwań, aby uzyskać te wartości.

Na przykład dla zapytania:

select Measure, SnapshotKey, MeasureBand, SUM(NumLoans), SUM(PrinBal)
from t1
where Measure = 'FinanceFICOScore'
group by Measure, SnapshotKey, MeasureBand

... potrzebujesz:

CREATE INDEX ixWhatever 
ON t1 (Measure, SnapshotKey, MeasureBand) 
INCLUDE (NumLoans,PrinBal);

1. Pole w warunku GDZIE nie jest wiodącym polem indeksu.

2. Zostały measurezdefiniowane jako NVARCHAR więc poprzedzić dosłownym z N: where Measure = N'FinanceFICOScore'.

Rozważ utworzenie indeksu klastrowanego SnapshotKey. Jeśli jest unikalny, może to być PK (i Clustered). Jeśli nie jest unikalny, nie może być PK, ale nadal może być nie unikalnym indeksem klastrowym. Wówczas indeks nieklastrowany byłby tylko w measurekolumnie.

Biorąc pod uwagę, że pierwsze pole GROUP BYrównież jest measure, skorzystałoby na tym, że będzie measurewiodącym polem.

W rzeczywistości dla tej operacji może być konieczne zdefiniowanie indeksu nieklastrowego Measure, SnapshotKey, MeasureBand, w dokładnie takiej kolejności, w jakiej odpowiada GROUP BYklauzuli. MeasureBandPod względem rozmiaru, który naprawdę dodaje, ponieważ indeks nieklastrowany jest już oparty na indeksie Measurei MeasureKeyjest już uwzględniony w indeksie, ponieważ jest teraz kluczem indeksu klastrowanego (nie, Measurenie będzie duplikowany w indeksie nieklastrowanym).

@Rob wspomniał w usuniętym komentarzu do swojej odpowiedzi, że rozwiązanie tego problemu wymaga jedynie zdefiniowania Indeksu nieklastrowego za pomocą tych trzech pól w tej kolejności oraz że utworzenie indeksu klastrowanego (nie unikatowego) SnapshotKeynie jest konieczne . Chociaż prawdopodobnie ma rację (miałem nadzieję, że mniej pól zadziała), nadal twierdzę, że posiadanie Indeksu klastrowego jest korzystne nie tylko dla tej operacji, ale prawdopodobnie dla większości innych.