Max Degree of Parallelism (MAXDOP)

MAXDOP to ustawienie ograniczające liczbę rdzeni wykorzystywanych przy równoległym wykonaniu pojedynczego zapytania.

Jak SQL Server wykorzystuje MAXDOP przy wykonywaniu zapytań

MAXDOP, czyli Max Degree of Parallelism, jest opcją aparatu bazodanowego Microsoft SQL Server określającą maksymalny stopień równoległości. W praktyce ogranicza liczbę procesorów logicznych, które mogą zostać wykorzystane przez równoległe operatory planu wykonania pojedynczej instrukcji. Nie oznacza to jednak, że każda instrukcja automatycznie otrzyma wskazaną liczbę rdzeni. SQL Server najpierw ocenia, czy plan równoległy będzie korzystniejszy od planu szeregowego, a następnie dobiera rzeczywisty stopień równoległości w ramach ustalonego limitu.

Równoległy plan wykonania dzieli pracę między wiele zadań. Każde zadanie może zostać przypisane do osobnego wątku roboczego i harmonogramu procesora. Warto odróżnić stopień równoległości od całkowitej liczby wątków użytych przez całe żądanie, ponieważ limit MAXDOP jest ustalany na poziomie zadania. Z tego powodu pojedyncze zapytanie może w określonych fazach angażować więcej niż jeden zestaw zadań, chociaż równoległe fragmenty planu nadal podlegają ograniczeniu konfiguracji.

Wartość zero, wartość jeden i ograniczenie liczby procesorów

Wartość MAXDOP równa 1 wyłącza równoległe plany dla danej konfiguracji, więc instrukcje są wykonywane szeregowo. Wartość większa od 1 pozwala optymalizatorowi rozważyć wykonanie równoległe, ale nie nakazuje mu użycia wszystkich dostępnych procesorów aż do wskazanego limitu. Jeżeli plan szeregowy ma niższy przewidywany koszt, zapytanie nie przekracza progu kosztu równoległości albo część operatorów nie może działać równolegle, SQL Server może nadal wykonać instrukcję z efektywnym DOP równym 1.

Wartość 0 oznacza, że SQL Server sam wybiera maksymalny stopień równoległości zgodnie z dostępnymi zasobami i obowiązującymi regułami silnika. Nie jest to równoznaczne z bezwarunkowym użyciem wszystkich rdzeni przez każde zapytanie. Przy ustalaniu wartości bierze się pod uwagę między innymi topologię NUMA, liczbę procesorów logicznych przypadających na węzeł oraz charakter obciążenia. W nowszych wersjach SQL Server, w tym od SQL Server 2019, instalator może przedstawiać zalecenie MAXDOP na podstawie konfiguracji procesorów.

MAXDOP nie zastępuje ustawienia cost threshold for parallelism. Pierwsza opcja wyznacza górną granicę równoległości, a druga wpływa na moment, od którego optymalizator zaczyna rozważać plany równoległe. Zbyt wysoki limit może zwiększać konkurencję o procesor, pamięć i przepustowość, natomiast zbyt niski może ograniczyć korzyści przy kosztownych zapytaniach analitycznych.

Zakres konfiguracji i wymuszanie ustawienia dla instrukcji

MAXDOP można ustawić na poziomie instancji za pomocą opcji konfiguracji SQL Server. Do odczytu oraz zmiany wartości służy procedura systemowa sp_configure, a zastosowanie zmiany wymaga wykonania polecenia RECONFIGURE. Zmiana tej opcji zaczyna obowiązywać bez restartowania usługi. Do modyfikacji ustawień serwera potrzebne jest uprawnienie ALTER SETTINGS albo uprawnienia wynikające z odpowiedniej roli serwerowej.

Silnik pozwala również ograniczyć równoległość na niższym poziomie. W zapytaniu można użyć wskazówki OPTION (MAXDOP 2), która dotyczy konkretnej instrukcji. Ustawienie na poziomie zapytania może nadpisać wartość konfiguracyjną instancji, ale nadal podlega ograniczeniom wynikającym z Resource Governor. MAXDOP może być także określany jako konfiguracja zakresu bazy danych, jako ustawienie grupy obciążenia Resource Governor oraz jako opcja operacji tworzenia lub przebudowy indeksu.

Ta sama koncepcja ma zastosowanie nie tylko do typowych zapytań SELECT. Konfiguracja wpływa również na wybrane operacje indeksowe, równoległe wstawianie danych, zbieranie statystyk oraz niektóre polecenia DBCC. Dlatego zmiana wartości wyłącznie na podstawie jednego wolnego zapytania może pogorszyć inne zadania. Przed korektą należy porównać plany wykonania, czas trwania, zużycie procesora, oczekiwania oraz zachowanie obciążenia w różnych porach.

Diagnostyka skutków niewłaściwego MAXDOP

Nieprawidłowo dobrany MAXDOP może objawiać się wysokim użyciem procesora, dużą liczbą oczekiwań związanych z synchronizacją wątków albo pogorszeniem czasu odpowiedzi wielu jednoczesnych zapytań. Równoległość sama w sobie nie jest błędem. Dla dużych skanów, agregacji, sortowań i części operacji indeksowych może skrócić wykonanie, lecz dla krótkich zapytań transakcyjnych narzut tworzenia i koordynowania zadań może przewyższyć zysk.

Podczas analizy należy sprawdzić rzeczywisty plan wykonania, a nie tylko tekst zapytania. Istotne są operatory Parallelism, rzeczywisty i szacowany koszt, rozkład pracy między wątkami oraz różnice między estymowaną i faktyczną liczbą wierszy. Nierówny podział danych może sprawić, że jeden wątek wykona większość pracy, mimo obecności planu równoległego. W takiej sytuacji samo zwiększenie MAXDOP zwykle nie usuwa przyczyny, którą może być nieaktualna statystyka, niewłaściwy indeks, zła estymacja kardynalności albo selektywność warunku.

Diagnostyka komputera, na którym działa SQL Server, powinna obejmować także liczbę rdzeni logicznych, obciążenie procesora, pamięć, konfigurację NUMA i inne procesy konkurujące o zasoby. W przypadku komputera stacjonarnego lub laptopa objawy spowolnienia mogą wynikać z przegrzewania, ograniczenia częstotliwości procesora, niewystarczającej ilości pamięci albo problemów z nośnikiem danych. Dopiero rozdzielenie problemu sprzętowego od ustawień silnika pozwala ocenić, czy korekta MAXDOP jest uzasadniona.