Parę faktów o funkcjach okna cz.3: Optymalizacja ROWSTORE

Po dwóch artykułach tłumaczących działanie i zastosowanie funkcji okna przyjrzyjmy się temu co się dzieje pod spodem i jak możemy zwiększyć wydajność naszych zapytań opartych o funkcje okna na strukturach opartych o ROWSTORE czyli tradycyjne tabele bez indeksów kolumnowych, których temat poruszymy w kolejnym artykule.

Dziś będziemy korzystać tradycyjnie z bazy transakcyjnej OLTP WideWorldImporters której zalety są raczej oczywiste: zawiera ona dane i indeksację typową dla obciążenia OLTP oraz jest ona darmowa i łatwa w interpretacji. Nie tracąc czasu przejdźmy do konkretnych przykładów!

Na początku spróbujmy uruchomić poniższe zapytanie:

SELECT 
   POL.LastReceiptDate,
   POL.OrderedOuters,
   ROW_NUMBER() OVER(ORDER BY POL.LastReceiptDate)
FROM  
   [Purchasing].[PurchaseOrderLines] AS POL

Jest to bardzo proste zapytanie korzystające tylko z jednej tabeli, które ma za zadnie ponumerować nam wiersze po polu LastReceiptDate. Rezultat nie jest zbytnio interesujący – sprawdźmy jak wygląda nasz plan zapytania ( dla przejrzystości wykorzystamy SQL Sentry Plan Explorer):

Koszt całego zapytania wyniósł 0.527. Na powyższym planie możemy dostrzec, że Sort jest najbardziej kosztowną operacją czego mogliśmy się domyślać jeszcze przed uruchomieniem zapytania. W tym miejscu mogę od razu napisać, że optymalizacja działania funkcji okna opiera się w dużej mierze na eliminacji tego właśnie operatora. Zobaczmy co jeszcze pokazuje nasz plan – mamy widoczne dwa operatory, które nie są dla nas interesujące z punktu widzenia optymalizacji tj. Segment oraz Sequence Project. Pierwszy z nich odpowiada za podział zestawu danych na partycje zgodnie z podanym w zapytaniu warunkiem PARTITION BY. Jak zapewne zauważyliście nasze zapytanie takowego zapisu nie posiada czyli cały zestaw jest pojedynczą partycją, jednakże najwyraźniej operator ten jest wymagany i dlatego też się pojawia. Podsumowując można powiedzieć, że Segment wskazuje wiersz w posortowanym zbiorze danych, który rozpoczyna kolejną „partycję” danych.  Pojawia się on w wielu zapytaniach – nie tylko tych powiązanych z funkcjami okna, ale o tym nie dzisiaj.

Kolejnym operatorem widocznym na naszym planie jest Sequence Project. Operator ten (w przypadku funkcji ROW_NUMBER()) oblicza coś na kształt licznika, który z każdym kolejnym wierszem jest zwiększany o 1 aż do końca partycji. Bardzo często Sequence Project pojawi się wraz z Segment, który jak już wspomniałem dostarcza istotnych informacji o końcu partycji. Operator ten pojawi się dla funkcji rankingu takich jak np. ROW_NUMBER, RANK czy DENSE_RANK – informację o tym jaka funkcja została wykonana znajdziemy we właściwościach:

Oba omówione operatory tj. Segment i Sequence Project nie są z punktu optymalizacji dla nas istotne – ważny jest dla nas Operator Sort, którego możemy pozbyć się dodając odpowiedni indeks niezgrupowany. Spróbujmy więc dodać prosty indeks:

CREATE INDEX IX_LastReceiptDate ON [Purchasing].[PurchaseOrderLines]

(LastReceiptDate)

INCLUDE (OrderedOuters)

Po ponownym uruchomieniu naszego zapytania widzimy, że kierunek sortowania indeksu + fakt, że był on pokrywający pozwolił na eliminację najbardziej kosztownego operatora:

Koszt zapytania po optymalizacji wyniósł 0.0259, a więc wielokrotnie mniej niż przed optymalizacją – cel zrealizowany! Oczywiście to tylko prosty przykład obrazujący cel optymalizacji tego typu zapytań czyli usunięcie Sort. Ogólnie rzecz biorąc strategia indeksacji pod funkcje okna często nazywana jest określeniem POC (Partitioning, Ordering, Covering) czyli jako klucze indeksu najpierw podajemy elementy tworzące partycje, następnie występujące w ORDER BY i dodajemy wszystkie pozostałe do sekcji, które sprawiają, że nasz indeks jest pokrywający. Nasz powyższy przykład był zgodny ze strategią POC – nie zawierał jedynie elementu Partitioning. Dla przykładu podam, że dla poniższego zapytania:

