Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00324 007707 11223365 na godz. na dobę w sumie
Microsoft SQL Server 2014. Optymalizacja zapytań - ebook/pdf
Microsoft SQL Server 2014. Optymalizacja zapytań - ebook/pdf
Autor: Liczba stron: 384
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-283-1165-7 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> sql server
Porównaj ceny (książka, ebook (-20%), audiobook).

Wrzuć piąty bieg!

Microsoft SQL Server 2014 to najnowsza wersja serwera bazodanowego firmy Microsoft. Ta platforma jest rozwijana od ponad 25 lat, a każda kolejna jej wersja wprowadza serię ulepszeń — zarówno w obszarze możliwości, jak i wydajności. Jednak sam rozwój serwera nie wystarczy, żeby błyskawicznie wyciągać z bazy danych kluczowe informacje. Konieczna jest także optymalizacja parametrów jego pracy oraz zadawanych zapytań SQL.

Jak to zrobić? Na to i wiele innych pytań odpowiada ta niepowtarzalna książka. Została ona w całości poświęcona optymalizacji serwera oraz zapytań SQL. W kolejnych rozdziałach znajdziesz bezcenne informacje na temat rozwiązywania problemów z zapytaniami oraz optymalizacją zapytań. Ponadto zrozumiesz sposób działania optymalizatora, zalety dynamicznych widoków oraz znaczenie dobrze wybranych indeksów. Microsoft SQL Server zbiera liczne informacje na temat swojej pracy — możesz je wykorzystać do podkręcenia jego osiągów. Ta książka jest obowiązkową lekturą każdego administratora, który ma do czynienia z Microsoft SQL Server!
 

Zmuś Microsoft SQL Server do pracy na najwyższych obrotach!


Benjamin Navarez — MVP w dziedzinie SQL Server. Niezależny konsultant. Ma ponad 15 lat doświadczenia w pracy z bazą danych SQL Server. Główny obszar jego zainteresowań obejmuje optymalizację zapytań SQL.
 
Znajdź podobne książki Ostatnio czytane w tej kategorii

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)

Gdzie kupić całą publikację:

Microsoft SQL Server 2014. Optymalizacja zapytań
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: