Podsumowanie
Główne problemy to:
- Wybór planu optymalizatora zakłada równomierny rozkład wartości.
- Brak odpowiednich indeksów oznacza:
- Skanowanie stołu jest jedyną opcją.
- Łączenie jest naiwnym łączeniem zagnieżdżonych pętli, a nie łączeniem zagnieżdżonych indeksów . W naiwnym złączeniu predykaty łączenia są oceniane na złączeniu, a nie zepchnięte na wewnętrzną stronę złączenia.
Detale
Te dwa plany są zasadniczo bardzo podobne, chociaż wydajność może się bardzo różnić:
Planuj z dodatkowymi kolumnami
Biorąc tę z dodatkowymi kolumnami, która nie kończy się w rozsądnym czasie:
Ciekawe funkcje to:
- Top w węźle 0 ogranicza liczbę wierszy zwróconych do 100. Ustawia także cel wiersza dla optymalizatora, więc wszystko poniżej planu w planie jest wybierane, aby szybko zwrócić pierwsze 100 wierszy.
- Skanowanie w węźle 4 znajduje wiersze z tabeli, w których
Start_Time
wartość nie jest pusta, State
wynosi 3 lub 4 i Operation_Type
jest jedną z wymienionych wartości. Tabela jest w pełni skanowana raz, a każdy wiersz jest testowany pod kątem wspomnianych predykatów. Tylko wiersze, które przejdą wszystkie testy, przechodzą do sortowania. Optymalizator szacuje, że zakwalifikuje się 38 283 wierszy.
- Sortuj w węźle 3 zużywa wszystkie wiersze ze skanowania w węźle 4 i sortuje je w kolejności
Start_Time DESC
. Jest to ostateczna kolejność prezentacji żądana przez zapytanie.
- Optymalizator szacuje, że 93 wiersze (w rzeczywistości 93.2791) będą musiały zostać odczytane z sortowania, aby cały plan zwrócił 100 wierszy (uwzględniając oczekiwany efekt sprzężenia).
- Oczekuje się, że łączenie zagnieżdżonych pętli w węźle 2 wykona swoje wewnętrzne wejście (dolna gałąź) 94 razy (w rzeczywistości 94,2791). Dodatkowy rząd jest wymagany przez wymianę równoległości stopu w węźle 1 ze względów technicznych.
- Skanuj w węźle 5 w pełni skanuje tabelę podczas każdej iteracji. Okazuje się, wiersze którym
Start_Time
jest różne od zera, a State
jest 3 lub 4. To oszacowanych produkować 400,875 rzędów na każdej iteracji. Ponad 94.2791 iteracji, całkowita liczba wierszy wynosi prawie 38 milionów.
- Łączenie zagnieżdżone w węźle 2 stosuje również predykaty łączenia. Sprawdza, czy
Operation_Type
pasuje, czy Start_Time
z węzła 4 jest mniejszy niż Start_Time
z węzła 5, czy Start_Time
z węzła 5 jest mniejszy niż Finish_Time
z węzła 4 i czy te dwie Id
wartości się nie zgadzają.
- Zbierz strumienie (zatrzymaj wymianę równoległości) w węźle 1 scala uporządkowane strumienie z każdego wątku, aż powstanie 100 wierszy. Zachowujący porządek charakter scalania wielu strumieni jest tym, co wymaga dodatkowego wiersza wspomnianego w kroku 5.
Wielka nieefektywność występuje oczywiście w krokach 6 i 7 powyżej. Pełne skanowanie tabeli w węźle 5 dla każdej iteracji jest nawet nieco rozsądne, jeśli dzieje się to tylko 94 razy, jak przewiduje optymalizator. Zestaw około 38 milionów wierszy porównań w węźle 2 to także duży koszt.
Co istotne, oszacowanie celu rzędu rzędu 93/94 również jest prawdopodobnie błędne, ponieważ zależy od rozkładu wartości. Optymalizator zakłada równomierny rozkład przy braku bardziej szczegółowych informacji. Mówiąc prościej, oznacza to, że jeśli oczekuje się, że kwalifikuje się 1% wierszy w tabeli, optymalizator powoduje, że aby znaleźć 1 pasujący wiersz, musi odczytać 100 wierszy.
Jeśli uruchomisz tę kwerendę do końca (co może zająć bardzo dużo czasu), najprawdopodobniej odkryjesz, że z Sortowania trzeba odczytać wiele więcej niż 93/94 wierszy, aby ostatecznie wygenerować 100 wierszy. W najgorszym przypadku setny rząd zostałby znaleziony przy użyciu ostatniego rzędu z sortowania. Zakładając, że oszacowanie optymalizatora w węźle 4 jest prawidłowe, oznacza to uruchomienie skanowania w węźle 5 38 284 razy, w sumie około 15 miliardów wierszy. Może być więcej, jeśli szacunki skanowania są również wyłączone.
Ten plan wykonania zawiera również ostrzeżenie o braku indeksu:
/*
The Query Processor estimates that implementing the following index
could improve the query cost by 72.7096%.
WARNING: This is only an estimate, and the Query Processor is making
this recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide
impact, including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Batch_Tasks_Queue] ([Operation_Type],[State],[Start_Time])
INCLUDE ([Id],[Parameters])
Optymalizator ostrzega o tym, że dodanie indeksu do tabeli poprawiłoby wydajność.
Planuj bez dodatkowych kolumn
Zasadniczo jest to dokładnie ten sam plan, co poprzedni, z dodaniem szpuli indeksu w węźle 6 i filtra w węźle 5. Ważne różnice to:
- Szpula indeksu w węźle 6 jest chętną szpulą. Z niecierpliwością zużywa wynik skanowania poniżej i tworzy tymczasowy indeks z kluczem
Operation_Type
i Start_Time
, z Id
kolumną bez klucza.
- Łączenie zagnieżdżonych pętli w węźle 2 jest teraz złączeniem indeksu. Nie łączą orzeczniki ocenia się tu zamiast aktualnych wartości dla każdej iteracji,
Operation_Type
, Start_Time
, Finish_Time
i Id
ze skanowania w węźle 4 są przemieszczane do gałęzi wewnętrzna po stronie zewnętrznej, jak odniesienia.
- Skanowanie w węźle 7 jest wykonywane tylko raz.
- Bufor indeksu w węźle 6 wyszukuje wiersze z indeksu tymczasowego, w którym
Operation_Type
odpowiada bieżącej zewnętrznej wartości odniesienia, i Start_Time
znajduje się w zakresie określonym przez odniesienia zewnętrzne Start_Time
i Finish_Time
.
- Filtr w węźle 5 testuje
Id
wartości z bufora indeksu pod kątem nierówności względem bieżącej zewnętrznej wartości odniesienia Id
.
Najważniejsze ulepszenia to:
- Skanowanie strony wewnętrznej jest wykonywane tylko raz
- Tymczasowy indeks na (
Operation_Type
, Start_Time
) z Id
dołączoną kolumną pozwala na łączenie zagnieżdżonych pętli indeksu. Indeks służy do wyszukiwania pasujących wierszy na każdej iteracji zamiast skanowania całego stołu za każdym razem.
Tak jak poprzednio, optymalizator zawiera ostrzeżenie o brakującym indeksie:
/*
The Query Processor estimates that implementing the following index
could improve the query cost by 24.1475%.
WARNING: This is only an estimate, and the Query Processor is making
this recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide
impact, including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Batch_Tasks_Queue] ([State],[Start_Time])
INCLUDE ([Id],[Operation_Type])
GO
Wniosek
Plan bez dodatkowych kolumn jest szybszy, ponieważ optymalizator postanowił utworzyć dla ciebie tymczasowy indeks.
Plan z dodatkowymi kolumnami spowodowałby, że tymczasowy indeks byłby droższy w budowie. [Parameters
Kolumny] to nvarchar(2000)
, które sumują się z 4000 bajtów do każdej linii wskaźnika. Dodatkowy koszt jest wystarczający, aby przekonać optymalizator, że utworzenie tymczasowego indeksu przy każdym wykonaniu nie zwróci się.
Optymalizator ostrzega w obu przypadkach, że lepszym rozwiązaniem byłby indeks stały. Idealna kompozycja indeksu zależy od szerszego obciążenia pracą. W przypadku tego konkretnego zapytania sugerowane indeksy są rozsądnym punktem wyjścia, ale należy zrozumieć korzyści i koszty z tym związane.
Rekomendacje
Dla tego zapytania korzystny byłby szeroki zakres możliwych indeksów. Ważne jest to, że potrzebny jest jakiś indeks nieklastrowany. Na podstawie dostarczonych informacji rozsądnym moim zdaniem byłby:
CREATE NONCLUSTERED INDEX i1
ON dbo.Batch_Tasks_Queue (Start_Time DESC)
INCLUDE (Operation_Type, [State], Finish_Time);
Kusiłbym również, by trochę lepiej zorganizować zapytanie i opóźnić wyszukiwanie szerokich [Parameters]
kolumn w indeksie klastrowym, aż do znalezienia 100 pierwszych wierszy (używając Id
jako klucza):
SELECT TOP (100)
BTQ1.id,
BTQ2.id,
BTQ3.[Parameters],
BTQ4.[Parameters]
FROM dbo.Batch_Tasks_Queue AS BTQ1
JOIN dbo.Batch_Tasks_Queue AS BTQ2 WITH (FORCESEEK)
ON BTQ2.Operation_Type = BTQ1.Operation_Type
AND BTQ2.Start_Time > BTQ1.Start_Time
AND BTQ2.Start_Time < BTQ1.Finish_Time
AND BTQ2.id != BTQ1.id
-- Look up the [Parameters] values
JOIN dbo.Batch_Tasks_Queue AS BTQ3
ON BTQ3.Id = BTQ1.Id
JOIN dbo.Batch_Tasks_Queue AS BTQ4
ON BTQ4.Id = BTQ2.Id
WHERE
BTQ1.[State] IN (3, 4)
AND BTQ2.[State] IN (3, 4)
AND BTQ1.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
AND BTQ2.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
-- These predicates are not strictly needed
AND BTQ1.Start_Time IS NOT NULL
AND BTQ2.Start_Time IS NOT NULL
ORDER BY
BTQ1.Start_Time DESC;
Tam, gdzie [Parameters]
kolumny nie są potrzebne, zapytanie można uprościć:
SELECT TOP (100)
BTQ1.id,
BTQ2.id
FROM dbo.Batch_Tasks_Queue AS BTQ1
JOIN dbo.Batch_Tasks_Queue AS BTQ2 WITH (FORCESEEK)
ON BTQ2.Operation_Type = BTQ1.Operation_Type
AND BTQ2.Start_Time > BTQ1.Start_Time
AND BTQ2.Start_Time < BTQ1.Finish_Time
AND BTQ2.id != BTQ1.id
WHERE
BTQ1.[State] IN (3, 4)
AND BTQ2.[State] IN (3, 4)
AND BTQ1.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
AND BTQ2.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
AND BTQ1.Start_Time IS NOT NULL
AND BTQ2.Start_Time IS NOT NULL
ORDER BY
BTQ1.Start_Time DESC;
FORCESEEK
Wskazówką jest to, aby pomóc zapewnić optymalizator wybiera indeksowanego zagnieżdżone pętle zaplanować (istnieje pokusa, oparte na kosztach dla optymalizatora, aby wybrać skrót lub (many-many) scalające inaczej, co nie ma tendencję do pracy również z tego typu zapytanie w praktyce. Oba kończą się dużymi resztkami; wiele elementów na wiadro w przypadku skrótu i wiele przewinięć do scalenia).
Alternatywny
Gdyby zapytanie (w tym jego określone wartości) miało szczególne znaczenie dla wydajności odczytu, zamiast tego wziąłbym pod uwagę dwa przefiltrowane indeksy:
CREATE NONCLUSTERED INDEX i1
ON dbo.Batch_Tasks_Queue (Start_Time DESC)
INCLUDE (Operation_Type, [State], Finish_Time)
WHERE
Start_Time IS NOT NULL
AND [State] IN (3, 4)
AND Operation_Type <> 23
AND Operation_Type <> 24
AND Operation_Type <> 25
AND Operation_Type <> 26
AND Operation_Type <> 27
AND Operation_Type <> 28
AND Operation_Type <> 30;
CREATE NONCLUSTERED INDEX i2
ON dbo.Batch_Tasks_Queue (Operation_Type, [State], Start_Time)
WHERE
Start_Time IS NOT NULL
AND [State] IN (3, 4)
AND Operation_Type <> 23
AND Operation_Type <> 24
AND Operation_Type <> 25
AND Operation_Type <> 26
AND Operation_Type <> 27
AND Operation_Type <> 28
AND Operation_Type <> 30;
W przypadku zapytania, które nie wymaga [Parameters]
kolumny, plan szacunkowy z wykorzystaniem przefiltrowanych indeksów wynosi:
Skanowanie indeksu automatycznie zwraca wszystkie kwalifikujące się wiersze bez oceny jakichkolwiek dodatkowych predykatów. Dla każdej iteracji łączenia zagnieżdżonych pętli indeksu przeszukiwanie indeksu wykonuje dwie operacje wyszukiwania:
- Prefiks wyszukiwania pasuje do
Operation_Type
i State
= 3, a następnie szuka zakresu Start_Time
wartości, a predykat resztkowy o Id
nierówności.
- Prefiks wyszukiwania pasuje do
Operation_Type
i State
= 4, a następnie szuka zakresu Start_Time
wartości, a predykat resztkowy na Id
nierówności.
Tam, gdzie [Parameters]
kolumna jest potrzebna, plan zapytań po prostu dodaje maksymalnie 100 wyszukiwań singletonów dla każdej tabeli:
Na koniec należy rozważyć użycie wbudowanych standardowych typów liczb całkowitych zamiast, gdy ma numeric
to zastosowanie.