SELECT
       POL.LastReceiptDate,
       POL.OrderedOuters,
       ROW_NUMBER() OVER(PARTITION BY POL.StockItemID ORDER BY POL.LastReceiptDate)
FROM  
      [Purchasing].[PurchaseOrderLines] AS POL

dobry byłby następujący indeks:

CREATE INDEX IX_StockItemID_LastReceiptDate_INCL_OrderedOuters ON [Purchasing].[PurchaseOrderLines]

(StockItemID,LastReceiptDate)

INCLUDE (OrderedOuters)

Oczywiście jeśli mamy dodatkowe warunki filtrujące to możemy dodać je jako wiodący klucz indeksu lub stworzyć indeks filtrowany gdy mamy pewność, że zawsze będziemy odwoływać się do podzbioru danych tabeli np.

SELECT
         POL.LastReceiptDate,
         POL.OrderedOuters,
         ROW_NUMBER() OVER(PARTITION BY POL.StockItemID ORDER BY POL.LastReceiptDate)
FROM  
        [Purchasing].[PurchaseOrderLines] AS POL
WHERE
        POL.PackageTypeID=6

Indeks:

CREATE INDEX FIX_StockItemID_LastReceiptDate_INCL_OrderedOuters 
ON [Purchasing].[PurchaseOrderLines]
(StockItemID,LastReceiptDate)
INCLUDE (OrderedOuters)
WHERE PackageTypeID=6

Przejdźmy dalej i wykonajmy zapytanie agregujące z wykorzystaniem okna:

SELECT 
     POL.StockItemID,
     POL.LastReceiptDate,
     POL.OrderedOuters,
     SUM(POL.OrderedOuters) OVER(PARTITION BY POL.StockItemID ORDER BY POL.LastReceiptDate) as c
FROM  
    [Purchasing].[PurchaseOrderLines] AS POL

Plan zapytania wygląda następująco:

Zapytanie zostało wykonane w następujący sposób:

1. Dane zostały pobrane z tabeli operatorem Clustered Index Scan – zwrócone dane są posortowane według klucza indeksu zgrupowanego, którym jest pole nieistotne z punktu widzenia naszego zapytania
2. Operator Sort posortował dane po atrybutach StockItemID oraz LastReceiptDate
3. Pierwszy operator Segment oznaczył wiersz w którym zmienił się klucz partycji StockItemID czyli innymi słowy stworzył grupy po atrybucie StockItemID
4. Drugi operator Segment oznaczył wiersz w którym zmieniła się wartość pola LastReceiptDate tj. stworzył grupy po LastReceiptDate w ramach wcześniej stworzonych grup StockItemID. Dlaczego operator Segment występuje tutaj dwa razy? Pierwsze wystąpienie jest oczywiste, drugie z kolei musi występować ze względu na fakt, iż gdy w ramach tej samej grupy występują to samo LastReceiptDate to nasza funkcja okna musi wyliczyć tę samą wartość.
5. Window Spool tworzy tabelę na czas wykonania zapytania, która rozszerza zestaw danych o wyliczone agregaty – w tym miejscu zestaw danych zawiera dwukrotnie więcej danych niż zestaw źródłowy (można to zauważyć, na planie który zwraca dwa razy więcej wierszy). Mamy tam dane źródłowe oraz dla każdego z nich wyliczony agregat przechowywany jako osobny wiersz (tj. wiersz źródłowy + wiersz z wyliczonym agregatem)
6. Obrazek
7. Stream Aggregate agreguje zestaw danych oraz oblicza liczebność, który otrzymał od Window Spool
8. Compute Scalar zawiera prostą funkcję warunkową i sprawdza czy liczebność jest równa 0 – jeśli tak podstawia NULL jeśli nie to podstawia obliczoną przez Stream Aggregate zagregowaną wartość

W taki to właśnie sposób działa nasz plan zapytania – proste zapytanie jednakże całkiem dużo aktywności ze strony SQL Server prawda? To na co warto zwrócić uwagę w tym miejscu to fakt, że Window Spool tworzy tabelę roboczą, która może być tabelą w pamięci operacyjnej (nie mylić z memory optimized table) lub też tradycyjną wolniejszą tabelą w tempdb. Tutaj może pojawić się pytanie kiedy używana jest która?

Tabela dyskowa zostanie użyta gdy:

• opieramy się na zbiorze, którego ilość wierszy w oknie jest większa równa 10 tysięcy
• używamy słowa kluczowego RANGE
• używamy LAG i LEAD z wyrażeniem jako offset

