Strona korzysta z plików cookies w celu realizacji usług i zgodnie z Polityką Plików Cookies.



26.10.2020

Nowa wersja nVision

Można już pobierać nową wersję nVision
26.10.2020

Monitorowanie infrastruktury

Vertiv Environet Alert
23.10.2020

Telefonia w chmurze

NFON Cloudya
23.10.2020

Nowości w EDR

Bitdefender GravityZone
23.10.2020

Wykrywanie anomalii

Flowmon ADS11
23.10.2020

Mobilny monitor

AOC 16T2
22.10.2020

HP Pavilion

HP zaprezentowało nowe laptopy z linii Pavilion.
22.10.2020

Inteligentny monitoring

WD Purple SC QD101
22.10.2020

Przełącznik 2,5GbE

QNAP QSW-1105-5T

Nowe funkcje Microsoft SQL Server 2019

Data publikacji: 20-02-2020 Autor: Marcin Szeliga
Rys. 1. Plany wykonania...

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.

 

Najnowsza wersja serwera SQL rozszerza listę mechanizmów adaptacyjnego wykonywania zapytań o pięć kolejnych. Przyjrzyjmy się im bliżej.
 

> ZWROTNA INFORMACJA O PRZYDZIELONEJ PAMIĘCI


SQL Server 2017 zapisywał zwrotną informację o przydzielonej pamięci wyłącznie dla zapytań wykonywanych w trybie wsadowym, które odwoływały się do indeksów kolumnowych (więcej informacji o tym trybie wykonywania zapytań znajduje się w następnym punkcie). Najnowsza wersja serwera SQL zapisuje dane o pamięci przydzielonej wszystkim zapytaniom. Działanie tego mechanizmu jest następujące:

 

  • zapytanie jest optymalizowane i wykonywane,
  • zbuforowany plan wykonania zapytania jest aktualizowany o rzeczywistą ilość potrzebnej do jego realizacji pamięci,
  • jeżeli rzeczywiste użycie pamięci było o ponad 50% mniejsze od szacowanego, oszacowanie zostaje odpowiednio zmniejszone,
  • jeżeli serwer SQL podczas wykonywania zapytania musiał użyć bazy tempdb do przechowania tymczasowych wyników, oszacowanie zostaje odpowiednio zwiększone.

 

Informacje o użyciu pamięci są aktualizowane wyłącznie dla ostatnio użytego planu jego wykonania. Jeżeli plan wykonania zostaje usunięty z bufora (np. na skutek jego rekompilacji), informacje zostaną utracone. Żeby przekonać się o tym, jak działa opisywany mechanizm, zasymulujemy błąd oszacowania liczby wierszy, ręcznie modyfikując statystyki tabeli:


UPDATE STATISTICS Fact.OrderHistory
WITH ROWCOUNT = 1;

 

Ponieważ serwer SQL spodziewał się odczytania jednego wiersza z tabeli Fact.OrderHistory, ilość pamięci przydzielonej na potrzeby wykonania poniższego zapytania wyniosła 1 MB:

 

SELECT
fo.[Order Key], fo.Description,
si.[Lead Time Days]
FROM Fact.OrderHistory AS fo
INNER HASH JOIN Dimension.[Stock Item] AS si
ON fo.[Stock Item Key] = si.[Stock Item Key]
WHERE fo.[Lineage Key] = 9
AND si.[Lead Time Days] > 19;


W rzeczywistości odczytanych zostało 66 416 wierszy, co spowodowało zapisanie tymczasowych danych w bazie tempdb (rys. 2). Wykonując to samo zapytanie ponownie, przekonamy się, że ilość przydzielanej pamięci nadal wynosi 1 MB – w konsekwencji zapytanie wykonywane jest wolno i silnie obciąża bazę tempdb. Wystarczy jednak podnieść poziom zgodności bazy danych do wersji 2019, żeby rozwiązać problem:


ALTER DATABASE [WideWorldImportersDW]
SET COMPATIBILITY_LEVEL = 150;


Od teraz dzięki zwrotnej informacji o niewystarczającej ilości przydzielanej pamięci ponowne wykonanie zapytania według tego samego planu otrzymało 625 MB RAM, co pozwoliło złączyć wiersze w pamięci bez konieczności ich zapisywania w bazie tempdb. Jeżeli jednak chcielibyśmy wyłączyć działanie opisanego mechanizmu dla zapytania, musielibyśmy dodać do niego dyrektywę DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK. Na koniec przywrócimy prawidłową informację o liczbie wierszy tabeli Fact.OrderHistory:


UPDATE STATISTICS Fact.OrderHistory
WITH ROWCOUNT = 3702672;

 

> WYKONYWANIE ZAPYTAŃ W TRYBIE WSADOWYM


Tryb wsadowy zadebiutował, razem z indeksami kolumnowymi, w wersji 2012 serwera SQL. Pozwolił on wydajnie przetwarzać duże zbiory skompresowanych kolumnowo danych. Jednak aż do wersji 2019 serwera zapytania, które nie odwoływały się do indeksów kolumnowych były wykonywane w trybie wierszowym.


Wiemy już, że wykonanie zapytania polega na realizacji jego planu wykonania lub na kolejnym wywołaniu zapisanych w tym planie operatorów. W trybie wierszowym operator znajdujący się po lewej wywołuje funkcję GetNext poprzedzającego go operatora, w wyniku czego otrzymuje od niego kolejny wiersz, np. przetworzenie miliona wierszy wymaga wywołania funkcji GetNext milion razy.


Operator działający w trybie wsadowym zwraca w wyniku wywołania funkcji GetNext nie jeden, lecz wiele wierszy (do 900, w zależności od ich wielkości). W efekcie przetworzenie tych samych danych wymaga wykonania mniejszej liczby instrukcji, co przekłada się na zmniejszenie obciążania procesorów. Z drugiej strony wykonanie zapytania w trybie wsadowym z reguły wymaga więcej pamięci, a zmniejszenie obciążania procesorów nie zawsze oznacza skrócenie czasu wykonania zapytania.

 

[...]

 

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.
 

Pełna treść artykułu jest dostępna w papierowym wydaniu pisma.

.

Transmisje online zapewnia: StreamOnline

All rights reserved © 2019 Presscom / Miesięcznik "IT Professional"