Wraz z SQL Server 2017 wprowadzono szereg ulepszeń w przetwarzaniu zapytań. Jedną z najbardziej spektakularnych jest bez wątpienia tzw. Adaptive Query Processing czyli tłumacząc na nasz język możliwość dostosowywania przez optymalizator sposobu wykonania zapytań w trakcie przetwarzania. Adepci optymalizacji zapytań powinni w tej chwili się zastanowić i zadać pytanie czy to w ogóle możliwe? Przecież to całkowicie zmienia schemat działania optymalizatora! Odpowiedź jest jednoznaczna i brzmi: tak można – od SQL Server 2017. Już na samym wstępie chciałbym zaznaczyć, że nie oznacza to, iż teraz optymalizator będzie zmieniał plan wykonania po każdym operatorze, a jedynie dostosowywał odpowiednie elementy planu w sprzyjających warunkach.
Cały mechanizm AQP składa się z trzech głównych elementów czy też mechanizmów:
- Interleaved Execution
- Adaptive Join
- Memory Grant Feedback
W ramach niniejszego artykułu przedstawimy pierwszy z powyżej wymienionych czyli Interleaved Execution, w kolejnych dwóch artykułach omówimy kolejne dwa tworząc swoistego rodzaju trylogię.
Jednym z najbardziej skomplikowanych mechanizmów wewnątrz SQL Server jest bez wątpienia optymalizator zapytań. Jak dobrze wiemy optymalizator jest oparty o koszt tzn. na podstawie licznych przesłanek takich jak np. statystyki stara się znaleźć najmniej kosztowny plan. Przy czym słowo “najmniej” nie jest do końca prawdziwe gdyż optymalizator poszukuje tzw. “good enough plan”czyli plan wystarczająco dobry (wcale nie musi być najlepszy), który został wygenerowany w skończonym czasie. Dlatego też bardzo często we właściwości planu o nazwie Reason for Early Termination znajdziemy informację: Good Enough Plan Found:
Mimo całego swojego skomplikowania i zaplecza matematyczno – statystycznego nie w każdej możliwej sytuacji optymalizator jest w stanie oszacować liczbę wierszy. Powodów takiego stanu rzeczy jest wiele np. nieaktualne statystyki lub ich brak. Innym powodem może być brak możliwości estymacji ze względu na logikę zaimplementowaną w niektórych obiektach stworzonych w SQL Server. Jakie to obiekty? Koronnym przykładem mogą tu być tu funkcje użytkownika zwane “Multi – statement table valued function” (MSTVF). Funkcje te dają naprawdę ogromne możliwości pod kątem implementacji logiki biznesowej jednakże dla optymalizatora szacowanie chociażby liczebności wierszy zwracanych przez te funkcje jest wręcz niemożliwe, właśnie ze względu na tą “customową” logikę wewnątrz funkcji. Jak zatem do tej pory radził sobie SQL Server? Zobrazujmy to na przykładzie.
Do poniższych przykładów użyjemy bazy AdventureWorksDW2017. W pierwszym kroku stwórzmy funkcję typu MSTVF:
CREATE OR ALTER FUNCTION dbo.TopSalesByProductAndCustomer(@TopN int) RETURNS @result TABLE(Product nvarchar(40), Customer nvarchar(100), SalesAmount decimal(18,2),ProductSubcategoryKey int) AS BEGIN INSERT @result(Product, Customer,SalesAmount,ProductSubcategoryKey) SELECT TOP (@TopN) EnglishProductName AS Product ,LastName +' '+FirstName AS Customer ,ProductSubcategoryKey ,SUM(SalesAmount) AS SalesAmount FROM dbo.FactInternetSales AS F JOIN dbo.DimProduct AS DP ON DP.ProductKey=F.PRoductKey JOIN dbo.DimCustomer AS DC ON DC.CustomerKey=F.CustomerKey GROUP BY EnglishProductName ,LastName +' '+FirstName ,ProductSubcategoryKey ORDER BY SalesAmount DESC RETURN; END
Funkcja ta ma na celu zwrócić zestawienie transakcji o największej wartości sprzedaży w połączeniu z danymi o kliencie, produkcie oraz identyfikatorze podkategorii produktu. Cały szkopuł polega na tym, że użytkownik może podać parametr ile największych transakcji ma zostać wyświetlone. W tym przypadku może to być zarówno 1 jak i 10 milionów. Sprawdźmy jak poradzi sobie z tym problemem optymalizator, funkcje typu MSTVF odpytujemy tak jak tabele w klauzuli FROM dlatego pozwoliłem sobie dorzucić złączenie z tabelą DimProductSubcategory i DimProductCategory:
SELECT F.Product ,F.Customer ,F.SalesAmount ,PS.EnglishProductSubcategoryName AS ProductSubCategory ,PC.EnglishProductCategoryName AS ProductCategory FROM dbo.TopSalesByProductAndCustomer(50000) AS F LEFT JOIN DimProductSubcategory AS PS ON PS.ProductSubcategoryKey=F.ProductSubcategorykey LEFT JOIN DimProductCategory AS PC ON PC.ProductCategoryKey=PS.ProductCategorykey ORDER BY SalesAmount OPTION (USE HINT( 'FORCE_LEGACY_CARDINALITY_ESTIMATION') )
Użyłem hinta FORCE_LEGACY_CARDINALITY_ESTIMATION aby użyć starego Cardinality Estymatora (przepraszam za użycie polsko-angielskiej nazwy ale nie chcę sztucznie spolszczać tejże nazwy), przypomnijmy, że nowy CE został wprowadzony w SQL Server 2014. Jaki jest wynik powyższego zapytania? Sam rezultat nie jest interesujący ale dosyć ciekawie prezentuje się plan wykonania, w pierwszym kroku spójrzmy na aktualne liczby wierszy:
Na pierwszy rzut oka widzimy kilka czynników, którym należy się przyjrzeć tj.:
- czerwony kolor na liczebnościach co oznacz nic innego jak “rozjazd” pomiędzy szacunkiem, a rzeczywistą liczbą wierszy ( nomenklatura Plan Explorera)
- Występowanie operatora Sort stanowiącego znaczny koszt całego zapytania – czy uporządkowany rezultat jest potrzebny? może warto pomyśleć o indeksie pokrywającym również sortowanie?
- Sort warning – zbyt mały przydział pamięci do tego aby uporządkować wiersze
- Nested Loops – czy aby na pewno złączenie tego typu jest odpowiednie dla takiej liczby wierszy?
No to teraz spójrzmy na estymowany plan – powinno nam to wyjaśnić kilka spraw:
Tak, dobrze widzicie estymowana liczba wierszy dla MSTVF to dla starego optymalizatora 1! Nie jest to losowa liczba, po prostu dla każdej funkcji tego typu estymator przyjmie, że zwraca ona jeden wiersz. To teraz już wiemy skąd się wzięły akurat te operatory złączenia i ostrzeżenie na Sort. Spróbujmy teraz uruchomić to samo zapytanie tylko przy pomocy nowego Cardinality Estymatora, nie chcę na ten moment pokazywać rezultatu używając mechanizmów SQL Server 2017 dlatego zmienię przy okazji tryb zgodności na odpowiadający SQL Server 2016:
ALTER DATABASE AdventureworksDW2017 set compatibility_level = 130; go
Estymowany plan wygląda następująco:
Nowy estymator uznaje, że MSTVF zwraca 100 wierszy. Nadal jest to sztywna liczba, która niestety nijak się ma do rzeczywistości i której z poziomu kodu zmienić nie możemy (żadne wymuszenie rekompilacji niestety nie pomoże bo nie ma to nic wspólnego z mechanizmem parameter sniffing). Co więc w takiej sytuacji możemy zrobić? Mamy kilka wyjść:
- jeśli obniżona wydajność nam nie przeszkadza to nie mamy problemu,
- spróbować przepisać logikę z funkcji MSTVF na funkcję inline (parametryzowany widok) lub prodedurę składowaną,
- zbudować zapytanie w aplikacji klienckiej i wysyłać zapytanie adhoc,
- zrobić upgrade do SQL Server 2017.
Ta ostatnia opcja wiąże się z wspomnianym wcześniej mechanizmem o nazwie Interleaved Execution. Do tej pory wykonanie zapytania składało się w uproszczeniu z dwóch faz:
Podczas fazy optymalizacji na podstawie dostępnych danych powstawał plan wykonania będący algorytmem prowadzącym do zwrócenia określonego rezultatu. Na podstawie powstałego planu realizowane jest rzeczywiste wykonanie. W tym modelu podczas samego wykonania nie ma już możliwości zastosowania jakichkolwiek poprawek. Nie zmieniają tego faktu żadne hinty użyte przez nas w zapytaniu, warto zdawać sobie sprawę, że modyfikują one jedynie działanie optymalizatora, a nie samą fazę wykonania. Tak właśnie działa SQL Server wszędzie tam gdzie nie ma mechanizmów Adaptive Query Processing. Teraz spójrzmy na schemat działania SQL Server w przypadku Interleaved Execution:
Dzięki Interleaved Execution optymalizator jest w stanie dostosować plan w trakcie wykonania. Jak to wygląda? W przypadku MSTVF jest ona po prostu wykonywana jako pierwsza i tuż po jej wykonaniu znana jest oczywiście liczba zwróconych przez nią wierszy. Z tą właśnie informacją możliwa jest dalsza optymalizacja i na tym właściwie polega cały sekret.
Zmieńmy tryb zgodności znów na 140:
ALTER DATABASE AdventureworksDW2017 set compatibility_level = 130; GO
Po uruchomieniu zapytania plan przedstawia się następująco (jest to plan estymowany):
Jak możecie zauważyć estymowana liczba wierszy zwróconych przez funkcję jest dokładnie taka jak oczekiwaliśmy. Dzięki tej informacji do złączenia został użyty Hash Match zamiast Nested Loop co spowodowało zwiększenie wydajności (o tym za chwilę). Widzimy jednak, że szacunki po złączeniu nie były już takie dokładne i stwierdzenie “im dalej w plan tym większe rozbieżności w szacunkach” jest nadal prawdziwe. Przykład ten przedstawiłem po to aby powiedzieć wprost, że to nie jest tak, że po migracji na SQL Server 2017 znikną wszelkie problemy wydajnościowe.
Jeśli chodzi o samą wydajność to wygląda to tak:
Bez Interleaved execution:
Z Interleaved Execution:
Całkiem imponujący wynik prawda? Wiąże się on głównie z algorytmem złączenia dobranym do zapytania.
Zmierzając już do końca chciałbym Wam pokazać sesję Extended Events gdzie dosyć fajnie można zobaczyć to jak działa IE. Kod do wygenerowania tej prostej sesji wygląda następująco:
CREATE EVENT SESSION [AQP_IE] ON SERVER ADD EVENT sqlserver.sp_statement_starting( ACTION(sqlserver.sql_text) WHERE ([sqlserver].[database_name]=N'AdventureWorksDW2017')), ADD EVENT sqlserver.sql_statement_starting( ACTION(sqlserver.sql_text) WHERE ([sqlserver].[database_name]=N'AdventureWorksDW2017')) GO
Po uruchomieniu tejże sesji i wykonaniu naszego testowego zapytania możemy dostrzec następującą kolejność wykonania:
Powyższe zrzuty ekranowe przedstawiają sytuację gdzie w pierwszej kolejności dane zostały zostały wstawione do zmiennej, która to następnie została zwrócona jako rezultat by zaraz potem wykonane zostało całe zapytanie poza funkcją. Sam mechanizm możemy śledzić używając Extended Events i nowych zdarzeń których listę możecie znaleźć w dokumentacji.
Oczywiście nie jest tak, że opisywany mechanizm rozwiązuje wszelkie problemy związane z MSTVF. Na moment pisania niniejszego artykułu nie działa on w przypadku wywołania funkcji, które jako parametr przyjmują niestały parametr. Funkcje tego typu bardzo często są wywołane przy pomocy CROSS APPLY, dla przykładu poniższa funkcja zwraca TOP 1000 sprzedaży produktów z kategorii podanej jako parametr:
CREATE OR ALTER FUNCTION [dbo].[TopProductsByCategory](@ProductCategoryKey int) RETURNS @result TABLE(RN int, Category nvarchar(40), Product nvarchar(100), SalesAmount decimal(18,2)) AS BEGIN INSERT @result(RN, Category,Product,SalesAmount) SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY SUM(SalesAmount) DESC) AS RN ,DPC.EnglishProductCategoryName AS Category ,DP.EnglishProductName AS Product ,SUM(SalesAmount) AS SalesAmount FROM dbo.FactInternetSales AS F JOIN dbo.DimProduct AS DP ON DP.ProductKey=F.PRoductKey JOIN dbo.DimProductSubcategory AS DPS ON DPS.ProductsubcategoryKey=DP.ProductSubcategoryKey JOIN dbo.DimProductCategory AS DPC ON DPC.ProductCategoryKey=DPS.ProductCategoryKey WHERE DPC.ProductCategoryKey =@ProductCategoryKey GROUP BY DPC.EnglishProductCategoryName ,DP.EnglishProductName ORDER BY SalesAmount DESC RETURN; END GO
Po standardowym wywołaniu tejże funkcji możemy zobaczyć, że zapytanie zostało wywołane z użyciem IE:
SELECT TPC.* FROM [dbo].[TopProductsByCategory](1) AS TPC ORDER BY RN ASC,Category ASC OPTION (RECOMPIlE)
Na poniższym planie możemy zauważyć, że w rzeczywistości liczba wierszy zwracana przez funkcję została dostosowana:
Co jednak gdy jako parametr funkcji przekażemy wiersze z innej tabeli:
SELECT TPC.* FROM dbo.DimProductCategory AS DPC CROSS APPLY [dbo].[TopProductsByCategory](DPC.ProductCategoryKey) AS TPC ORDER BY RN ASC,Category ASC OPTION (RECOMPIlE)
Plan wygląda nieco inaczej i zapytanie zostało wykonane “w tradycyjny sposób” ze sztywną liczbą estymowanych 100 wierszy (na planie możemy zobaczyć 400 ponieważ pętla Nested Loops odpytała funkcję 4 razy):
Podsumowując warto zdawać sobie sprawę, że Interleaved Execution nie jest rozwiązaniem na wszystkie problemy jednakże w niektórych przypadkach może znacząco poprawić wydajność. Mimo, że nie powinna ona mieć negatywnego wpływu na zapytania to możemy ją również wyłączyć używając ustawienia na poziomie bazy:
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;
lub używając hinta na poziomie zapytania o nazwie DISABLE_INTERLEAVED_EXECUTION_TVF. To by było na tyle jeśli chodzi o Interleaved Execution czyli jednego z trzech mechanizmów wchodzących w skład Adaptive Query Processing w SQL Server 2017 i wyżej oraz Azure SQL Database. Mechanizm bardzo fajny i użyteczny aczkolwiek tak jak wszystko ma swoje ograniczenia. W przyszłości opiszemy również pozostałe mechanizmy AQP na ten moment mam nadzieję, że niniejszy artykuł pozwolił Wam zrozumieć parę ciekawych aspektów pracy z SQL Server.
- Avoiding Issues: Monitoring Query Pushdowns in Databricks Federated Queries - October 27, 2024
- Microsoft Fabric: Using Workspace Identity for Authentication - September 25, 2024
- Executing SQL queries from Azure DevOps using Service Connection credentials - August 28, 2024
Last comments