Czy możemy sprawdzić czy zapytanie używa tabeli dyskowej czy też tabeli in-memory? Oczywiście, że tak! Mamy do dyspozycji Extended Events i wydarzenie window_spool_ondisk_warning – stwórzmy sobie taką sesję:

CREATE EVENT SESSION [WindowDiskSpools] ON SERVER

ADD EVENT sqlserver.window_spool_ondisk_warning(

    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.session_nt_username))

WITH (STARTUP_STATE=OFF)

GO

Następnie włączmy sesję i obserwujmy dane na żywo. Zgodnie z przytoczonymi ograniczeniami w momencie gdy używamy powyżej 10 tysięcy wierszy automatycznie powinniśmy otrzymać tabelę dyskową – sprawdźmy to dublując nasze testowe dane:

WITH cte
AS
(
select * from [Purchasing].[PurchaseOrderLines]
union all
select * from [Purchasing].[PurchaseOrderLines]
union all
select * from [Purchasing].[PurchaseOrderLines]
union all
select * from [Purchasing].[PurchaseOrderLines]
)
SELECT 
     POL.StockItemID,
     POL.LastReceiptDate,
     POL.OrderedOuters,
     SUM(POL.OrderedOuters) OVER(PARTITION BY POL.StockItemID ORDER BY POL.LastReceiptDate) as c
FROM  
     cte AS POL

Po uruchomieniu tego zapytania automatycznie otrzymaliśmy ostrzeżenie:

Sprawdźmy teraz czy wykonanie zapytania z RANGE automatycznie powoduje, że mamy do czynienia z tabelą dyskową:

Jak widać na powyższym zrzucie rzeczywiście RANGE powoduje zrzut na dysk! Teraz już nie musimy ślepo wierzyć zaleceniom nt. RANGE wiemy dlaczego dlaczego powinniśmy go unikać- mamy dowód.

Oprócz Extended Events możemy również użyć SET STATISTICS IO np.:

WITH cte
AS
(
select * from [Purchasing].[PurchaseOrderLines]
union all
select * from [Purchasing].[PurchaseOrderLines]
union all
select * from [Purchasing].[PurchaseOrderLines]
union all
select * from [Purchasing].[PurchaseOrderLines]
)
SELECT 
     POL.StockItemID,
     POL.LastReceiptDate,
     POL.OrderedOuters,
     SUM(POL.OrderedOuters) 
               OVER(PARTITION BY POL.StockItemID 
                       ORDER BY POL.LastReceiptDate
                          ROWS BETWEEN 9999 PRECEDING AND CURRENT ROW
) as c
FROM
cte AS POL
ORDER BY      
     POL.StockItemID,
     POL.LastReceiptDat

Powyższe zapytanie zwraca następujący rezultat statystyk:

Table 'Worktable'. Scan count 33695, logical reads 202084, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PurchaseOrderLines'. Scan count 4, logical reads 632, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

A zapytanie:

WITH cte
AS
(
select * from [Purchasing].[PurchaseOrderLines]
union all
select * from [Purchasing].[PurchaseOrderLines]
union all
select * from [Purchasing].[PurchaseOrderLines]
union all
select * from [Purchasing].[PurchaseOrderLines]
)
SELECT 
     POL.StockItemID,
     POL.LastReceiptDate,
     POL.OrderedOuters,
     SUM(POL.OrderedOuters) 
             OVER(PARTITION BY POL.StockItemID 
                    ORDER BY POL.LastReceiptDate
                        ROWS BETWEEN 9998 PRECEDING AND CURRENT ROW
) as c
FROM
cte AS POL
ORDER BY
     POL.StockItemID,
     POL.LastReceiptDate

zwraca

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PurchaseOrderLines'. Scan count 4, logical reads 632, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Jaki z tego wniosek? A no taki, że w przypadku gdy ilość odczytów na tabeli roboczej jest równa 0 przy wszystkich rodzajach odczytów to mamy do czynienia z tabelą in-memory. Gdy natomiast wartości odczytów są większe niż 0 mamy do czynienia z tabelą dyskową. Warto o tym pamiętać i jak najrzadziej używać tabel dyskowych.

Jeśli chodzi o pozostałe aspekty wydajnościowe związane z funkcjami okna to wszystkie techniki jakie znacie mają swoje znaczenie również tutaj. Pamiętajmy aby zwracać uwage przy indeksacji na funkcje okna, oraz piszmy o ile to możliwe nasze zapytania w taki sposób aby unikać dyskowych tabel tymczasowych. Do omówienia pozostał nam jeszcze plan z wykorzystaniem indeksu kolumnowego, który od wersji SQL 2016 posiada nowy operator działający w trybie batchowym – o tym powiemy sobie w ramach następnego artykułu.