SQL Server 2012 i nie tylko: wstęp do planów

SQL SERVER 2012 i
nie tylko:
Wstęp do planów zapytań
Cezary Ołtuszyk
coltuszyk.wordpress.com
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Kilka słów o mnie

Starszy Administrator Baz Danych w firmie
BEST S.A. (Bazy danych > 1TB)

Konsultant z zakresu SQL Server
(tuning, troubleshooting, wirtualizacja)

Posiadacz certyfikatów MCITP/MCTS z dziedziny
SQL Server, HYPER-V, Windows Server …

Prelegent na konferencjach informatycznych i
spotkaniach grup pasjonackich

Autor artykułów i blogger
(wss.pl, coltuszyk.wordpress.com)
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Plan spotkania
I.
Wprowadzenie do tematu (mierzenie wydajności)
II.
Czytanie i wybieranie danych
III.
Łączenie tabel
IV.
Grupowanie i sortowanie wartości
V.
Podsumowanie
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Wprowadzenie do tematu (mierzenie wydajnosci)
Jednym często zadawanych pytań podczas
tworzenia
oprogramowania
jest
następujące zdanie:
„Dlaczego moje zapytanie trwa tak długo?”
Niestety nie
odpowiedzi …
ma
na
nie
uniwersalnej
Są natomiast narzędzia, które pozwalają
nam znaleźć odpowiedź dla danego
przypadku.
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Wprowadzenie do tematu (mierzenie wydajności)
Zanim
zaczniemy
poprawiać
nasze
zapytania, powinniśmy wiedzieć jak można
mierzyć ich wydajność:

Czas wykonania zapytań SQL

Ilość odczytanych danych

Czas zajętości procesora

