Artykuł pochodzi z wydania: Marzec 2020
Na początku listopada ubiegłego roku miała miejsce premiera serwera SQL Server firmy Microsoft. Wersja 2019 jest reklamowana jako kompletna platforma do przetwarzania i analizowania danych każdego typu. Jedną z największych nowości jest całkowicie nowa edycja Big Data Cluster, ale oprócz tego pojawił się szereg innych ciekawych funkcji. Przyjrzyjmy się nowościom zwiększającym wydajność serwera.
Bezpłatną wersję próbną, jak również darmowe edycje Developer i Express SQL Server 2019 można pobrać pod adresem tinyurl.com/tkhajrk. Dostępne są wersje dla systemów Windows i Linux oraz dla platformy Docker. Kopię użytej w przykładach bazy danych WideWorldImportersDW można pobrać pod adresem aka.ms/wwibak. Baza ta została powiększona przez uruchomienie skryptu dostępnego pod adresem tinyurl.com/w677qjd.
> JAK SQL SERVER WYKONUJE ZAPYTANIA?
Żeby zrozumieć działanie nowych funkcji inteligentnego wykonywania zapytań serwera SQL 2019, musimy wiedzieć, w jaki sposób wykonywały zapytania wcześniejsze wersje tego serwera. Aplikacje klienckie wysyłają do serwera SQL zapytania w postaci instrukcji języka SQL, np. zapytanie o całkowitą wartość sprzedaży w poszczególnych latach może wyglądać następująco:
SELECT [Calendar Year], SUM([Total Including Tax])
FROM [Fact].[OrderHistory] AS OH
JOIN [Dimension].[Date] AS D
ON D.Date = OH.[Order Date Key]
GROUP BY [Calendar Year];
Zapytanie jest więc po prostu tekstem opisującym interesujący nas wynik. W tym wypadku serwer zwróci lata i zsumowane wartości sprzedaży. Zapytanie można wykonać na bardzo wiele różnych sposobów. Serwer może na przykład odczytać potrzebne dane z tabeli lub jej indeksu, połączyć dane przy użyciu różnych operatorów, a następnie pogrupować je na jeden z wybranych sposobów. Może też najpierw pogrupować dane i dopiero potem je połączyć. Niektóre z tych sposobów mogą być znacznie lepsze, tj. szybsze od innych.
Modułem serwera SQL odpowiedzialnym za wybór planu wykonania zapytań jest optymalizator. Ten najbardziej skomplikowany moduł całego serwera opracowuje wiele różnych planów wykonania zapytania, a następnie szacuje ich koszt i wybiera plan o najniższym koszcie wykonania. Na koszt zapytania składają się potrzebne do jego wykonania zasoby procesora, pamięci i odczyty z dysku. Optymalizator jest w stanie w krótkim czasie wygenerować i porównać koszty tysięcy różnych planów wykonania zapytania, uwzględniając bieżące obciążenie serwera. Odnaleziony przez optymalizator plan wykonania przykładowego zapytania został przedstawiony na rys. 1.
Co ważne, optymalizator musi oszacować koszt różnych planów bez wykonywania zapytania. Używa do tego statystyk opisujących rozkład wartości przechowywanych w poszczególnych kolumnach. Koszt pokazanego na rys. 1 planu wykonania zapytania wyniósł 78,8. Znaleziony plan o najniższym koszcie jest przekazywany do modułu wykonującego zapytania. Moduł wykonujący zapytania odpowiada za realizację otrzymanego planu. Dodatkowo żeby zmniejszyć liczbę kosztownych optymalizacji, zapisuje on plan w buforze. Dzięki temu następnym razem takie samo zapytanie będzie mogło zostać wykonane według wcześniej znalezionego, zbuforowanego planu. Na przykład plan widoczny na rys. 1 zajął w buforze 56 KB RAM.
Do niedawna żadne dane o rzeczywistym wykonaniu zapytania nie trafiały do serwera SQL. Na przykład jeżeli rzeczywista liczba wierszy zwrócona przez operator różniła się od oszacowanej, to serwer nie odnotowywał tego i nie reagował na ten błąd. W rezultacie jeśli optymalizator z jakichś powodów zwrócił nieoptymalny plan, to zapytanie było wielokrotnie wykonywane według złego, tj. wolniejszego planu. Sytuacja ta zmieniła się wraz z dodaniem do serwera SQL 2017 funkcji dostosowania sposobu wykonania zapytania podczas jego wykonania. Składają się na nią trzy mechanizmy:
- Adaptacyjne złączenie pozwoliło zmienić sposób łączenia tabel na podstawie rzeczywistej liczby wierszy – jeśli ich liczba przekroczy określony próg, używany jest operator Hash Match. Jeśli jest ich mniej, to serwer SQL używa operatora Nested Loops.
- Przeplatane wykonanie umożliwiło opóźnienie optymalizacji pewnych części zapytania. SQL Server najpierw wykonywał użyte w zapytaniu funkcje tabelaryczne, a następnie, dysponując już dokładną liczbą zwróconych przez te funkcje wierszy, optymalizował pozostałą część zapytania.
- Zwrotna informacja o przydzielonej pamięci pozwoliła skorygować ilość RAM-u błędnie przydzielonego do wykonania zapytania w trybie wsadowym. Realizacja planu wykonania wymaga pewnej ilości pamięci, zależnej od użytych operatorów i szacowanych wielkości przetwarzanych przez nie danych, np. realizacja planu z rys. 1 wymagała 21 256 KB pamięci. Gdyby okazało się, że realizacja planu wymaga dodatkowej pamięci, to serwer SQL automatycznie użyłby bazy tempdb, co wydłużyłoby czas wykonania zapytania. Natomiast przydzielenie większej ilości pamięci oznaczałoby jej zablokowanie na czas wykonania zapytania, co przełożyłoby się na wolniejsze realizowanie pozostałych żądań. Zwrotna informacja o przydzielonej pamięci pozwala skorygować takie błędy, bo ponowne wykonanie zapytania według tego samego planu otrzymywało skorygowaną ilość pamięci.
[…]
Marcin Szeliga
Pracownik naukowy Wyższej Szkoły Bankowej w Poznaniu Wydział Zamiejscowy w Chorzowie; jest autorem książek poświęconych analizie danych i posiada tytuł Microsoft Most Valuable Professional.
