Wolnoooooo
Jak pamiętacie z poprzedniego wpisu, “Kiedy najlepiej zamieścić pytanie w tagu sql-server na StackOverflow?“, najlepiej pytania jest zamieszczać w sobotę między 02:15:00 a 02:29:59 😉 I pamiętacie zapewne, że zapytanie tam podane wykonywało się u mnie ponad 2 minuty. Za wolno, trzeba to przyspieszyć! Poniżej przypomnienie kwerendy i lecimy.
;WITH RawData AS (
SELECT
p.[Id] as [PostId],
DATEDIFF(SECOND,p.[CreationDate],pFA.[CreationDate]) as [TimeElapsedInSeconds],
DATEPART(WEEKDAY,p.[CreationDate]) as [WeekdayOfQuestion],
CAST(CASE
WHEN DATEPART(MINUTE,p.[CreationDate]) < 15 THEN
TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),0,0,0,0)
WHEN DATEPART(MINUTE,p.[CreationDate]) BETWEEN 15 AND 29 THEN
TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),15,0,0,0)
WHEN DATEPART(MINUTE,p.[CreationDate]) BETWEEN 30 AND 44 THEN
TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),30,0,0,0)
ELSE
TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),45,0,0,0)
END AS TIME(0)) as [TimeOfQuestion]
FROM
[dbo].[Posts] p
CROSS APPLY
(SELECT TOP(1)
[Id],
[CreationDate]
FROM
[dbo].[Posts] pA
WHERE
p.[Id] = pA.[ParentId]
ORDER BY
pA.[CreationDate]) pFA
WHERE
p.[Tags] LIKE '%<sql-server>%'
)
SELECT DISTINCT
[WeekdayOfQuestion],
[TimeOfQuestion],
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [TimeElapsedInSeconds]) OVER (PARTITION BY [WeekdayOfQuestion],[TimeOfQuestion]) AS [Median]
FROM
[RawData]
ORDER BY
[Median];
Rzut oka na plan
Mamy zapytanie, wykonało się, zerknijmy na plan zapytania. Od razu widać, że największe rzeczy dzieją się na końcu, czyli tak naprawdę na początku, bo plany wykonują się od prawej do lewej, chociaż tak naprawdę to od lewej do prawej, więc… pogmatwana kwestia, kiedyś napiszę dlaczego oba punkty widzenia są poprawne.
Na razie nie skupimy się na tym dużym operatorze Index Scan u góry, tylko na gałęzi poniżej, bo posiada ona pewien podchwytliwy operator, który bardzo nam psuje całe zapytanie.
Jaeger… Eager Index Spool
Index Spool (Eager Spool) jest operatorem blokującym i upierdliwym. Po pierwsze, oznacza on, że SQL Server postanowił zbudować samemu indeks, nic nam o tym nie mówiąc (nie ma też podpowiedzi na zielono na górze planu), wykorzystać go w wykonaniu zapytania, a potem porzucić jak marzenia o wakacjach w 2020. Po drugie, jest on typu Eager, czyli dopóki nie otrzyma wszystkich wierszy z poprzedzającego operatora (Clustered Index Scan) nie puści pracy dalej, więc cała robota stoi w miejscu i następny po nim Sort nawet nie może się zacząć wykonywać (stąd określenie “blokujący”).
I po TRZECIE
Widzicie te czarne strzałki na pomarańczowych kółkach przy operatorach? To oznacza, że dany element planu został wykonany równolegle, czyli robota została podzielona na kilka/kilkanaście gałęzi i każda wykonywała się niezależnie. Ale czy na pewno?
Jeżeli spojrzymy na obrazek powyżej, to właściwości operatora (wyświetlić je można zaznaczając operator i wciskając F4 w SSMS) mówią nam, że rzeczywiście, ten operator był wykonany równolegle. Yay!
NIE!
Nigdy nie ufajcie ikonkom.
Gdy zajrzymy w rozkład wierszy per wątek… Wygląda to jak typowy projekt na studiach. 8 osób w grupie, jedna robi wszystko, reszta się obija ale pod wynikiem podpisują się wszyscy. Eager Index Spool wymusza na poprzedzającym operatorze pracę jednowątkową! Koniec, kropka, nie ma dyskusji, marsz do pokoju.
Farewell, Mr. Spool
Musimy się tego pozbyć. Jako, że jest to indeks tymczasowy to zapewne jesteśmy w stanie stworzyć taki indeks na stałe i wtedy zapytanie będzie z niego korzystać. Tylko jak się dowiedzieć jaki indeks został stworzony? Nie tak trudno, wystarczy najechać na operator i…
Aktualnie interesuje nas tylko fragment zaznaczony na czerwono, ale to nie oznacza, że nie ma tam jeszcze czegoś. Seek Predicate informuje nas o tym jakie kolumny wchodzą w skład klucza indeksu i do czego zostały użyte. Tutaj widzimy, że indeks został stworzony na kolumnie ParentId z tabeli Posts i posłużył do łączenia się z kolumną Id z tej samej tabeli (nasz warunek WHERE z CROSS APPLY). Na co czekać, lecimy tutaj!
CREATE INDEX [doggo] ON [dbo].[Posts]([ParentId]);
Piękny indeks na kolumnie ParentId na pewno nam pomoże. F5
Jest lepiej. Index Spool zniknoł, tzn. zaginoł na zawsze, pojawił się za to Index Seek połączony z Key Lookupem. Powróciła również równoległość, liczba wierszy została w miarę równo rozdzielona na wątki! Zapytanie wykonało się w ok. 65 sekund, więc duży zysk z ponad 2 minut, ale dalej chce się więcej. Można powiedzieć, że z zupełnego bagna przeszliśmy do etapu “ciulowo ale stabilnie”.
Key Lookup
Naszym następnym problemem jest ten nieszczęsny Key Lookup. Oznacza on nic innego jak to, że w naszym psim indeksie nie było wszystkich kolumn jakie operator potrzebował wyciągnąć, więc łączył się z indeksem klastrowym na tabeli w celu pobrania dodatkowych informacji. Jakich?
Przypomnijmy sobie nasz Index Spool. Nad wartością Seek Predicate była jeszcze jedna grupa. Output List. To nic innego jak lista kolumn, które indeks zwraca dodatkowo. Brakuje nam więc w nim CreationDate. Ale czy dodać to do klucza czy do klauzuli INCLUDE? Spójrzmy trochę w przód, mamy tam nieprzyjemny Sort (Top N Sort). Niby nic nie wadzi, ale fajnie jakby go nie było. Po czym sortujemy? Po niczym innym jak CreationDate, bo taki ORDER BY mamy w naszym CROSS APPLY. Czyli dodajemy tę kolumnę do klucza!
DROP INDEX [doggo] ON [dbo].[Posts]; CREATE INDEX [birb] ON [dbo].[Posts]([ParentId],[CreationDate]);
Ptasi indeks gotowy, F5!
Nie ma Key Lookup! Nie ma Sort! A zapytanie dalej wykonało się w ok. 65 sekund…
Do trzech razy sztuka!
Dotychczas unikaliśmy słonia w pokoju. Górnej gałęzi planu, która ma skan całej tabeli Posts i zajmuje ponad minutę. I tutaj pojawia się problem… Nasz warunek.
WHERE p.[Tags] LIKE '%<sql-server>%'
Klops, warunek zaczyna się od znaku % czyli indeks nam nie pomoże… Ale czy aby na pewno? Pamiętajmy, że SQL Server zawsze chce użyć najmniejszej kopii tabeli jaką ma dostępną i jaka posiada wszystkie wartości, które go interesują. Jednak umieszczenie kolumny Tags w kluczu indeksu nie będzie miało tutaj zbyt dużego sensu, bo i tak wykonujemy na niej operacje, więc silnik nie będzie w stanie przeskoczyć bezpośrednio do interesującej nas wartości, musi wykonać swój LIKE. Dlatego dodajmy to do klauzuli INCLUDE, która umieści naszą kolumnę tylko na poziomie liści indeksu i dzięki temu będziemy mieli najmniejszą kopię tabeli, która zawiera wszystkie dane potrzebne do wykonania naszego zapytania.
DROP INDEX [birb] ON [dbo].[Posts] CREATE INDEX [kitten] ON [dbo].[Posts]([ParentId],[CreationDate]) INCLUDE ([Tags]);
Papa ptaszku, witaj kitku! F5
<3 Miłosć, nirvana, oranżada w proszku. Całe zapytanie wykonało się w 4 sekundy (!) i mimo, że dalej mamy Index Scan, jest to już skan naszego indeksu, czyli tylko małego wycinka całej tabeli. Czyli z 2 minut i 42 sekund zeszliśmy do 4 sekund. 162 vs 4. 40x szybciej.
Oki, to pa!