Autor: Ahmad Yaseen | aktualizacja: 2018-03-30 | Komentarze (3) | powiązane: więcej > Optymalizacja zapytań
bezpłatne webinarium MSSQLTips: indeksowanie pod kątem wydajności: strategie zapytań, aby zmaksymalizować indeksy serwera SQL
niewiele rzeczy wpływa na szybkość i wydajność instrukcji SQL w SQL Server tak samo jak indeksy. Ta sesja pomoże Ci zrozumieć podstawy wydajności zapytań w odniesieniu do indeksów w Twojej produkcyjnej bazie danych. Nauczymy cię, jak określić, czy korzystasz z dobrych strategii indeksowania i jaki bezpośredni wpływ ma Twoja strategia indeksowania na zapytania.
Problem
wiem, że SQL Server może uruchamiać zapytania równolegle, ale jak mogę powiedzieć, że zapytanie korzysta z równoległego planu wykonania i jak Mogę uzyskać informacje o liczbie używanych procesorów, gdy zapytanie wykonuje się równolegle? Sprawdź tę poradę, aby dowiedzieć się, jak.
rozwiązanie
koncepcja równoległości pochodzi z podziału dużego zadania na mniejsze zadania, gdzie każde małe zadanie jest przypisane do konkretnej osoby lub procesora w przypadku zapytania SQLServer, aby wykonać część głównego zadania. Na koniec częściowy wynik każdego małego zadania zostanie połączony w jeden końcowy wynik. Możesz sobie wyobrazić, o ile szybciej można to zrobić i zysk wydajności, który można uzyskać dzięki równoległemu uruchamianiu wielu zadań szeregowych w tym samym czasie, w porównaniu z wykonywaniem jednego dużego zadania seryjnie!
przed przejściem przez poradę warto zrozumieć szereg terminów technicznych związanych z koncepcją równoległości w SQL Server:
- Scheduler jest fizycznym lub logicznym procesorem, który jest odpowiedzialny za planowanie wykonania wątków serwera SQL.
- Worker jest wątkiem, który jest związany z harmonogramem, aby utworzyć określone zadanie.
- stopień równoległości to liczba pracowników, lub liczba procesorów, które są przypisane do równoległego planu w celu wykonania zadania pracownika.
- maksymalny stopień równoległości (MAXDOP) to opcja serwera,bazy danych lub poziomu zapytań, która służy do ograniczenia liczby procesorów, z których może korzystać Plan równoległy. Domyślną wartością MAXDOP jest 0, w którym silnik serwera QL może używać wszystkich dostępnych procesorów, do 64, w zapytaniu parallelexecution. Ustawienie opcji MAXDOP na 1 uniemożliwi użycie więcej niż jednego procesora wykonującego zapytanie, co oznacza, że silnik SQL Server użyje planu serialowego do wykonania zapytania. Opcja MAXDOP może przyjmować wartość do 32767, gdzie silnik SQL Server będzie używał wszystkich dostępnych procesorów serwerowych w równoległym wykonywaniu planu, jeśli wartość MAXDOP przekroczy liczbę procesorów dostępnych na serwerze. Jeśli serwer SQL jest zainstalowany na pojedynczym serwerze processorserver, wartość MAXDOP zostanie zignorowana.
- zadanie to jest mały kawałek pracy, który jest przypisany do konkretnego pracownika.
- kontekst wykonania jest granicą, w której każda singletask działa wewnątrz.
- Parallel page Supplier jest częścią silnika SQL ServerStorage, który dystrybuuje zbiory danych żądane przez zapytanie z udziałem pracowników.
- wymiana jest komponentem, który połączy różne konteksty związane z równoległym planem zapytania, aby uzyskać końcowy wynik.
decyzja o użyciu równoległego planu do wykonania zapytania lub nie zależy od wielu czynników. Na przykład, serwer SQL powinien być zainstalowany na wieloprocesorserwerze, wymagana liczba wątków powinna być dostępna do zaspokojenia, Maksymalna liczba opcji równoległości nie jest ustawiona na 1, a koszt zapytania przekracza wcześniej skonfigurowany próg kosztów dla wartości równoległości.
celem tej wskazówki jest pobranie informacji o równoległych wątkach zapytania z rzeczywistego planu wykonania zapytania. Aby wymusić na silniku SQL Server wykonanie przesłanego zapytania za pomocą planu równoległego, ustawimy próg kosztu dla wartości równoległości na 0, aby upewnić się, że we wszystkich przypadkach koszt zapytania przekroczy próg kosztu dla wartości równoległości i zachowamy maksymalną opcję stopnia podobieństwa z domyślną wartością 0, aby umożliwić silnikowi SQL Server użycie wszystkich dostępnych procesorów podczas wykonywania naszego zapytania, które są 4 procesorami w mymachine używanymi do tego demo.
próg kosztów dla wartości równoległości można ustawić za pomocą SQL Server ManagementStudio, łącząc się z instancją SQL Server, klikając prawym przyciskiem myszy nazwę instancji i wybierając opcję Właściwości. Na zaawansowanej stronie okna Właściwości serwera przewiń w dół do sekcji równoległość, skąd możesz nadpisać domyślną wartość progu kosztu dla opcji równoległość, która wynosi 5, lub maksymalny stopień równoległości, jeśli jest to wymagane, jak pokazano na zrzucie ekranu poniżej:
możesz również nadpisać domyślną wartość progu kosztów dla równoległości przy użyciu sp_configure. Aby móc zmienić próg kosztów dla wartości równoległości, powinieneś użyć sp_configure, aby najpierw włączyć opcję Pokaż zaawansowane, jak pokazano w skrypcie poniżej:
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;GOEXEC sp_configure 'cost threshold for parallelism', 0;RECONFIGURE;GOEXEC sp_configure 'show advanced options', 0;RECONFIGURE;
Demo planu wykonywania równoległego
serwer jest teraz skonfigurowany dla wersji demonstracyjnej. Stwórzmy nową tabelę za pomocą instrukcji CREATE TABLE T-SQL, jak pokazano poniżej:
USE MSSQLTipsDemoGOCREATE TABLE ParallelDemo( ID INT IDENTITY (1,1) PRIMARY KEY, FirstName NVARCHAR (200), LastName NVARCHAR (200), PhoneNumber VARCHAR(50), BirthDate DATETIME, Address NVARCHAR(MAX) )
po utworzeniu tabeli wypełnimy ją 100 000 rekordów za pomocą poniższych instrukcji theINSERT INTO:
INSERT INTO ParallelDemo VALUES ('John','Horold','+96255889955','1987-01-08','Jordan - Amman - Mecca Street - Building 77')GO 50000 INSERT INTO ParallelDemo VALUES ('Michel','Anglo','+96255776655','1985-06-02','Jordan - Amman - Mecca Street - Building 74')GO 50000
teraz chcemy włączyć „Include Actual Execution Plan”, a następnie wykonać poniższe zapytanie SELECT:
SELECT , , , , , FROM .. WHERE Address LIKE '%AMM%' ORDER BY BirthDate desc
po zakończeniu działania możemy sprawdzić plan wykonania wygenerowany odwykonanie zapytania, zobaczysz, że silnik SQL Server decyduje się użyć planu równoległego do wykonania zapytania, ze względu na fakt, że koszt tego zapytania przekracza próg kosztów dla wartości równoległości.
poniżej widzimy, że plan wykonania jest planem równoległym, ze względu na równoległość, a także żółte kółka z dwiema strzałkami pod każdym operatorem wykonywanym z równoległością.
plan wykonania to nie tylko Wykres, zawiera cenne informacje opisujące proces wykonania przesłanego zapytania. Aby wyświetlić te informacje,kliknij prawym przyciskiem myszy wybierz węzeł w planie wykonania i wybierz opcję Właściwości. W oknie Właściwości można zobaczyć liczbę procesorów, które są używane do przetworzenia przesłanego zapytania z atrybutu stopni równoległości, w sekcji Różne pokazanej poniżej:
w tym samym oknie Informacje o równoległych wątkach zapytań są również wyświetlane w sekcji ThreadStat. W tej sekcji można sprawdzić atrybut parallel Query Branches, który pokazuje liczbę ścieżek równoległego wykonywania w ramach planu wykonywania zapytań, identyfikator węzła NUMA, liczbę równoległych wątków zarezerwowanych dla określonego NUMAnode w atrybucie ReservedThreads oraz liczbę wątków używanych podczas wykonywania zapytania w atrybucie UsedThreads.
z okna Właściwości można wywnioskować, że silnik SQL Server używał 4procesorów do wykonania zapytania. Ponadto plan wykonania zapytania ma jedną ścieżkę wykonania, w której 4 wątki są zarezerwowane i używane do wykonania przesłanego zapytania przy użyciu planu równoległego.
te same informacje można również uzyskać z planu wykonania XML, przeglądając sekcję ThreadStat, jak pokazano poniżej:
Możesz również zanurkować głębiej, aby sprawdzić wejścia/wyjścia i zasoby procesora zużywane przez eachthread. Kliknij prawym przyciskiem myszy węzeł skanowania indeksów w planie wykonania i wybierz Właściwości. W oknie wyświetlane właściwości można rozszerzyć węzeł statystyki We / Wy, aby sprawdzić liczbę odczytów logicznych i fizycznych wykonywanych przez każdy wątek podczas wykonywania zapytania. Ponadto możesz również sprawdzić liczbę wierszy pobieranych z każdego wątku, czas procesora zsumowany przez każdy wątek i wreszcie czas wykonania, który upłynął przez każdy wątek, aby wykonać swoje zadanie, jak pokazano poniżej:
wyniki są zgodne z oknem SELECT Node Properties, w którym znajdują się 4 wątki robocze, które brały udział w wykonywaniu zadań zapytań i jeden coordinatorthread, który koordynuje między wątkami roboczymi.
weź pod uwagę, że poprzednie Informacje mogą być pobrane tylko z rzeczywistego planu wykonania, który jest generowany po wykonaniu zapytania. Jeśli spróbujesz wyszukać te informacje z szacowanego planu wykonania, bez wykonania zapytania, żadne informacje nie będą wyświetlane, ponieważ zapytanie musi zostać wykonane w celu określenia liczby procesorów, które będą przypisane do zapytania z dostępnych procesorów podczas wykonywania zapytania, jak pokazano poniżej dla szacowanego planu wykonania.
uzyskiwanie większej ilości informacji o równoległości SQL Server
podczas działania zapytania możesz po prostu uruchomić polecenie sp_who2 z identyfikatorem querysession, aby uzyskać informacje o wątkach używanych do wykonania submittedquery podczas wykonywania zapytania, jak pokazano poniżej:
w planie wykonania każdy operator w tym planie miał przypisany do niego numer i harmonogram, który jest używany do jego wykonania. Informacje te mogą być pobrane przez sys.widok katalogu systemu dm_os_tasks i połączenie go z widokami katalogu systemu sys.dm_os_workersand sys.dm_exec_query_profiles, jak w poniższym skrypcie T-SQL:
SELECT OSTSK.scheduler_id, qp.node_id, qp.physical_operator_nameFROM sys.dm_os_tasks OSTSKLEFT JOIN sys.dm_os_workers OSWRK on OSTSK.worker_address=OSWRK.worker_addressLEFT JOIN sys.dm_exec_query_profiles qp on OSWRK.task_address=qp.task_addressWHERE OSTSK.session_id=58ORDER BY scheduler_id, node_id;
wykonując poprzedni skrypt podczas uruchamiania instrukcji SELECT z włączonym planem ActualExecution, wynik pokaże nam,że trzy operatory planu: Sort, Filter i clustered Index skanują z ID każdego operatora pokazanego obok. Jest to wykonywane cztery razy, przy użyciu czterech harmonogramów. Operator równoległości z ID równym 0 zostanie wykonany raz przez czwarty harmonogram, aby połączyć konteksty allexecution razem w ostatnim kroku, jak pokazano poniżej:
poniżej dodałem wartości węzłów, które odpowiadają powyższym wynikom zapytań z planem wykonania, aby pomóc w dalszym zilustrowaniu.
kolejne kroki
- sprawdź następujące zasoby:
- określanie maksymalnego stopnia równoległości w SQL Server dla zapytania.
- jakie ustawienie MAXDOP powinno być używane dla SQL Server.
- jak wymusić równoległy Plan wykonania w SQL Server 2016.
- SQL Server 2016 DBCC CHECKDB z MAXDOP
Ostatnia aktualizacja: 2018-03-30
o autorze
Ahmad Yaseen jest DBA SQL Server z licencjatem z inżynierii komputerowej, a także doświadczeniem w rozwoju. NET.Zobacz wszystkie moje porady
powiązane zasoby
- więcej porad dla programistów baz danych…
