Darmowy fragment publikacji:
Tytuł oryginału: Microsoft® SQL Server® 2014 Query Tuning Optimization
Tłumaczenie: Jakub Hubisz
ISBN: 978-83-283-1162-6
Original edition copyright © 2015 by Benjamin Nevarez.
All rights reserved.
Polish edition copyright © 2015 by HELION SA.
All rights reserved.
All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording or by any information storage retrieval system,
without permission from the Publisher.
Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej
publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną,
fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym powoduje
naruszenie praw autorskich niniejszej publikacji.
Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi ich
właścicieli.
Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje były
kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane
z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz Wydawnictwo HELION nie
ponoszą również żadnej odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji
zawartych w książce.
Projekt okładki: Studio Gravite / Olsztyn
Obarek, Pokoński, Pazdrijowski, Zaprucki
Wydawnictwo HELION
ul. Kościuszki 1c, 44-100 GLIWICE
tel. 32 231 22 19, 32 230 98 63
e-mail: helion@helion.pl
WWW: http://helion.pl (księgarnia internetowa, katalog książek)
Pliki z przykładami omawianymi w książce można znaleźć pod adresem:
ftp://ftp.helion.pl/przyklady/sql14o.zip
Drogi Czytelniku!
Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres
http://helion.pl/user/opinie/sql14o
Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję.
Printed in Poland.
• Kup książkę
• Poleć książkę
• Oceń książkę
• Księgarnia internetowa
• Lubię to! » Nasza społeczność
Spis tre(cid:258)ci
Podzi(cid:218)kowania .......................................................................................................................... 11
Wprowadzenie .......................................................................................................................... 13
Rozdzia(cid:239) 1. Wprowadzenie do optymalizacji zapyta(cid:241) .................................................................17
Architektura ............................................................................................................................... 19
Parsowanie i przypisywanie .............................................................................................. 21
Optymalizacja zapyta(cid:241) ..................................................................................................... 21
Generowanie mo(cid:285)liwych planów zapyta(cid:241) ......................................................................... 22
Okre(cid:258)lanie kosztu ka(cid:285)dego z planów ................................................................................ 23
Wykonywanie zapyta(cid:241) i przechowywanie planów ............................................................. 23
Plany wykonania ....................................................................................................................... 25
Plany graficzne ................................................................................................................ 26
XML ................................................................................................................................ 32
Plany tekstowe ................................................................................................................ 35
Dodatkowe w(cid:239)a(cid:258)ciwo(cid:258)ci planów ....................................................................................... 36
Ostrze(cid:285)enia w planach wykonania .................................................................................... 39
Pobieranie planów za pomoc(cid:200) (cid:258)ledzenia lub z magazynu planów ...................................... 44
Usuwanie planów z magazynu planów ............................................................................. 49
SET STATISTICS TIME i SET STATISTICS IO ................................................................................... 50
Podsumowanie .......................................................................................................................... 52
Rozdzia(cid:239) 2.
Rozwi(cid:200)zywanie problemów w zapytaniach ..............................................................53
DMV i DMF ............................................................................................................................... 55
sys.dm_exec_requests i sys.dm_exec_sessions ................................................................. 55
sys.dm_exec_query_stats ................................................................................................ 57
Warto(cid:258)ci statement_start_offset i statement_end_offset .................................................... 60
sql_handle i plan_handle ................................................................................................. 61
query_hash i plan_hash ................................................................................................... 62
Szukanie kosztownych zapyta(cid:241) ......................................................................................... 64
SQL Trace .................................................................................................................................. 65
Zdarzenia rozszerzone ................................................................................................................ 69
Mapowanie zdarze(cid:241) SQL Trace na zdarzenia rozszerzone .................................................. 71
Tworzenie sesji ................................................................................................................ 73
Poleć książkęKup książkę6 Microsoft SQL Server 2014. Optymalizacja zapyta(cid:241)
Rozdzia(cid:239) 3.
Rozdzia(cid:239) 4.
Data Collector ............................................................................................................................ 82
Konfiguracja .................................................................................................................... 83
Wykorzystanie Data Collectora ......................................................................................... 87
Zapytania na tabelach Data Collectora .............................................................................. 88
Podsumowanie .......................................................................................................................... 90
Optymalizator zapyta(cid:241) ...............................................................................................91
Przegl(cid:200)d .................................................................................................................................... 92
sys.dm_exec_query_optimizer_info ............................................................................................ 94
Parsowanie i przypisywanie ..................................................................................................... 101
Upraszczanie ........................................................................................................................... 104
Wykrywanie sprzeczno(cid:258)ci ............................................................................................... 105
Usuwanie z(cid:239)(cid:200)cze(cid:241) z kluczem obcym ................................................................................ 107
Plan trywialny .......................................................................................................................... 109
Regu(cid:239)y transformacji ................................................................................................................ 112
Memo ..................................................................................................................................... 122
Statystyki ................................................................................................................................. 127
Pe(cid:239)na optymalizacja ................................................................................................................. 129
Search 0 ........................................................................................................................ 131
Search 1 ........................................................................................................................ 131
Search 2 ........................................................................................................................ 133
Podsumowanie ........................................................................................................................ 135
Operatory zapyta(cid:241) ....................................................................................................137
Operatory dost(cid:218)pu do danych .................................................................................................. 138
Skanowanie ................................................................................................................... 139
Przeszukiwanie .............................................................................................................. 141
Wyszukiwanie zaznacze(cid:241) ............................................................................................... 143
Agregacje ................................................................................................................................ 147
Sortowanie i haszowanie ............................................................................................... 147
Stream Aggregate .......................................................................................................... 147
Hash Aggregate ............................................................................................................. 150
Distinct Sort ................................................................................................................... 151
Z(cid:239)(cid:200)czenia ................................................................................................................................. 152
Nested Loops Join .......................................................................................................... 153
Merge Join .................................................................................................................... 155
Hash Join ...................................................................................................................... 157
Dzia(cid:239)ania równoleg(cid:239)e ............................................................................................................... 158
Operator wymiany ......................................................................................................... 160
Ograniczenia ................................................................................................................. 166
Poleć książkęKup książkęSpis tre(cid:258)ci
7
Rozdzia(cid:239) 5.
Rozdzia(cid:239) 6.
Aktualizacje ............................................................................................................................. 167
Plany per wiersz i per indeks .......................................................................................... 169
Zabezpieczenie przed problemem Halloween .................................................................. 172
Podsumowanie ........................................................................................................................ 173
Indeksy ......................................................................................................................175
Wprowadzenie ........................................................................................................................ 176
Tworzenie indeksów ................................................................................................................ 177
Indeksy klastrowe a sterty .............................................................................................. 181
Klucz indeksu klastrowego ............................................................................................. 185
Indeksy pokrywaj(cid:200)ce ...................................................................................................... 186
Indeksy filtrowane ......................................................................................................... 187
Operacje na indeksach ............................................................................................................. 189
Database Engine Tuning Advisor .............................................................................................. 193
Optymalizacja zapyta(cid:241) i korzystanie z magazynu planów ................................................. 196
Roz(cid:239)adowanie narzutu optymalizacji na serwer testowy ................................................... 197
Brakuj(cid:200)ce indeksy .................................................................................................................... 202
Fragmentacja indeksów ........................................................................................................... 204
Nieu(cid:285)ywane indeksy ................................................................................................................ 206
Podsumowanie ........................................................................................................................ 208
Statystyki ...................................................................................................................209
Statystyki ................................................................................................................................. 210
Tworzenie i aktualizacja statystyk ................................................................................... 210
Sprawdzanie obiektów statystyk ..................................................................................... 213
G(cid:218)sto(cid:258)(cid:202) ......................................................................................................................... 216
Histogram ............................................................................................................................... 218
Nowy mechanizm szacowania kardynalno(cid:258)ci ............................................................................ 222
Przyk(cid:239)ady ....................................................................................................................... 223
Flaga 4137 .................................................................................................................... 227
B(cid:239)(cid:218)dy szacunku kardynalno(cid:258)ci .................................................................................................. 227
Statystyki inkrementacyjne ....................................................................................................... 229
Statystyki dla kolumn wyliczeniowych ....................................................................................... 232
Statystyki filtrowane ................................................................................................................. 234
Statystyki dla kluczy rosn(cid:200)cych ................................................................................................. 236
Flaga 2389 .................................................................................................................... 238
UPDATE STATISTICS z ROWCOUNT i PAGECOUNT .................................................................... 242
Statystyki na serwerach po(cid:239)(cid:200)czonych ........................................................................................ 245
Konserwacja statystyk .............................................................................................................. 246
Szacowanie kosztów ................................................................................................................ 249
Podsumowanie ........................................................................................................................ 251
Poleć książkęKup książkę8 Microsoft SQL Server 2014. Optymalizacja zapyta(cid:241)
Rozdzia(cid:239) 7.
OLTP w pami(cid:218)ci — Hekaton .....................................................................................253
Architektura ............................................................................................................................. 255
Tabele i indeksy ....................................................................................................................... 257
Tworzenie tabel Hekatona .............................................................................................. 258
Indeksy haszowe ........................................................................................................... 264
Indeksy zakresowe ......................................................................................................... 268
Przyk(cid:239)ady ....................................................................................................................... 269
Natywnie kompilowane procedury przechowywane .................................................................. 273
Tworzenie natywnie kompilowanych procedur przechowywanych ................................... 273
DLL ............................................................................................................................... 276
Ograniczenia ........................................................................................................................... 279
Narz(cid:218)dzie AMR ........................................................................................................................ 280
Podsumowanie ........................................................................................................................ 285
Rozdzia(cid:239) 8. Magazynowanie planów ..........................................................................................287
Kompilacja i rekompilacja zestawów zapyta(cid:241) ............................................................................ 288
Przegl(cid:200)danie magazynu planów ............................................................................................... 292
Jak usuwa(cid:202) plany? ......................................................................................................... 294
Parametryzacja ........................................................................................................................ 295
Autoparametryzacja ....................................................................................................... 296
Opcja optymalizacji dla zapyta(cid:241) ad hoc ........................................................................... 297
Wymuszona parametryzacja ........................................................................................... 299
Procedury przechowywane ............................................................................................. 300
Pods(cid:239)uchiwanie parametrów .................................................................................................... 302
Optymalizacja pod typowy parametr ............................................................................... 304
Optymalizacja przy ka(cid:285)dym wykonaniu ........................................................................... 305
Zmienne lokalne i podpowied(cid:283) OPTIMIZE FOR UNKNOWN .............................................. 306
Wy(cid:239)(cid:200)czanie pods(cid:239)uchiwania parametrów ........................................................................ 308
Pods(cid:239)uchiwanie parametrów i opcje SET
wp(cid:239)ywaj(cid:200)ce na powtórne wykorzystanie planów ......................................................... 309
Podsumowanie ........................................................................................................................ 315
Rozdzia(cid:239) 9.
Hurtownie danych .....................................................................................................317
Hurtownie danych ................................................................................................................... 318
Optymalizacja z(cid:239)(cid:200)czenia gwia(cid:283)dzistego ..................................................................................... 321
Indeksy magazynu kolumn ....................................................................................................... 326
Korzy(cid:258)ci wydajno(cid:258)ciowe ................................................................................................. 327
Przetwarzanie partiami ................................................................................................... 329
Tworzenie indeksów magazynu kolumn .......................................................................... 330
Podpowiedzi ................................................................................................................. 335
Podsumowanie ........................................................................................................................ 336
Poleć książkęKup książkęSpis tre(cid:258)ci
9
Rozdzia(cid:239) 10. Ograniczenia i podpowiedzi procesora zapyta(cid:241) .....................................................339
Badania nad optymalizacj(cid:200) zapyta(cid:241) .......................................................................................... 341
Kolejno(cid:258)(cid:202) z(cid:239)(cid:200)cze(cid:241) ..................................................................................................................... 341
Rozbijanie skomplikowanych zapyta(cid:241) ....................................................................................... 344
Logika OR w klauzuli WHERE ......................................................................................... 345
Z(cid:239)(cid:200)czenia i zagregowane zbiory danych .......................................................................... 347
Podpowiedzi ........................................................................................................................... 348
Kiedy korzysta(cid:202) z podpowiedzi? ...................................................................................... 349
Rodzaje podpowiedzi ..................................................................................................... 351
Z(cid:239)(cid:200)czenia ....................................................................................................................... 352
Agregacje ...................................................................................................................... 355
FORCE ORDER ............................................................................................................... 356
INDEX, FORCESCAN i FORCESEEK ................................................................................... 359
FAST N .......................................................................................................................... 361
NOEXPAND i EXPAND VIEWS ........................................................................................ 363
Wskazówki planów ........................................................................................................ 364
USE PLAN ...................................................................................................................... 366
Podsumowanie ........................................................................................................................ 368
Dodatek
(cid:189)ród(cid:239)a ........................................................................................................................369
Opracowania techniczne .......................................................................................................... 370
Artyku(cid:239)y ................................................................................................................................... 371
Prace naukowe ........................................................................................................................ 372
Ksi(cid:200)(cid:285)ki ..................................................................................................................................... 374
Skorowidz ..................................................................................................................375
Poleć książkęKup książkęPoleć książkęKup książkęRozdzia(cid:239) 1
Wprowadzenie
do optymalizacji zapyta(cid:313)
W tym rozdziale:
(cid:102) Architektura
(cid:102) Plany wykonania
(cid:102) SET STATISTICS TIME i SET STATISTICS IO
(cid:102) Podsumowanie
Poleć książkęKup książkę18 Microsoft SQL Server 2014. Optymalizacja zapyta(cid:241)
W
szyscy to prze(cid:366)yli(cid:334)my: nagle dostajesz telefon z informacj(cid:251) o awarii
aplikacji i pro(cid:334)b(cid:251) o pilne przy(cid:311)(cid:251)czenie si(cid:271) do konferencji. Po po(cid:311)(cid:251)czeniu
dowiadujesz si(cid:271), (cid:366)e aplikacja jest tak wolna, i(cid:366) firma nie jest w stanie
spe(cid:311)nia(cid:253) celów biznesowych, traci pieni(cid:251)dze i by(cid:253) mo(cid:366)e tak(cid:366)e klientów. Zazwyczaj
te(cid:366) nikt nie jest w stanie zapewni(cid:253) (cid:366)adnych dodatkowych informacji, które mog(cid:311)yby
pomóc w zlokalizowaniu problemu. Co zatem powiniene(cid:334) zrobi(cid:253)? Gdzie zacz(cid:251)(cid:253)?
A po zlokalizowaniu i naprawieniu problemu co zrobi(cid:253), aby taka sytuacja nie powtó-
rzy(cid:311)a si(cid:271) w przysz(cid:311)o(cid:334)ci?
Chocia(cid:366) awaria mo(cid:366)e powsta(cid:253) z wielu ró(cid:366)nych powodów, w(cid:311)(cid:251)czaj(cid:251)c w to problemy
ze sprz(cid:271)tem i z systemem operacyjnym, jako specjalista baz danych powiniene(cid:334) by(cid:253)
w stanie odpowiednio dostosowa(cid:253) i zoptymalizowa(cid:253) swoje bazy tak, aby(cid:334) móg(cid:311) szybko
zlokalizowa(cid:253) ewentualne problemy. Ta ksi(cid:251)(cid:366)ka zapewni Ci wiedz(cid:271) i narz(cid:271)dzia do tego
potrzebne. Skupiaj(cid:251)c si(cid:271) na wydajno(cid:334)ci bazy SQL Servera, a w szczególno(cid:334)ci na opty-
malizacji i dostosowaniu zapyta(cid:313), ksi(cid:251)(cid:366)ka ta pomo(cid:366)e Ci, po pierwsze, dzi(cid:271)ki optyma-
lizacji bazy danych unikn(cid:251)(cid:253) problemów z wydajno(cid:334)ci(cid:251), a po drugie, szybko znale(cid:364)(cid:253)
i naprawi(cid:253) problemy, które mimo wszystko mog(cid:251) wyst(cid:251)pi(cid:253).
Jednym z najlepszych sposobów na poprawienie wydajno(cid:334)ci baz danych jest nie
tylko praca z technologi(cid:251), ale równie(cid:366) zrozumienie, jak dzia(cid:311)a technologia, co mo(cid:366)na
dzi(cid:271)ki niej uzyska(cid:253), jak najlepiej j(cid:251) wykorzysta(cid:253), a tak(cid:366)e jakie s(cid:251) jej ograniczenia.
Najwa(cid:366)niejszym sk(cid:311)adnikiem bazy SQL Servera wp(cid:311)ywaj(cid:251)cym na wydajno(cid:334)(cid:253) zapyta(cid:313)
jest procesor zapyta(cid:313), który sk(cid:311)ada si(cid:271) z optymalizatora zapyta(cid:313) i silnika wykonuj(cid:251)cego.
Maj(cid:251)c idealny optymalizator zapyta(cid:313), móg(cid:311)by(cid:334) po prostu przes(cid:311)a(cid:253) dowolne zapytanie
i otrzyma(cid:253) za ka(cid:366)dym razem idealny plan wykonania. Idealny silnik wykonuj(cid:251)cy
pozwala(cid:311)by natomiast wykona(cid:253) ka(cid:366)de zapytanie w ci(cid:251)gu kilku milisekund. W rzeczy-
wisto(cid:334)ci jednak optymalizacja zapyta(cid:313) to bardzo z(cid:311)o(cid:366)ony problem, a (cid:366)aden optyma-
lizator nie znajdzie idealnego planu za ka(cid:366)dym razem — przynajmniej w rozs(cid:251)d-
nym czasie. W przypadku rozbudowanych zapyta(cid:313) optymalizator zapyta(cid:313) mo(cid:366)e
przeanalizowa(cid:253) tylko ograniczon(cid:251) licz(cid:271) planów wykonania. Nawet gdyby optymali-
zator zapyta(cid:313) móg(cid:311) przeanalizowa(cid:253) wszystkie mo(cid:366)liwe rozwi(cid:251)zania, kolejnym pro-
blemem by(cid:311)oby podj(cid:271)cie decyzji, który plan wybra(cid:253). Który b(cid:271)dzie najbardziej wydajny?
Wybór planu wi(cid:251)za(cid:311)by si(cid:271) z oszacowaniem kosztu ka(cid:366)dego z rozwi(cid:251)za(cid:313), co równie(cid:366)
jest bardzo skomplikowanym zadaniem.
Nie zrozum mnie (cid:364)le: optymalizator zapyta(cid:313) SQL Servera sprawuje si(cid:271) naprawd(cid:271)
(cid:334)wietnie i prawie za ka(cid:366)dym razem wybiera dobry plan wykonania. Musisz jednak
zrozumie(cid:253), jakie informacje nale(cid:366)y przekaza(cid:253) do optymalizatora zapyta(cid:313), aby móg(cid:311)
dobrze wykona(cid:253) swoj(cid:251) prac(cid:271) — mo(cid:366)e to wi(cid:251)za(cid:253) si(cid:271) z konieczno(cid:334)ci(cid:251) zapewnienia
odpowiednich indeksów lub statystyk, a tak(cid:366)e z konieczno(cid:334)ci(cid:251) zdefiniowania odpo-
wiednich wi(cid:271)zów integralno(cid:334)ci i dobrego projektu bazy danych. SQL Server zawiera
nawet narz(cid:271)dzia, które mog(cid:251) pomóc Ci w niektórych z tych obszarów, m.in. Database
Engine Tuning Advisor (DTA) czy funkcjonalno(cid:334)ci automatycznego tworzenia i aktu-
Poleć książkęKup książkęRozdzia(cid:239) 1. Wprowadzenie do optymalizacji zapyta(cid:241)
19
alizowania statystyk. Mo(cid:366)esz jednak zrobi(cid:253) wi(cid:271)cej, aby poprawi(cid:253) wydajno(cid:334)(cid:253) swoich baz,
szczególnie je(cid:366)eli budujesz wysoko wydajne aplikacje. Musisz te(cid:366) zrozumie(cid:253) przy-
padki, w których optymalizator zapyta(cid:313) mo(cid:366)e nie zwróci(cid:253) dobrych wyników, i dowie-
dzie(cid:253) si(cid:271), co mo(cid:366)esz wtedy zrobi(cid:253).
Aby(cid:334) wi(cid:271)c móg(cid:311) lepiej zrozumie(cid:253) technologi(cid:271), ten rozdzia(cid:311) rozpoczyna si(cid:271) od
opisu dzia(cid:311)ania optymalizatora zapyta(cid:313) bazy SQL Servera i od wprowadzenia kon-
cepcji dok(cid:311)adniej omawianych w dalszej cz(cid:271)(cid:334)ci ksi(cid:251)(cid:366)ki. Wyja(cid:334)niam cel istnienia zarówno
optymalizatora zapyta(cid:313), jak i silnika wykonuj(cid:251)cego i ich maksymalnej interakcji
z pami(cid:271)ci(cid:251) podr(cid:271)czn(cid:251) planów zapytania. W dalszej cz(cid:271)(cid:334)ci pokazuj(cid:271), jak pracowa(cid:253)
z planami wykonania, które s(cid:251) podstawowym narz(cid:271)dziem podczas pracy z proceso-
rem zapyta(cid:313).
Architektura
W sercu bazy danych SQL Servera znajduj(cid:251) si(cid:271) dwa g(cid:311)ówne komponenty: silnik prze-
chowywania i silnik relacyjny, nazywany równie(cid:366) procesorem zapyta(cid:313). Silnik prze-
chowywania jest odpowiedzialny za odczyt danych pomi(cid:271)dzy dyskiem a pami(cid:271)ci(cid:251)
w sposób optymalizuj(cid:251)cy wspó(cid:311)bie(cid:366)no(cid:334)(cid:253) i pozwalaj(cid:251)cy zachowa(cid:253) integralno(cid:334)(cid:253) danych.
Procesor zapyta(cid:313), jak sugeruje nazwa, przyjmuje zapytania kierowane do serwera,
buduje plan ich optymalnego wykonania, a nast(cid:271)pnie wykonuje go i dostarcza dane
wynikowe.
Zapytania s(cid:251) przekazywane do SQL Servera za pomoc(cid:251) j(cid:271)zyka SQL (lub T-SQL,
który jest rozszerzeniem j(cid:271)zyka SQL wykorzystywanym w Micorosoft SQL Serverze).
Poniewa(cid:366) SQL jest j(cid:271)zykiem deklaratywnym wysokiego poziomu, definiuje tylko,
jakie dane pobra(cid:253) z bazy danych, nie definiuje natomiast kroków potrzebnych do ich
pobrania ani algorytmów przetwarzania (cid:366)(cid:251)dania. Dlatego, dla ka(cid:366)dego odebranego
zapytania, pierwszym krokiem wykonywanym przez procesor zapyta(cid:313) jest jak naj-
szybsze okre(cid:334)lenie planu zapytania, który opisuje najlepszy mo(cid:366)liwy sposób (lub co
najmniej wydajny sposób) na wykonanie danego zapytania. Jego drugim zadaniem jest
wykonanie zapytania zgodnie z planem. Ka(cid:366)de z tych zada(cid:313) jest powierzane odr(cid:271)b-
nemu komponentowi wewn(cid:251)trz procesora zapyta(cid:313); optymalizator zapyta(cid:313) opraco-
wuje plan i przekazuje go do silnika wykonuj(cid:251)cego, który wykona zapytanie i pobierze
rezultaty z bazy danych.
Optymalizator zapyta(cid:313) bazy SQL Servera dzia(cid:311)a na podstawie kosztów. Dla danego
zapytania analizuje kilka potencjalnych planów zapytania, szacuje koszt ka(cid:366)dego
z nich i wybiera plan o najni(cid:366)szym koszcie. W rzeczy samej, bior(cid:251)c pod uwag(cid:271) fakt,
(cid:366)e optymalizator nie mo(cid:366)e przeanalizowa(cid:253) wszystkich mo(cid:366)liwych planów wykonania
zapytania, musi znale(cid:364)(cid:253) równowag(cid:271) pomi(cid:271)dzy czasem optymalizacji i jako(cid:334)ci(cid:251) wybra-
nego planu.
W zwi(cid:251)zku z tym jest to komponent serwera, który ma najwi(cid:271)kszy wp(cid:311)yw na wydaj-
no(cid:334)(cid:253) bazy. W ko(cid:313)cu wybór dobrego lub z(cid:311)ego planu zapytania mo(cid:366)e stanowi(cid:253) ró(cid:366)nic(cid:271)
Poleć książkęKup książkę20 Microsoft SQL Server 2014. Optymalizacja zapyta(cid:241)
pomi(cid:271)dzy wykonaniem zapytania w ci(cid:251)gu milisekund a wykonaniem go w ci(cid:251)gu
minut lub nawet godzin. Naturalnie, lepsze zrozumienie sposobu dzia(cid:311)ania optyma-
lizatora zapyta(cid:313) mo(cid:366)e pomóc zarówno administratorom baz, jak i programistom
w pisaniu lepszych zapyta(cid:313) i w zapewnianiu optymalizatorowi informacji potrzeb-
nych do jego jak najlepszego dzia(cid:311)ania. Ta ksi(cid:251)(cid:366)ka poka(cid:366)e Ci, jak wykorzysta(cid:253) nowo
poznan(cid:251) wiedz(cid:271) na temat architektury optymalizatora zapyta(cid:313), a ponadto przeka(cid:366)e
Ci wiedz(cid:271) i narz(cid:271)dzia do radzenia sobie z sytuacjami, w których optymalizator nie
produkuje dobrego planu.
Aby dotrze(cid:253) do optymalnego planu zapytania, procesor zapyta(cid:313) wykonuje kilka
kroków. Ca(cid:311)y proces przetwarzania zapytania zosta(cid:311) przedstawiony na rysunku 1.1.
Rysunek 1.1. Proces przetwarzania zapytania
Procesowi temu dok(cid:311)adniej przyjrzymy si(cid:271) w rozdziale 3., ale teraz omówi(cid:271) pokrótce
poszczególne kroki:
1. Parsowanie i przypisywanie. Zapytanie jest parsowane i przypisywane. Zak(cid:311)a-
daj(cid:251)c, (cid:366)e zapytanie jest poprawne, wynikiem tej fazy jest drzewo logiczne, któ-
rego ka(cid:366)dy w(cid:271)ze(cid:311) reprezentuje operacj(cid:271) logiczn(cid:251), jak(cid:251) musi wykona(cid:253) zapytanie,
tak(cid:251) jak na przyk(cid:311)ad czytanie tabeli lub wykonanie z(cid:311)(cid:251)czenia.
Poleć książkęKup książkęRozdzia(cid:239) 1. Wprowadzenie do optymalizacji zapyta(cid:241)
21
2. Optymalizacja zapytania. Drzewo logiczne jest wykorzystywane w procesie
optymalizacji zapytania, który, ogólnie rzecz ujmuj(cid:251)c, sk(cid:311)ada si(cid:271) z nast(cid:271)puj(cid:251)cych
kroków:
(cid:102) Generowanie mo(cid:366)liwych planów zapytania. Korzystaj(cid:251)c z drzewa logicz-
nego, optymalizator okre(cid:334)la kilka mo(cid:366)liwych sposobów wykonania zapytania
(czyli mo(cid:366)liwe plany wykonania). Plan wykonania to, ogólnie mówi(cid:251)c, zestaw
fizycznych operacji (takich jak Index Seek [przeszukanie indeksu] lub Nested
Loops Join [z(cid:311)(cid:251)czenie z zagnie(cid:366)d(cid:366)on(cid:251) p(cid:271)tl(cid:251)]), które mog(cid:251) zosta(cid:253) wykonane
w celu osi(cid:251)gni(cid:271)cia wymaganego rezultatu opisanego w drzewie logicznym.
(cid:102) Okre(cid:334)lenie kosztu ka(cid:366)dego z planów. Chocia(cid:366) optymalizator nie generuje
wszystkich mo(cid:366)liwych planów zapytania, okre(cid:334)la koszt zasobów i czasu
ka(cid:366)dego z wygenerowanych planów. Plan, którego szacowany koszt b(cid:271)dzie
najni(cid:366)szy, zostanie wybrany i przekazany dalej do silnika wykonuj(cid:251)cego.
3. Wykonywanie zapyta(cid:313) i przechowywanie planów. Zapytanie jest wykonywane
przez silnik wykonuj(cid:251)cy zgodnie z wybranym planem; plan mo(cid:366)e by(cid:253) przecho-
wywany w pami(cid:271)ci podr(cid:271)cznej planów.
Parsowanie i przypisywanie
Parsowanie i przypisywanie to pierwsze operacje wykonywane po przes(cid:311)aniu zapytania
do instancji SQL Servera. Parsowanie pozwala upewni(cid:253) si(cid:271), czy zapytanie T-SQL ma
poprawn(cid:251) sk(cid:311)adni(cid:271), i przekszta(cid:311)ca je w drzewo, a dok(cid:311)adniej w drzewo logicznych
operatorów reprezentuj(cid:251)cych kroki wysokiego poziomu prowadz(cid:251)ce do wykonania
zapytania. Na pocz(cid:251)tku te operatory b(cid:271)d(cid:251) blisko zwi(cid:251)zane z pierwotn(cid:251) sk(cid:311)adni(cid:251) zapy-
tania i b(cid:271)d(cid:251) zawiera(cid:311)y takie operacje jak „pobierz dane z tabeli Klient”, „pobierz dane
z tabeli Kontakt”, „wykonaj z(cid:311)(cid:251)czenie wewn(cid:271)trzne” i tak dalej. W trakcie procesu
optymalizacji b(cid:271)d(cid:251) wykorzystywane ró(cid:366)ne drzewa reprezentuj(cid:251)ce zapytanie, a drzewo
to b(cid:271)dzie mia(cid:311)o ró(cid:366)ne nazwy, dopóki nie zostanie wykorzystane do inicjalizacji
struktury Memo.
Przypisywanie jest zwi(cid:251)zane z rozwi(cid:251)zywaniem nazw. Podczas operacji przypi-
sywania SQL Server upewnia si(cid:271), czy wszystkie nazwy obiektów istniej(cid:251), i przypisuje
ka(cid:366)d(cid:251) tabel(cid:271) i kolumn(cid:271) na drzewie parsowania do powi(cid:251)zanych obiektów w katalogu
systemowym. Wynik tego procesu nazywany jest drzewem zalgebraizowanym. Drzewo
to jest nast(cid:271)pnie przesy(cid:311)ane do optymalizatora zapyta(cid:313).
Optymalizacja zapyta(cid:241)
Kolejny krok to proces optymalizacji, który jest w zasadzie generowaniem potencjal-
nych planów wykonywania i wyborem najlepszego z nich na podstawie szacowanych
kosztów ich wykonania. Jak ju(cid:366) wspomnia(cid:311)em, SQL Server wykorzystuje model sza-
cowania kosztów do okre(cid:334)lenia kosztu wykonania ka(cid:366)dego z planów.
Poleć książkęKup książkę22 Microsoft SQL Server 2014. Optymalizacja zapyta(cid:241)
Na proces optymalizacji zapyta(cid:313) mo(cid:366)na równie(cid:366) patrze(cid:253) jak na proces mapowania
logicznych operacji zapytania wyra(cid:366)onych w pierwotnym drzewie na operacje fizyczne,
które b(cid:271)d(cid:251) mog(cid:311)y zosta(cid:253) wykorzystane przez silnik wykonuj(cid:251)cy. W planach wykonania
tworzonych przez optymalizator zapyta(cid:313) jest w zasadzie implementowana funkcjo-
nalno(cid:334)(cid:253) silnika wykonywania; to znaczy silnik wykonywania implementuje pewn(cid:251)
liczb(cid:271) algorytmów, a optymalizator podczas tworzenia planu wykonania wybiera
spo(cid:334)ród nich. Robi to, t(cid:311)umacz(cid:251)c pierwotne operacje logiczne na operacje fizyczne,
które silnik wykonuj(cid:251)cy b(cid:271)dzie w stanie wykona(cid:253). Plany wykonania pokazuj(cid:251) zarówno
operacje logiczne, jak i fizyczne dla ka(cid:366)dego operatora. Te same logiczne operacje,
takie jak sortowanie, przek(cid:311)adaj(cid:251) si(cid:271) na te same operacje fizyczne, niektóre jednak
mapuj(cid:251) si(cid:271) na wi(cid:271)cej ni(cid:366) jedn(cid:251) mo(cid:366)liw(cid:251) operacj(cid:271) fizyczn(cid:251). Na przyk(cid:311)ad logiczne z(cid:311)(cid:251)-
czenie mo(cid:366)e by(cid:253) mapowane na kilka fizycznych operatorów: Nested Loops Join, Merge Join
(z(cid:311)(cid:251)czenie ze scalaniem) lub Hash Join (z(cid:311)(cid:251)czenie haszowe). Nie jest to wi(cid:271)c proces
mapowania jeden do jednego i wi(cid:251)(cid:366)e si(cid:271) z bardziej skomplikowanymi dzia(cid:311)aniami,
które dok(cid:311)adniej omówi(cid:271) w rozdziale 3.
Produktem ko(cid:313)cowym procesu optymalizacji zapytania jest plan wykonania
— drzewo sk(cid:311)adaj(cid:251)ce si(cid:271) z fizycznych operatorów, które zawieraj(cid:251) algorytmy wykony-
wane przez silnik wykonuj(cid:251)cy w celu pobrania (cid:366)(cid:251)danych wyników z bazy danych.
Generowanie mo(cid:285)liwych planów zapyta(cid:241)
Podstawowym celem istnienia optymalizatora zapyta(cid:313) jest odnalezienie wydajnego
planu wykonania zapytania. Nawet dla relatywnie prostych zapyta(cid:313) mo(cid:366)e istnie(cid:253)
ogromna liczba ró(cid:366)nych sposobów na uzyskanie dost(cid:271)pu do danych w celu otrzymania
tych samych rezultatów. W zwi(cid:251)zku z tym optymalizator musi wybra(cid:253) najlepszy plan
z puli, która mo(cid:366)e by(cid:253) bardzo du(cid:366)a, a podj(cid:271)cie dobrej decyzji jest wa(cid:366)ne, poniewa(cid:366)
czas potrzebny na zwrócenie wyniku u(cid:366)ytkownikowi mo(cid:366)e by(cid:253) bardzo ró(cid:366)ny w zale(cid:366)-
no(cid:334)ci od wybranego planu.
Zadaniem optymalizatora zapyta(cid:313) jest stworzenie i sprawdzenie najwi(cid:271)kszej mo(cid:366)-
liwej liczby planów w ramach pewnych kryteriów, aby znale(cid:364)(cid:253) wystarczaj(cid:251)co dobry
plan, który mo(cid:366)e, ale nie musi, by(cid:253) planem optymalnym. Definiujemy obszar poszu-
kiwa(cid:313) dla danego zapytania jako zestaw wszystkich mo(cid:366)liwych planów zapytania,
a ka(cid:366)dy mo(cid:366)liwy plan zwraca te same wyniki. Teoretycznie, aby znale(cid:364)(cid:253) optymalny
plan wykonania dla zapytania, optymalizator kosztowy powinien znale(cid:364)(cid:253) wszystkie
mo(cid:366)liwe plany istniej(cid:251)ce w danym obszarze poszukiwa(cid:313) i poprawnie oszacowa(cid:253)
koszty ich wszystkich. Niestety niektóre skomplikowane zapytania mog(cid:251) mie(cid:253) tysi(cid:251)ce,
a nawet miliony mo(cid:366)liwych planów, i chocia(cid:366) optymalizator mo(cid:366)e zazwyczaj bra(cid:253)
pod uwag(cid:271) znacz(cid:251) liczb(cid:271) planów, nie jest w stanie sprawdzi(cid:253) ich wszystkich. Gdyby
to zrobi(cid:253), czas potrzebny na tak(cid:251) operacj(cid:271) by(cid:311)by nieakceptowalnie d(cid:311)ugi i móg(cid:311)by
mie(cid:253) znacz(cid:251)cy wp(cid:311)yw na ca(cid:311)o(cid:334)ciowy czas wykonania zapytania.
Poleć książkęKup książkęRozdzia(cid:239) 1. Wprowadzenie do optymalizacji zapyta(cid:241)
23
Optymalizator zapyta(cid:313) musi utrzymywa(cid:253) równowag(cid:271) pomi(cid:271)dzy czasem optyma-
lizacji a jako(cid:334)ci(cid:251) planu. Na przyk(cid:311)ad, je(cid:366)eli optymalizator potrzebuje 1 sekundy na
znalezienie wystarczaj(cid:251)co dobrego planu, który wykonany zostanie w minut(cid:271), nie
ma sensu szuka(cid:253) idealnego lub najbardziej optymalnego planu, skoro zaj(cid:271)(cid:311)oby to
5 minut plus czas wykonania zapytania. Dlatego SQL Server nie wykonuje wyczerpu-
j(cid:251)cego wyszukiwania, lecz próbuje znale(cid:364)(cid:253) wystarczaj(cid:251)co dobry plan najszybciej jak
to mo(cid:366)liwe. Poniewa(cid:366) praca optymalizatora jest ograniczona czasem, istnieje szansa,
(cid:366)e znaleziony plan b(cid:271)dzie planem optymalnym, ale mo(cid:366)e te(cid:366) by(cid:253) czym(cid:334) tylko zbli(cid:366)o-
nym do planu optymalnego.
Aby zbada(cid:253) obszar poszukiwa(cid:313), optymalizator wykorzystuje regu(cid:311)y transformacji
i heurystyk(cid:271). Generowanie potencjalnych planów zapytania jest wykonywane wewn(cid:251)trz
optymalizatora z wykorzystaniem regu(cid:311) transformacji, a wykorzystanie heurystyki
ogranicza liczb(cid:271) branych pod uwag(cid:271) planów, tak aby czas wykonywania optymalizacji
by(cid:311) akceptowalny. Zestaw alternatywnych planów rozpatrywanych przez optymalizator
nazywany jest obszarem planów, a same plany podczas optymalizacji przechowywane
s(cid:251) w pami(cid:271)ci w komponencie o nazwie Memo. Regu(cid:311)y transformacji, heurystyk(cid:271) i struk-
tur(cid:271) Memo omówi(cid:271) dok(cid:311)adnie w rozdziale 3.
Okre(cid:258)lanie kosztu ka(cid:285)dego z planów
Wyszukanie lub ponumerowanie potencjalnych planów to tylko jedna cz(cid:271)(cid:334)(cid:253) procesu
optymalizacji. Optymalizator zapyta(cid:313) musi jeszcze oszacowa(cid:253) koszt tych planów
i wybra(cid:253) najmniej kosztowny z nich. Aby oszacowa(cid:253) koszt planu, musi oszacowa(cid:253)
koszt ka(cid:366)dego fizycznego operatora w planie, korzystaj(cid:251)c z formu(cid:311) kosztowych, które
uwzgl(cid:271)dniaj(cid:251) wykorzystanie zasobów takich jak I/O, CPU i pami(cid:271)(cid:253). Szacunek kosztów
zale(cid:366)ny jest w du(cid:366)ej mierze od algorytmu wykorzystywanego przez fizyczny operator
i szacowanej liczby rekordów, które b(cid:271)d(cid:251) musia(cid:311)y zosta(cid:253) przetworzone. Szacunek
liczby rekordów do przetworzenia nazywa si(cid:271) szacunkiem kardynalno(cid:329)ci.
Aby pomóc w szacunku kardynalno(cid:334)ci, SQL Server wykorzystuje i przechowuje
statystyki, które zawieraj(cid:251) informacje opisuj(cid:251)ce rozk(cid:311)ad warto(cid:334)ci w jednej lub wi(cid:271)kszej
liczbie kolumn tabeli. Kiedy koszt ka(cid:366)dego z operatorów zostanie oszacowany z wyko-
rzystaniem szacunku kardynalno(cid:334)ci i wymaga(cid:313) dotycz(cid:251)cych zasobów, optymalizator
doda do siebie wszystkie te koszty, w wyniku czego otrzyma szacowany koszt planu.
Nie b(cid:271)d(cid:271) tutaj zag(cid:311)(cid:271)bia(cid:311) si(cid:271) w szczegó(cid:311)y, statystyki omówi(cid:271) dok(cid:311)adniej w rozdziale 6.
Wykonywanie zapyta(cid:241) i przechowywanie planów
Kiedy zapytanie zosta(cid:311)o zoptymalizowane, plan wynikowy jest wykorzystywany przez
silnik wykonuj(cid:251)cy do pobrania (cid:366)(cid:251)danych danych. Wygenerowany plan zapytania mo(cid:366)e
by(cid:253) przechowywany w pami(cid:271)ci w magazynie planów, dzi(cid:271)ki czemu b(cid:271)dzie móg(cid:311) zosta(cid:253)
powtórnie wykorzystany, je(cid:366)eli to samo zapytanie zostanie wykonane powtórnie.
Poleć książkęKup książkę24 Microsoft SQL Server 2014. Optymalizacja zapyta(cid:241)
SQL Server ma pul(cid:271) pami(cid:271)ci, która jest wykorzystywana do przechowywania zarówno
stron danych, jak i planów zapyta(cid:313). Wi(cid:271)kszo(cid:334)(cid:253) tej pami(cid:271)ci wykorzystywana jest do
przechowywania stron danych i nazywa si(cid:271) pul(cid:246) bufora. Cz(cid:271)(cid:334)(cid:253) tej pami(cid:271)ci zawiera
plany wykonywania zapyta(cid:313), które zosta(cid:311)y zoptymalizowane przez optymalizator,
i nazywa si(cid:271) magazynem planów (wcze(cid:334)niej nazywana by(cid:311)a magazynem procedur). Procen-
towa ilo(cid:334)(cid:253) miejsca w pami(cid:271)ci przypisana do magazynu planów lub puli bufora jest
dynamiczna i zale(cid:366)y od stanu systemu.
Przed optymalizowaniem zapytania SQL Server sprawdza, czy w magazynie planów
nie zosta(cid:311) zapisany plan dla wykonanego zestawu zapyta(cid:313). Optymalizacja zapyta(cid:313)
to relatywnie kosztowna operacja, je(cid:366)eli wi(cid:271)c w magazynie planów dost(cid:271)pny jest odpo-
wiedni plan, proces optymalizacji mo(cid:366)e zosta(cid:253) pomini(cid:271)ty, dzi(cid:271)ki czemu system unika
kosztów w postaci czasu optymalizacji, zasobów procesora i tak dalej. Je(cid:366)eli plan dla
zestawu zapyta(cid:313) nie zostanie odnaleziony, zestaw jest kompilowany w celu wygene-
rowania planów dla wszystkich zapyta(cid:313) w procedurze przechowywanej, wyzwalaczu
lub dynamicznym kodzie SQL. Optymalizacja rozpoczyna si(cid:271) od za(cid:311)adowania wszyst-
kich interesuj(cid:251)cych statystyk. Nast(cid:271)pnie optymalizator zapyta(cid:313) sprawdza, czy staty-
styki s(cid:251) aktualne. Dla nieaktualnych statystyk, w przypadku domy(cid:334)lnych ustawie(cid:313)
statystyk, przed przej(cid:334)ciem do dalszego etapu optymalizacji statystyki zostan(cid:251) zak-
tualizowane.
Kiedy plan zostanie znaleziony w magazynie planów lub zostanie stworzony nowy
plan, nast(cid:251)pi sprawdzenie, czy nie pojawi(cid:311)y si(cid:271) zmiany w strukturze lub statystykach.
Zmiany w strukturze s(cid:251) weryfikowane pod k(cid:251)tem poprawno(cid:334)ci planu. Statystyki rów-
nie(cid:366) s(cid:251) weryfikowane: optymalizator sprawdza, czy nie wyst(cid:271)puj(cid:251) nowsze statystyki
lub czy statystyki si(cid:271) nie przedawni(cid:311)y. Je(cid:366)eli z którego(cid:334) z tych powodów plan nie jest
poprawny, wówczas jest odrzucany, a zestaw lub pojedyncze zapytanie jest kompi-
lowane jeszcze raz. Takie kompilacje nazywane s(cid:251) rekompilacjami. Proces ten zosta(cid:311)
pokazany na rysunku 1.2.
Plany mog(cid:251) by(cid:253) usuwane z magazynu planów, je(cid:366)eli serwer wymaga zwolnienia
pami(cid:271)ci lub kiedy zosta(cid:311)y wykonane pewne instrukcje. Zmiana niektórych opcji kon-
figuracji (np. maksymalny poziom wspó(cid:311)bie(cid:366)no(cid:334)ci) wyczy(cid:334)ci ca(cid:311)y magazyn planów.
Podobnie niektóre instrukcje, takie jak operacje na bazie z wykorzystaniem pew-
nych opcji ALTER DATABASE, spowoduj(cid:251) wyczyszczenie planów zwi(cid:251)zanych z t(cid:251) baz(cid:251).
Warto równie(cid:366) zauwa(cid:366)y(cid:253), (cid:366)e powtórne wykorzystanie istniej(cid:251)cego planu nie zawsze
musi by(cid:253) dobrym rozwi(cid:251)zaniem dla danego zapytania oraz (cid:366)e w takim przypadku
mog(cid:251) pojawi(cid:253) si(cid:271) pewne problemy. Na przyk(cid:311)ad, w zale(cid:366)no(cid:334)ci od rozk(cid:311)adu danych
w tabeli, optymalny plan zapytania mo(cid:366)e w du(cid:366)ej mierze zale(cid:366)e(cid:253) od wykorzystanych
parametrów. Wi(cid:271)cej na temat tego typu problemów i samego magazynu planów
dowiesz si(cid:271) z rozdzia(cid:311)u 8.
Poleć książkęKup książkęRozdzia(cid:239) 1. Wprowadzenie do optymalizacji zapyta(cid:241)
25
Rysunek 1.2. Proces kompilacji i rekompilacji
Plany wykonania
Teraz, kiedy znamy podstawy dzia(cid:311)ania procesora zapyta(cid:313), czas dowiedzie(cid:253) si(cid:271), jak
mo(cid:366)emy wp(cid:311)ywa(cid:253) na jego dzia(cid:311)anie. Podstawowym sposobem interakcji z procesorem
zapyta(cid:313) s(cid:251) plany wykonania, które, jak wcze(cid:334)niej wspomnia(cid:311)em, s(cid:251) drzewami sk(cid:311)a-
daj(cid:251)cymi si(cid:271) z operatorów fizycznych, które z kolei zawieraj(cid:251) algorytmy prowadz(cid:251)ce
do wygenerowania (cid:366)(cid:251)danych wyników z bazy danych. Bior(cid:251)c pod uwag(cid:271), (cid:366)e w ca(cid:311)ej
ksi(cid:251)(cid:366)ce b(cid:271)dziemy cz(cid:271)sto korzysta(cid:253) z planów wykonania, w tym podrozdziale poka(cid:366)(cid:271),
jak je wy(cid:334)wietla(cid:253) i czyta(cid:253).
Mo(cid:366)esz za(cid:366)(cid:251)da(cid:253) w(cid:311)a(cid:334)ciwego lub szacowanego planu zapytania dla danego zapy-
tania, a oba typy mog(cid:251) zosta(cid:253) wy(cid:334)wietlone jako grafika, tekst lub XML. Ka(cid:366)dy z tych
formatów pokazuje ten sam plan wykonania, a jedyna ró(cid:366)nica tkwi w sposobie przed-
stawienia i w stopniu szczegó(cid:311)owo(cid:334)ci informacji w nich zawartych.
Poleć książkęKup książkę26 Microsoft SQL Server 2014. Optymalizacja zapyta(cid:241)
Kiedy wykonujemy (cid:366)(cid:251)danie wy(cid:334)wietlenia szacowanego planu wykonywania, zapy-
tanie nie jest wykonywane; wy(cid:334)wietlany plan jest tym, który najprawdopodobniej
zosta(cid:311)by wykorzystany, gdyby zapytanie zosta(cid:311)o wykonane (nale(cid:366)y jednak pami(cid:271)ta(cid:253),
(cid:366)e rekompilacja mo(cid:366)e spowodowa(cid:253) wygenerowanie innego planu zapytania — dok(cid:311)ad-
niej omówi(cid:271) t(cid:271) kwesti(cid:271) w dalszej cz(cid:271)(cid:334)ci). Gdy jednak za(cid:366)(cid:251)damy w(cid:311)a(cid:334)ciwego planu,
zapytanie musi zosta(cid:253) wykonane, a plan zostanie wy(cid:334)wietlony wraz z wynikami zapy-
tania. Mimo wszystko wykorzystanie planu szacowanego ma kilka zalet, mo(cid:366)emy
bowiem na przyk(cid:311)ad przejrze(cid:253) plan dla zapytania, które wykonuje si(cid:271) bardzo d(cid:311)ugo
bez konieczno(cid:334)ci jego wykonywania, lub wy(cid:334)wietli(cid:253) plan zapytania dla operacji aktu-
alizacji bez konieczno(cid:334)ci zmieniania bazy danych.
Plany graficzne
W programie SQL Server Management Studio mo(cid:366)esz wy(cid:334)wietla(cid:253) plany graficzne,
klikaj(cid:251)c przycisk Display Estimated Execution Plan (wy(cid:334)wietlenie szacowanego planu
zapytania) lub Include Actual Execution Plan (uwzgl(cid:271)dnienie w(cid:311)a(cid:334)ciwego planu zapytania)
na pasku narz(cid:271)dzi. Klikni(cid:271)cie przycisku Display Estimated Execution Plan wy(cid:334)wietli plan
zapytania od razu, bez wykonywania zapytania. Aby uzyska(cid:253) w(cid:311)a(cid:334)ciwy plan, musisz
klikn(cid:251)(cid:253) przycisk Include Actual Execution Plan, a nast(cid:271)pnie wykona(cid:253) zapytanie i przej(cid:334)(cid:253)
do zak(cid:311)adki Execution plan (plan wykonania).
Aby uzyska(cid:253) przyk(cid:311)ad, przekopiuj poni(cid:366)sze zapytanie do edytora zapyta(cid:313) programu
SQL Server Management Studio, wybierz baz(cid:271) AdventureWorks2012, kliknij przycisk
Include Actual Execution Plan, a nast(cid:271)pnie wykonaj zapytanie:
SELECT DISTINCT(City) FROM Person.Address
W panelu rezultatów wybierz zak(cid:311)adk(cid:271) Execution plan. Wy(cid:334)wietlony zostanie plan przed-
stawiony na rysunku 1.3.
Rysunek 1.3. Graficzny plan wykonania
Ka(cid:366)dy w(cid:271)ze(cid:311) w drzewie jest reprezentowany przez ikon(cid:271) przedstawiaj(cid:251)c(cid:251) logiczny
i fizyczny operator, taki jak Index Scan czy Hash Aggregate, zgodnie z rysunkiem 1.3.
Pierwsza ikona to element j(cid:271)zyka o nazwie Result operator (operator rezultatu) i repre-
zentuje polecenie SELECT, a zazwyczaj jest te(cid:366) g(cid:311)ównym elementem planu.
Operatory implementuj(cid:251) podstawow(cid:251) funkcj(cid:271) lub operacj(cid:271) silnika wykonuj(cid:251)cego;
na przyk(cid:311)ad logiczna operacja z(cid:311)(cid:251)czenia mo(cid:366)e by(cid:253) implementowana przez jedn(cid:251)
z trzech fizycznych operacji z(cid:311)(cid:251)czenia (Nested Loops Join, Merge Join, Hash Join). Oczywi(cid:334)cie,
Poleć książkęKup książkęRozdzia(cid:239) 1. Wprowadzenie do optymalizacji zapyta(cid:241)
27
UWAGA
W tej ksi(cid:200)(cid:285)ce znajdziesz ogromn(cid:200) liczb(cid:218) przyk(cid:239)adowych zapyta(cid:241) SQL — wszystkie zapytania
tworzone by(cid:239)y dla bazy AdventureWorks2012, chocia(cid:285) rozdzia(cid:239) 9. wykorzystuje równie(cid:285) baz(cid:218)
AdventureWorksDW2012. Kod by(cid:239) testowany na bazie SQL Server 2014 RTM. Te bazy danych
nie s(cid:200) do(cid:239)(cid:200)czane do standardowej instalacji MS SQL Servera, ale mo(cid:285)esz je pobra(cid:202) ze strony
CodePlex. Musisz pobra(cid:202) rodzin(cid:218) baz przyk(cid:239)adowych dla SQL Servera 2012 (w momencie pisania
tej ksi(cid:200)(cid:285)ki przyk(cid:239)adowe bazy dla MS SQL Servera 2014 nie istnia(cid:239)y). Podczas instalacji mo(cid:285)esz
zainstalowa(cid:202) wszystkie bazy lub tylko AdventureWorks2012 i AdventureWorksDW2012.
w silniku wykonuj(cid:251)cym jest zaimplementowanych znacznie wi(cid:271)cej operatorów, a ca(cid:311)(cid:251)
ich list(cid:271) znajdziesz pod adresem http://msdn.microsoft.com/en-us/library/ms191158(v=
sql.110).aspx. Ikony operatorów logicznych i fizycznych wy(cid:334)wietlane s(cid:251) w kolorze
niebieskim, z wyj(cid:251)tkiem operatorów kursora, które s(cid:251) (cid:366)ó(cid:311)te, i elementów j(cid:271)zyka, które
s(cid:251) zielone:
Operator logiczny/fizyczny
Element j(cid:218)zyka
Kursor
Optymalizator zapyta(cid:313) buduje plan wykonania, wybieraj(cid:251)c spo(cid:334)ród operatorów,
które mog(cid:251) odczytywa(cid:253) rekordy z bazy, jak na przyk(cid:311)ad operator Index Scan przed-
stawiony na poprzednim planie; mog(cid:251) te(cid:366) odczytywa(cid:253) rekordy z innego operatora,
jak na przyk(cid:311)ad operator Hash Aggregate odczytuj(cid:251)cy rekordy z operatora Index Scan.
Ka(cid:366)dy w(cid:271)ze(cid:311) jest zwi(cid:251)zany z w(cid:271)z(cid:311)em nadrz(cid:271)dnym, z którym jest po(cid:311)(cid:251)czony strza(cid:311)k(cid:251),
a dane p(cid:311)yn(cid:251) od operatora potomnego do nadrz(cid:271)dnego, przy czym szeroko(cid:334)(cid:253) strza(cid:311)ki
jest proporcjonalna do liczby rekordów. Kiedy operator wykonuje pewne funkcje na
przeczytanych rekordach, rezultaty za(cid:334) s(cid:251) przekazywane do w(cid:271)z(cid:311)a nadrz(cid:271)dnego. Mo(cid:366)esz
najecha(cid:253) kursorem myszy, aby uzyska(cid:253) wi(cid:271)cej informacji na temat ilo(cid:334)ci danych —
zostan(cid:251) zaprezentowane w oknie podpowiedzi. Je(cid:366)eli, na przyk(cid:311)ad, najedziesz na strza(cid:311)k(cid:271)
pomi(cid:271)dzy operacjami Index Scan i Hash Aggregate przedstawion(cid:251) na rysunku 1.3, otrzy-
masz informacje o danych przep(cid:311)ywaj(cid:251)cych pomi(cid:271)dzy tymi operacjami (zobacz
rysunek 1.4).
Operator Index Scan odczytuje 19 614 rekordów i przesy(cid:311)a je do operatora Hash
Aggregate. Z kolei operator Hash Aggregate wykonuje pewne operacje na danych i do swo-
jego w(cid:271)z(cid:311)a nadrz(cid:271)dnego przesy(cid:311)a 575 rekordów, co równie(cid:366) mo(cid:366)esz zobaczy(cid:253), naje(cid:366)-
d(cid:366)aj(cid:251)c kursorem myszy na strza(cid:311)k(cid:271) pomi(cid:271)dzy tymi operacjami.
Poleć książkęKup książkę28 Microsoft SQL Server 2014. Optymalizacja zapyta(cid:241)
Rysunek 1.4. Przep(cid:239)yw danych pomi(cid:218)dzy operatorami Index Scan i Hash Aggregate
W tym planie operacja Index Scan czyta wszystkie 19 614 wierszy indeksu, a operacja
Hash Aggregate wykonuje operacje maj(cid:251)ce wybra(cid:253) niepowtarzaj(cid:251)ce si(cid:271) nazwy miast, któ-
rych jest 575 — te nazwy zostan(cid:251) wy(cid:334)wietlone w oknie rezultatów programu Mana-
gement Studio. Zauwa(cid:366) tak(cid:366)e, (cid:366)e oprócz rzeczywistej liczby rekordów otrzymujesz
te(cid:366) informacje o szacowanej liczbie wierszy, która jest szacunkiem kardynalno(cid:334)ci
wykonywanym przez optymalizator dla tego operatora. Porównanie rzeczywistej
i szacowanej liczby rekordów mo(cid:366)e pomóc w wykryciu b(cid:311)(cid:271)dów szacunku kardynal-
no(cid:334)ci, które mog(cid:251) wp(cid:311)yn(cid:251)(cid:253) na jako(cid:334)(cid:253) planów wykonywania (wi(cid:271)cej na ten temat dowiesz
si(cid:271) z rozdzia(cid:311)u 6.).
Aby by(cid:311)y w stanie wykonywa(cid:253) swoje zadanie, operatory fizyczne musz(cid:251) implemen-
towa(cid:253) przynajmniej trzy poni(cid:366)sze metody:
(cid:102) Open() — inicjalizuje operator, mo(cid:366)e zawiera(cid:253) zadania przygotowuj(cid:251)ce wyma-
gane struktury danych.
(cid:102) GetRow() — wykonuje (cid:366)(cid:251)danie rekordu z operatora.
(cid:102) Close() — wykonuje operacje sprz(cid:251)taj(cid:251)ce i zamyka operator po zako(cid:313)czeniu
jego roli.
Operator pobiera rekordy z innych operatorów za pomoc(cid:251) metody GetRow(), co
oznacza równie(cid:366), (cid:366)e wykonanie planu rozpoczyna si(cid:271) od lewej do prawej. Poniewa(cid:366)
GetRow() tworzy tylko jeden wiersz w danym momencie, liczba rekordów wy(cid:334)wietlona
w planie zapytania jest równie(cid:366) liczb(cid:251) wywo(cid:311)a(cid:313) metody dla danego operatora; do ozna-
czenia ko(cid:313)ca zestawu danych wynikowych wykorzystywane jest jedno dodatkowe wywo-
(cid:311)anie metody GetRow(). W poprzednim przyk(cid:311)adzie operator Hash Aggregate wywo(cid:311)uje na
operatorze Index Scan metod(cid:271) Open() raz, metod(cid:271) GetRow() 19 615 razy i raz metod(cid:271) Close().
UWAGA
Na razie wyja(cid:258)nimy tradycyjny tryb przetwarzania zapyta(cid:241), w którym operatory przetwarzaj(cid:200)
jeden wiersz w danym momencie. Ten tryb przetwarzania by(cid:239) wykorzystywany we wszystkich
wersjach SQL Servera od wersji 7.0. W rozdziale 9. wspomn(cid:218) o nowym trybie przetwarzania
partiami, wprowadzonym w SQL Serverze 2012 i wykorzystywanym przez operatory zwi(cid:200)zane
z indeksami magazynów kolumn.
Poleć książkęKup książkęRozdzia(cid:239) 1. Wprowadzenie do optymalizacji zapyta(cid:241)
29
Mo(cid:366)esz równie(cid:366) najecha(cid:253) kursorem myszy na operator, aby uzyska(cid:253) o nim wi(cid:271)cej
informacji. Na przyk(cid:311)ad rysunek 1.5 przedstawia informacje o operatorze Index Scan;
zauwa(cid:366), (cid:366)e zawiera mi(cid:271)dzy innymi opis operatora i dane na temat szacowanych
kosztów, takich jak koszt I/O, CPU, operatora i poddrzewa (subtree).
Rysunek 1.5. Okno z informacjami o operatorze Index Scan
Niektóre z tych w(cid:311)a(cid:334)ciwo(cid:334)ci omawiam w tabeli 1.1, inne wyja(cid:334)ni(cid:271) w dalszej cz(cid:271)(cid:334)ci
ksi(cid:251)(cid:366)ki.
UWAGA
Warto nadmieni(cid:202), (cid:285)e koszt wyra(cid:285)ony jest w wewn(cid:218)trznych jednostkach, które nie powinny by(cid:202)
uto(cid:285)samiane z sekundami czy milisekundami.
Koszt ka(cid:366)dego z operatorów jest równie(cid:366) przedstawiany relatywnie, jako warto(cid:334)(cid:253)
procentowa ca(cid:311)ego planu (zobacz rysunek 1.3). Na przyk(cid:311)ad koszt operacji Index Scan
wynosi 52 kosztu ca(cid:311)ego planu. Dodatkowe informacje z operatora lub ca(cid:311)ego zapy-
tania mo(cid:366)na pozyska(cid:253) za pomoc(cid:251) okna Properties (w(cid:311)a(cid:334)ciwo(cid:334)ci). Wybór ikony SELECT
i otwarcie okna Properties z menu View (widok) lub naci(cid:334)ni(cid:271)cie klawisza F4 poka(cid:366)e w(cid:311)a-
(cid:334)ciwo(cid:334)ci ca(cid:311)ego zapytania (zobacz rysunek 1.6).
Poleć książkęKup książkę30 Microsoft SQL Server 2014. Optymalizacja zapyta(cid:241)
Tabela 1.1. W(cid:239)a(cid:258)ciwo(cid:258)ci operatorów
W(cid:239)a(cid:258)ciwo(cid:258)(cid:202)
Opis
Physical Operation
Algorytm fizyczny dla w(cid:218)z(cid:239)a.
Logical Operation
Operator algebry relacyjnej reprezentowany przez w(cid:218)ze(cid:239).
Actual Number of Rows Liczba rekordów wytworzonych przez operator.
Estimated I/O Cost
Szacunkowy koszt operacji I/O. Nie wszystkie operacje zwi(cid:200)zane s(cid:200) z kosztem I/O.
Estimated Operator Cost Koszt operacji oszacowany przez optymalizator zapyta(cid:241). Jest to szacowany koszt I/O
i CPU. Zawiera tak(cid:285)e koszt operacji jako procentow(cid:200) warto(cid:258)(cid:202) ca(cid:239)ego kosztu zapytania
wy(cid:258)wietlony w nawiasach.
Estimated Subtree Cost
Szacowany narastaj(cid:200)cy koszt wykonania tej operacji i wszystkich operacji poprzedzaj(cid:200)cych
j(cid:200) w tym samym poddrzewie.
Estimated CPU Cost
Szacowany koszt procesora dla tej operacji.
Estimated Number
of Executions
Szacowana liczba wywo(cid:239)a(cid:241) tego operatora podczas wykonywania bie(cid:285)(cid:200)cego zapytania.
Number of Executions
Liczba wywo(cid:239)a(cid:241) tego operatora po wykonaniu zapytania.
Estimated Number
of Rows
Szacowana liczba rekordów wytworzona przez operator (szacunek kardynalno(cid:258)ci).
Estimated Row Size
Szacowany (cid:258)redni rozmiar rekordu przetwarzanego przez ten operator.
Rysunek 1.6. Okno w(cid:239)a(cid:258)ciwo(cid:258)ci zapytania
Poleć książkęKup książkęRozdzia(cid:239) 1. Wprowadzenie do optymalizacji zapyta(cid:241)
31
Tabela 1.2 zawiera wi(cid:271)kszo(cid:334)(cid:253) w(cid:311)a(cid:334)ciwo(cid:334)ci z rysunku 1.6. W zale(cid:366)no(cid:334)ci od zapytania
mog(cid:251) pojawi(cid:253) si(cid:271) inne, opcjonalne w(cid:311)a(cid:334)ciwo(cid:334)ci (na przyk(cid:311)ad Parameter List lub Warnings).
Tabela 1.2. W(cid:239)a(cid:258)ciwo(cid:258)ci zapyta(cid:241)
W(cid:239)a(cid:258)ciwo(cid:258)(cid:202)
Cached plan size
CompileCPU
CompileMemory
CompileTime
Degree of Parallelism
Memory Grant
MemoryGrantInfo
Optimization Level
Opis
Ilo(cid:258)(cid:202) pami(cid:218)ci w kilobajtach w magazynie planów wykorzystana
przez plan zapytania.
Czas procesora w milisekundach wykorzystany do skompilowania
zapytania.
Pami(cid:218)(cid:202) w kilobajtach wykorzystana do skompilowania zapytania.
Czas w milisekundach wykorzystany do skompilowania zapytania.
Liczba w(cid:200)tków, które mog(cid:200) zosta(cid:202) wykorzystane do wykonania
zapytania, je(cid:285)eli procesor zapyta(cid:241) wybierze plan równoleg(cid:239)y.
Ilo(cid:258)(cid:202) pami(cid:218)ci w kilobajtach udzielonej do uruchomienia tego zapytania.
Informacje dotycz(cid:200)ce szacunku na temat udzielonej pami(cid:218)ci i informacje
o rzeczywistej ilo(cid:258)ci udzielonej pami(cid:218)ci.
Poziom optymalizacji wykorzystany do skompilo
Pobierz darmowy fragment (pdf)