Nie powinieneś zbytnio polegać na procentach kosztów w planach wykonania. Są to zawsze szacunkowe koszty , nawet w planach po wykonaniu z „rzeczywistymi” liczbami dla takich rzeczy, jak liczba wierszy. Szacunkowe koszty są oparte na modelu, który okazuje się działać całkiem dobrze w celu, do którego jest przeznaczony: umożliwienie optymalizatorowi wyboru pomiędzy różnymi planami wykonania kandydata dla tego samego zapytania. Informacje o kosztach są interesujące i stanowią czynnik do rozważenia, ale rzadko powinny być podstawową miarą dostrajania zapytań. Interpretacja informacji o planie wykonania wymaga szerszego widoku prezentowanych danych.
ItemTran Indeks klastrowany Szukaj operatora
Ten operator to tak naprawdę dwie operacje w jednym. Najpierw operacja wyszukiwania indeksu wyszukuje wszystkie wiersze, które pasują do predykatu product_code_v42 = 'M10BOLT', a następnie do każdego wiersza stosuje się resztkowy predykat bit_field_v41 & 4 = 0. Istnieje domyślna konwersja bit_field_v41z jego typu podstawowego ( tinyintlub smallint) na integer.
Konwersja następuje, ponieważ operator bitowego AND (&) wymaga, aby oba operandy były tego samego typu. Domniemany typ wartości stałej „4” jest liczbą całkowitą, a reguły pierwszeństwa typu danych oznaczają, że bit_field_v41wartość pola o niższym priorytecie jest konwertowana.
Problem (taki jaki jest) można łatwo rozwiązać, pisząc predykat jako bit_field_v41 & CONVERT(tinyint, 4) = 0- co oznacza, że stała wartość ma niższy priorytet i jest konwertowana (podczas ciągłego składania), a nie wartość kolumny. Jeśli bit_field_v41jest tinyintbrak konwersji występuje w ogóle. Podobnie CONVERT(smallint, 4)można użyć, jeśli bit_field_v41jest smallint. To powiedziawszy, konwersja nie stanowi w tym przypadku problemu z wydajnością , ale nadal dobrą praktyką jest dopasowywanie typów i unikanie niejawnych konwersji tam, gdzie to możliwe.
Większa część szacowanego kosztu tego poszukiwania zależy od wielkości stołu bazowego. Chociaż klastrowany klucz indeksu sam w sobie jest dość wąski, wielkość każdego wiersza jest duża. Definicja tabeli nie jest podana, ale tylko kolumny użyte w widoku sumują się do znacznej szerokości wiersza. Ponieważ indeks klastrowany obejmuje wszystkie kolumny, odległość między klastrowanymi kluczami indeksu to szerokość wiersza , a nie szerokość kluczy indeksu . Zastosowanie sufiksów wersji w niektórych kolumnach sugeruje, że prawdziwa tabela zawiera jeszcze więcej kolumn dla poprzednich wersji.
Patrząc na kolumny wyszukiwania, resztowe predykaty i dane wyjściowe, wydajność tego operatora można sprawdzić osobno, budując równoważne zapytanie ( 1 <> 2jest to sztuczka, która zapobiega automatycznej parametryzacji, sprzeczność jest usuwana przez optymalizator i nie pojawia się w plan zapytań):
SELECT
it.booking_no_v32,
it.QtyCheckedOut,
it.QtyReturned,
it.Trans_qty,
it.trans_type_v41
FROM dbo.tblItemTran AS it
WHERE
1 <> 2
AND it.product_code_v42 = 'M10BOLT'
AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0;
Interesująca jest wydajność tego zapytania z zimną pamięcią podręczną danych, ponieważ fragmentacja tabeli (indeksu klastrowanego) miałaby wpływ na wcześniejsze odczytanie. Klucz klastrowania dla tej tabeli zachęca do fragmentacji, dlatego może być ważne regularne utrzymywanie (reorganizacja lub przebudowa) tego indeksu i użycie odpowiedniego, FILLFACTORaby zapewnić miejsce na nowe wiersze między oknami konserwacji indeksu.
Przeprowadziłem test wpływu fragmentacji na odczyt z wyprzedzeniem, używając przykładowych danych wygenerowanych za pomocą SQL Data Generator . Przy użyciu tej samej liczby wierszy tabeli, jak pokazano w planie zapytań pytania, wysoce rozdrobniony indeks klastrowy SELECT * FROM viewzajął 15 sekund później DBCC DROPCLEANBUFFERS. Ten sam test w tych samych warunkach ze świeżo przebudowanym indeksem klastrowym w tabeli ItemTrans zakończony w 3 sekundy.
Jeśli dane tabeli są zwykle całkowicie w pamięci podręcznej, problem fragmentacji jest o wiele mniej ważny. Ale nawet przy małej fragmentacji szerokie wiersze tabeli mogą oznaczać, że liczba odczytów logicznych i fizycznych jest znacznie wyższa niż można by się spodziewać. Możesz również poeksperymentować z dodawaniem i usuwaniem wyrażeń jawnych, CONVERTaby potwierdzić moje oczekiwania, że niejawny problem konwersji nie jest tutaj ważny, z wyjątkiem naruszenia najlepszych praktyk.
Bardziej do rzeczy jest szacowana liczba wierszy opuszczających operator wyszukiwania. Szacunkowy czas optymalizacji wynosi 165 wierszy, ale w czasie wykonywania wygenerowano 4226. Powrócę do tego punktu później, ale głównym powodem rozbieżności jest to, że selektywność resztkowego predykatu (obejmującego bitowe-AND) jest bardzo trudna do przewidzenia przez optymalizator - w rzeczywistości ucieka się do zgadywania.
Filtr operatora
Pokazuję tutaj predykat filtra głównie w celu zilustrowania, w jaki sposób dwie NOT INlisty są połączone, uproszczone, a następnie rozwinięte, a także w celu zapewnienia odniesienia do następnej dyskusji na temat dopasowania skrótów. Kwerendę testową z wyszukiwania można rozszerzyć, aby uwzględnić jej efekty i określić wpływ operatora filtru na wydajność:
SELECT
it.booking_no_v32,
it.trans_type_v41,
it.Trans_qty,
it.QtyReturned,
it.QtyCheckedOut
FROM dbo.tblItemTran AS it
WHERE
it.product_code_v42 = 'M10BOLT'
AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
AND
(
(
it.trans_type_v41 NOT IN (2, 3, 6, 7, 18, 19, 20, 21, 12, 13, 22)
AND it.trans_type_v41 NOT IN (6, 7)
)
OR
(
it.trans_type_v41 NOT IN (6, 7)
)
OR
(
it.trans_type_v41 IN (6, 7)
AND it.QtyCheckedOut = 0
)
OR
(
it.trans_type_v41 IN (6, 7)
AND it.QtyCheckedOut > 0
AND it.trans_qty - (it.QtyCheckedOut - it.QtyReturned) > 0
)
);
Operator obliczania skalarnego w planie definiuje następujące wyrażenie (samo obliczenie jest odraczane, dopóki wynik nie będzie wymagany przez późniejszego operatora):
[Expr1016] = (trans_qty - (QtyCheckedOut - QtyReturned))
Operator dopasowania skrótu
Wykonanie łączenia na typach danych znaków nie jest przyczyną wysokich szacunkowych kosztów tego operatora. Etykietka SSMS pokazuje tylko wpis Sondy kluczy skrótu, ale ważne szczegóły znajdują się w oknie Właściwości SSMS.
Operator dopasowania skrótów buduje tabelę skrótów przy użyciu wartości booking_no_v32kolumny (kompilacja kluczy skrótu) z tabeli ItemTran, a następnie sprawdza zgodność przy użyciu booking_nokolumny (sonda kluczy skrótu) z tabeli rezerwacji. Etykietka SSMS normalnie pokazywałaby również resztkową sondę, ale tekst jest o wiele za długi na etykietkę i jest po prostu pomijany.
Resztka sondy jest podobna do reszty widzianej po wcześniejszym poszukiwaniu indeksu; predykat resztkowy jest oceniany we wszystkich wierszach, które pasują do siebie, aby ustalić, czy wiersz powinien zostać przekazany do operatora nadrzędnego. Znalezienie dopasowań skrótów w dobrze zrównoważonej tabeli skrótów jest niezwykle szybkie, ale zastosowanie złożonego predykatu resztkowego do każdego dopasowanego wiersza jest dość powolne w porównaniu. Etykieta narzędzia Dopasuj skrót w Eksploratorze planów pokazuje szczegóły, w tym wyrażenie resztkowe sondy:
Predykat rezydualny jest złożony i obejmuje sprawdzenie statusu postępu rezerwacji teraz, gdy kolumna jest dostępna z tabeli rezerwacji. Etykietka pokazuje również tę samą rozbieżność między szacunkową a rzeczywistą liczbą wierszy widoczną wcześniej podczas wyszukiwania indeksu. Może wydawać się dziwne, że większość filtrowania jest wykonywana dwukrotnie, ale jest to optymizm optymistyczny. Nie oczekuje części filtra, które można zepchnąć w dół planu z resztkowej sondy, aby wyeliminować jakiekolwiek rzędy (szacunki liczby wierszy są takie same przed filtrem i po nim), ale optymalizator wie, że może się to mylić. Szansa na wcześniejsze filtrowanie wierszy (zmniejszenie kosztu łączenia mieszającego) jest warta niewielkiego kosztu dodatkowego filtra. Cały filtr nie może zostać zepchnięty w dół, ponieważ zawiera test na kolumnie z tabeli rezerwacji, ale większość z nich może być.
Niedoszacowanie liczby wierszy jest problemem dla operatora dopasowania mieszania, ponieważ ilość pamięci zarezerwowanej dla tabeli skrótów zależy od szacunkowej liczby wierszy. Tam, gdzie pamięć jest zbyt mała, aby zmieścić się w wymaganym czasie wykonywania tabeli skrótów (z powodu większej liczby wierszy), tabela skrótów rekurencyjnie przelewa się do fizycznej pamięci tempdb , co często powoduje bardzo niską wydajność. W najgorszym przypadku silnik wykonawczy przestaje rekurencyjnie wylewać segmenty mieszania i działa bardzo wolnoalgorytm ratowania. Rozlewanie skrótu (rekurencyjne lub ratunkowe) jest najbardziej prawdopodobną przyczyną problemów z wydajnością przedstawionych w pytaniu (nie kolumn łączenia znaków lub konwersji niejawnych). Główną przyczyną byłoby rezerwowanie przez serwer zbyt małej pamięci dla zapytania na podstawie niepoprawnego oszacowania liczby wierszy (liczności).
Niestety przed SQL Server 2012 nic nie wskazuje w planie wykonania, że operacja haszująca przekroczyła przydzieloną pamięć (która nie może dynamicznie rosnąć po zarezerwowaniu przed rozpoczęciem wykonywania, nawet jeśli serwer ma mnóstwo wolnej pamięci) i musiała się rozlewać tempdb. Możliwe jest monitorowanie Klasy zdarzeń ostrzegawczych mieszania za pomocą programu Profiler, ale korelowanie ostrzeżeń z konkretnym zapytaniem może być trudne.
Rozwiązywanie problemów
Te trzy problemy to fragmentacja, resztkowa złożona sonda w operatorze dopasowania mieszania i niepoprawne oszacowanie liczności wynikające z zgadywania przy wyszukiwaniu indeksu.
Zalecane rozwiązanie
Sprawdź fragmentację i popraw ją, jeśli to konieczne, planując konserwację, aby indeks był dobrze zorganizowany. Zwykłym sposobem na poprawienie oszacowania liczności jest dostarczenie statystyk. W takim przypadku optymalizator potrzebuje statystyk dla kombinacji ( product_code_v42, bitfield_v41 & 4 = 0). Nie możemy tworzyć statystyk dotyczących wyrażenia bezpośrednio, dlatego najpierw musimy utworzyć kolumnę obliczeniową dla wyrażenia pola bitowego, a następnie utworzyć ręczne statystyki wielokolumnowe:
ALTER TABLE dbo.tblItemTran
ADD Bit3 AS bit_field_v41 & CONVERT(tinyint, 4);
CREATE STATISTICS [stats dbo.ItemTran (product_code_v42, Bit3)]
ON dbo.tblItemTran (product_code_v42, Bit3);
Obliczona kolumna tekstowa musi być dokładnie dopasowana do tekstu w definicji widoku, aby można było użyć statystyk, więc poprawianie widoku w celu wyeliminowania niejawnej konwersji powinno być wykonane w tym samym czasie i należy dołożyć starań, aby zapewnić dopasowanie tekstowe.
Wielokolumnowe statystyki powinny dać znacznie lepsze oszacowania, znacznie zmniejszając szansę, że operator dopasowania mieszającego użyje rekurencyjnego rozlewania lub algorytmu ratowania. Dodanie kolumny obliczeniowej (która jest operacją tylko na metadanych i nie zajmuje miejsca w tabeli, ponieważ nie jest zaznaczona PERSISTED), a statystyki wielokolumnowe to moje najlepsze przypuszczenie przy pierwszym rozwiązaniu.
Podczas rozwiązywania problemów z wydajnością zapytań ważne jest, aby mierzyć takie rzeczy, jak upływ czasu, zużycie procesora, odczyty logiczne, odczyty fizyczne, typy i czasy oczekiwania ... i tak dalej. Przydatne może być również uruchomienie części zapytania osobno, aby sprawdzić podejrzane przyczyny, jak pokazano powyżej.
W niektórych środowiskach, w których aktualny widok danych nie jest ważny, przydatne może być uruchamianie procesu w tle, który od czasu do czasu materializuje cały widok w tabeli migawek. Ta tabela jest zwykłą tabelą podstawową i może być indeksowana pod kątem zapytań odczytu, nie martwiąc się o wpływ na wydajność aktualizacji.
Wyświetl indeksowanie
Nie ulegaj pokusie bezpośredniego indeksowania oryginalnego widoku. Wydajność odczytu będzie zadziwiająco szybka (pojedyncze wyszukiwanie w indeksie widoków), ale (w tym przypadku) wszystkie problemy z wydajnością w istniejących planach zapytań zostaną przeniesione do zapytań, które modyfikują dowolną kolumnę tabeli, do której odwołuje się widok. Na zapytania, które zmieniają wiersze tabeli podstawowej, będzie to miało bardzo duży wpływ.
Zaawansowane rozwiązanie z częściowym widokiem indeksowanym
Dla tego konkretnego zapytania istnieje rozwiązanie z częściowym widokiem indeksowanym, które koryguje szacunki liczności i usuwa resztki filtra i sondy, ale opiera się na pewnych założeniach dotyczących danych (głównie zgaduję na podstawie schematu) i wymaga specjalistycznej implementacji, szczególnie w odniesieniu do odpowiednich indeksy do obsługi planów konserwacji widoków indeksowanych. Dzielę poniższy kod w celach informacyjnych, nie proponuję go wdrożyć bez bardzo starannej analizy i testowania.
-- Indexed view to optimize the main view
CREATE VIEW dbo.V1
WITH SCHEMABINDING
AS
SELECT
it.ID,
it.product_code_v42,
it.trans_type_v41,
it.booking_no_v32,
it.Trans_qty,
it.QtyReturned,
it.QtyCheckedOut,
it.QtyReserved,
it.bit_field_v41,
it.prep_on,
it.From_locn,
it.Trans_to_locn,
it.PDate,
it.FirstDate,
it.PTimeH,
it.PTimeM,
it.RetnDate,
it.BookDate,
it.TimeBookedH,
it.TimeBookedM,
it.TimeBookedS,
it.del_time_hour,
it.del_time_min,
it.return_to_locn,
it.return_time_hour,
it.return_time_min,
it.AssignTo,
it.AssignType,
it.InRack
FROM dbo.tblItemTran AS it
JOIN dbo.tblBookings AS tb ON
tb.booking_no = it.booking_no_v32
WHERE
(
it.trans_type_v41 NOT IN (2, 3, 7, 18, 19, 20, 21, 12, 13, 22)
AND it.trans_type_v41 NOT IN (6, 7)
AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
)
OR
(
it.trans_type_v41 NOT IN (6, 7)
AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
AND tb.BookingProgressStatus = 1
)
OR
(
it.trans_type_v41 IN (6, 7)
AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
AND it.QtyCheckedOut = 0
)
OR
(
it.trans_type_v41 IN (6, 7)
AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
AND it.QtyCheckedOut > 0
AND it.trans_qty - (it.QtyCheckedOut - it.QtyReturned) > 0
);
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.V1 (product_code_v42, ID);
GO
Istniejący widok poprawiono, aby użyć powyższego widoku indeksowanego:
CREATE VIEW [dbo].[vwReallySlowView2]
AS
SELECT
I.booking_no_v32 AS bkno,
I.trans_type_v41 AS trantype,
B.Assigned_to_v61 AS Assignbk,
B.order_date AS dateo,
B.HourBooked AS HBooked,
B.MinBooked AS MBooked,
B.SecBooked AS SBooked,
I.prep_on AS Pon,
I.From_locn AS Flocn,
I.Trans_to_locn AS TTlocn,
CASE I.prep_on
WHEN 'Y' THEN I.PDate
ELSE I.FirstDate
END AS PrDate,
I.PTimeH AS PrTimeH,
I.PTimeM AS PrTimeM,
CASE
WHEN I.RetnDate < I.FirstDate
THEN I.FirstDate
ELSE I.RetnDate
END AS RDatev,
I.bit_field_v41 AS bitField,
I.FirstDate AS FDatev,
I.BookDate AS DBooked,
I.TimeBookedH AS TBookH,
I.TimeBookedM AS TBookM,
I.TimeBookedS AS TBookS,
I.del_time_hour AS dth,
I.del_time_min AS dtm,
I.return_to_locn AS rtlocn,
I.return_time_hour AS rth,
I.return_time_min AS rtm,
CASE
WHEN
I.Trans_type_v41 IN (6, 7)
AND I.Trans_qty < I.QtyCheckedOut
THEN 0
WHEN
I.Trans_type_v41 IN (6, 7)
AND I.Trans_qty >= I.QtyCheckedOut
THEN I.Trans_Qty - I.QtyCheckedOut
ELSE
I.trans_qty
END AS trqty,
CASE
WHEN I.Trans_type_v41 IN (6, 7)
THEN 0
ELSE I.QtyCheckedOut
END AS MyQtycheckedout,
CASE
WHEN I.Trans_type_v41 IN (6, 7)
THEN 0
ELSE I.QtyReturned
END AS retqty,
I.ID,
B.BookingProgressStatus AS bkProg,
I.product_code_v42,
I.return_to_locn,
I.AssignTo,
I.AssignType,
I.QtyReserved,
B.DeprepOn,
CASE B.DeprepOn
WHEN 1 THEN B.DeprepDateTime
ELSE I.RetnDate
END AS DeprepDateTime,
I.InRack
FROM dbo.V1 AS I WITH (NOEXPAND)
JOIN dbo.tblbookings AS B ON
B.booking_no = I.booking_no_v32
JOIN dbo.tblInvmas AS M ON
I.product_code_v42 = M.product_code;
Przykładowy plan zapytania i wykonania:
SELECT
vrsv.*
FROM dbo.vwReallySlowView2 AS vrsv
WHERE vrsv.product_code_v42 = 'M10BOLT';
W nowym planie dopasowanie hash nie ma resztkowego predykatu , nie ma złożonego filtru , żadnego resztkowego predykatu podczas wyszukiwania widoku indeksowanego, a szacunki liczności są dokładnie poprawne.
Jako przykład wpływu na wstawianie / aktualizowanie / usuwanie planów, oto plan wstawiania do tabeli ItemTrans:
Podświetlona sekcja jest nowa i wymagana do konserwacji widoku indeksowanego. Bufor tabeli odtwarza wstawione wiersze tabeli podstawowej w celu konserwacji widoku indeksowanego. Każdy wiersz jest łączony z tabelą rezerwacji za pomocą wyszukiwania klastrowanego indeksu, a następnie filtr stosuje WHEREpredykaty klauzuli złożonej , aby sprawdzić, czy wiersz należy dodać do widoku. Jeśli tak, wstawianie jest wykonywane do indeksu klastrowego widoku.
Ten sam SELECT * FROM viewtest, który wykonano wcześniej, zakończono w 150 ms z założonym widokiem indeksowanym.
Ostatnia rzecz: zauważam, że twój serwer 2008 R2 nadal jest w RTM. Nie rozwiąże to problemów z wydajnością, ale dodatek Service Pack 2 dla 2008 R2 jest dostępny od lipca 2012 r. Istnieje wiele dobrych powodów, aby utrzymywać jak najświeższą wersję dodatków Service Pack.