Plan wykonania instrukcji
Plany wykonania są zarówno miarą jak i
wyjaśnieniem zagadki związanej z wydajnością 
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Czytanie danych
Podstawową jednostką zapisu i odczytu w SQL Server jest strona,
która zawiera 8KB danych. Czytając dane z tabeli, tak naprawdę
(fizycznie) czytamy 8KB strony.
Data Pages
Page 1
Page 2
Page 3
Page 4
Con ...
Funk ...
Woods ...
...
...
...
...
Russo ...
Woods ...
Barr ...
...
...
...
...
Akers ...
Funk ...
Smith ...
Martin ...
...
...
Smith
Owen
Jones
...
...
...
...
...
...
...
Page 5
Page 6
Martin ...
Pica ...
Jones ...
Smith ...
...
...
Ganio
Jones
Hall
...
...
...
...
...
...
...
Nasze zabiegi związane z przyśpieszeniem odczytu będą wiązały się
ze zmniejszeniem ilość stron, które należy przeczytać aby wybrać
interesujące nas informacje.
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Czytanie danych
Operatory „Table Skan”, „Clustered Index Scan” i
„Index Scan” świadczą o tym, że SQL Server
czyta cały indeks lub całą tabelę w celu podania
interesujących nas wartości.
Przeciwieństwem operatorów skanujących są
operacje typu „Seek”. Operatory „Clustered Index
Seek” oraz „Index Seek” mówią nam, że silnik baz
danych wie, gdzie znajdują się odpowiednie dane.
Nowością wprowadzoną w SQL Server 2012 jest
operator „Columnstore Index Scan”. W przypadku,
gdy wybieramy dużą ilość danych z hurtowni, to
będzie to prawdopodobnie najefektywniejsza
metoda dostępu.
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Łączenie tabel
Pierwszą rzeczą jaką trzeba powiedzieć na temat
łączenia tabel jest rozróżnienie logicznego
operatora
łączenia
od
jego
fizycznej
implementacji.
Logiczne operatory łączenia to: INNER JOIN,
LEFT JOIN, RIGHT JOIN, FULL JOIN i CROSS
JOIN. Są one używane podczas tworzenia
instrukcji T-SQL.
W momencie wykonania SQL Server realizując
dane łączenie logiczne korzysta z jednego z trzech
operatorów fizycznych: „Nested Loops”, „Hash
Match”, „Merge Join”
Przykład: Programista napisał instrukcję zawierającą łączenie
logiczne LEFT JOIN, SQL Server aby podać wynik użył
operatora „Nested Loops”
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Łączenie tabel
Łączenie tabel za pomocą operatora „Nested
Loops” polega na wybraniu odpowiedniego wiersza
z tabeli A, a następnie przeszukaniu tabeli B w
celu znalezienia pasujących wierszy.
Ci z was, którzy zetknęli się z algorytmiką już się
pewnie
domyślają
jaka
jest
złożoność
obliczeniowa tego rozwiązania ( A x B) i kiedy
plan używający „Nested Loops” jest optymalny.
Przykład:
Dwie tabele po 1000 rekordów mogą dać łącznie milion porównań
podczas łączenia „Nested Loops”(1000*1000 = 1000000)
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Łączenie tabel
Algorytm łączenia za pomocą operatora „Hash
Match” wygląda następująco:
1. Pobierane są dane z tabeli A, dla których SQL
Server używa funkcji haszującej w celu
zbudowania odpowiednich kubełków
2. Pobierane są kolejne wiersze tabeli B, dla
których wyliczana jest ta sama funkcja
haszująca w celu dopasowania do odpowiedniego
kubełka (tabeli A)
3. Dane tabeli A i B są łączone wewnątrz kubełków
w celu podania wyniku
Co nam daje takie podejście?
- Mniej porównań podczas łączenia !!! (i większe zużycie procesora) 
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Łączenietabel
Łączenie tabel za pomocą operatora „Merge Join”
można porównać do działania algorytmu sortowania
przez scalanie:
0. Obie tabele muszą być posortowane !!!!
1. Pobieramy pierwszy wiersz z tabeli A i
porównujemy go z pierwszym wierszem tabeli B
2. Jeżeli porównywalne wartość jest równa, to
brane wiersz jest dodawany do wyniku
3. Jeżeli wartości są różne, to wartość mniejsza
jest odrzucana na rzecz kolejnego wiersza
danej tabeli
Główna zaleta: liniowa złożoność obliczeniowa 
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Grupowanie i sortowanie wartości
Grupowanie w SQL Server może nastąpić na dwa
sposoby:
1. Za pomocą operacji „Stream Aggregate”, która
polega na zgrupowaniu wcześniej posortowanych
wartości
2. Z użyciem funkcji haszującej („Hash Match”),
która grupuje dane do odpowiednich kubełków,
a następnie wyliczy agregację
W momencie, gdy dysponujemy już wstępnie
posortowanymi wartościami (np. pobierając dane z
odpowiednio przygotowanego indeksu), to operacja
„Stream Aggregate” jest szybsza.
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Grupowanie i sortowanie wartości
Sortowanie danych może się odbywać w sposób
„jawny”, lub też „ukryty” przed programistą:
1. Sposób „jawny” polega na użyciu specjalnego
operatora „Sort”, który poukłada rekordy
według zadanych kryteriów
2. Sposób „niejawny” to po prostu przeczytanie
danych z indeksu, który ma logiczny porządek
zgodny z naszymi wymaganiami
Zadanie:
Czy instrukcja typu „SELECT * FROM tabela” zwróci rekordy
ułożone w określonym porządku? (np. posortowane wg. klucza
głównego tabeli)
Bazując na planach zapytań można poznać odpowiedź 
SQL Server 2012 i nie tylko: wstęp do planów zapytań
Podsumowanie
 Graficzne
plany
wykonania
są
doskonałym narzędziem pozwalającym
na badanie problemów wydajnościowych
kodu SQL
 Analizując plany zapytań zwróćmy
uwagę na to, czy nie czytamy zbyt dużej
ilości niepotrzebnych danych
 Każdy z fizycznych operatorów łączenia
tabel jest OK, pod warunkiem, że
korzystamy z niego w odpowiednich
sytuacjach
 Operacje grupowania i
potrafią
bazować
na
„przygotowaniu” rekordów
sortowania
wstępnym