Ludzie nie lubią czekać na wynik działania aplikacji. SQL Serwer wykonuje świetną pracę optymalizując wykonywanie zapytań lecz optymalizator nie jest doskonały. Znalezienie optymalnego planu zapytania jest złożonym procesem. Możemy pomóc optymalizatorowi poprzez odpowiednią architekturę bazy danych i aplikacji, poprawnie napisane zapytania, dobre indeksy, podpowiedzi do wykonywanych zapytań (hints) itp. Aby pomóc wybrać serwerowi najlepszą opcję polepszającą wydajność zapytania trzeba pierw zrozumieć jak SQL Serwer wykonuje zapytania.

Problemy optymalizacji zapytań i QUERY OPTIMIZER

Większość zapytań może być wykonana na wiele różnych sposobów. Jak jest ich wiele? Liczba różnych dróg wykonywania zapytań rośnie wraz z ich złożonością. Przykładowo przeanalizujmy poniższe pseudo zapytanie:

SELECT A.col5, SUM(C.col6) AS col6sum
FROM TableA AS A
INNER JOIN TableB AS B
ON A.col1 = B.col1
INNER JOIN TableC AS C
ON B.col2 = c.col2
WHERE A.col3 = constant 1
AND B.col4 = constant2
GROUP BY A.col5;

zacznijmy od części FROM. Którą parę z tabel SQL Serwer powinien połączyć najpierw, tabelę A z B czy B z C? Które z tabel powinny być tabelami z lewej strony w łączeniu a które z prawej? Liczba możliwości to 6, jeżeli złączenia są obliczane liniowo. jeżeli przetwarzamy wiele złączeń jednocześnie to liczba kombinacji wynosi 12. SQL Serwer może wykonać złączenia na różne sposoby. Może użyć do tego jednego z poniższych algorytmów:

  • NESTED LOOPS
  • MERGE
  • HASH
  • BITMAP FILTERING OPTIMIZED HASH

Daje to cztery opcje dla każdego złączenia czyli dla zapytania istnieje 6x4 = 24 różne opcje dla wykonania części FROM. Ale w rzeczywistej sytuacji jest jeszcze gorzej bo SQL Serwer może wykonać złączenie HASH na trzy różne sposoby. Byłą to tylko powierzchowna analiza wykonania pseudo zapytania służąca wprowadzeniu w problem optymalizacji zapytań. W klauzuli WHERE wyrażenie jest połączone operatorem logicznym AND które jest przemienne co daje 24x2 = 48 rozwiązania. Tak samo w klauzuli GROUP BY SQL Serwer może zadziałać na dwa sposoby, jako posortowane grupowanie lub grupowanie mieszane itd. Licząc w ten sposób można by dojść do ogromnej liczby możliwych rozwiązań. SQL Serwer musi zdecydować którego użyć w bardzo krótkim czasie. Nikt nie chciał by czekać całego dnia aby znaleźć najlepszy możliwy plan zapytania które potem zostałoby wykonane w 5 sekund zamiast 15 sekund.

Fazy wykonywania zapytania

query exec Wykonywanie zapytania zaczyna się od uruchomienia kodu T-SQL. W czasie fazy parsowania, SQL Serwer sprawdza czy zapytanie jest poprawne składniowo. Jeżeli zapytanie jest poprawne składniowo to wynikiem tej fazy jest drzewo operacji logicznych zwane parse tree. Następna faza to faza zwana binding w której SQL Serwer rozpoznaje nazwy obiektów w zapytaniu i wiąże je z operatorami logicznymi. Oczywiście obiekty muszą istnieć aby ta faza zakończyła się sukcesem. Wynikiem tej fazy jest drzewo zwane algebrized tree. Proces znajdywania i przeliczania rożnych opcji  zwanych planami wykonania zapytania ma miejsce w fazie query optimization. Jest to faza w której optymalizator wykonuje większość swojej pracy. SQL Serwer generuje plany wykonania i przelicza ich wydajność. Optymalizator szuka najlepszego planu wykonania dla następnej fazy. Wynikiem tej fazy jest aktualny plan zapytania z angielskiego actual execution plan który jest drzewem z operacjami fizycznymi. Wszystkie kroki do tej pory są wykonywane przez silnik relacyjny. Silnik relacyjny jest wewnętrznym komponentem który pracuje na poziomie logicznym. Właściwe wykonanie odbywa się w silniku składowania (ang. storage engine). Silnik składowania przeprowadza operacje fizyczne na danych. W skrócie Query Optymizer musi zamienić operatory logiczne na fizyczne które można wykonać fizycznie na danych np logiczny operator JOIN może być przetransformowany do fizycznej operacji MERGE JOIN. Wynikiem finalnej fazy (Execution phase) jest pożądany zestaw wyników. Wynik tej fazy otrzymywany jest na podstawie planu wykonania otrzymanego z poprzedniej fazy. Plan wykonania może być również zachowany w pamięci podręcznej w celu wielokrotnego użycia zamiast wykonywania ponownej optymalizacji. Plany wykonania są zapisywane w pamięci podręcznej w postaci skompilowanej na kod binarny. SQL Server Query Optymizer jest optymalizatorem kosztowym. Przypisuje on numer zwany kosztem do każdego planu. Większy koszt oznacza bardziej skomplikowany plan i co za tym idzie wolniejsze zapytanie. W teorii SQL Serwer powinien wygenerować wszystkie możliwe plany i wybrać ten który ma najmniejszy koszt. Ponieważ liczba możliwych planów rośnie wykładniczo wraz ze złożonością zapytania to wygenerowanie wszystkich planów nie zawsze jest możliwe. Optymalizator balansuje pomiędzy jakością plany i czasem potrzebnym do optymalizacji. Dlatego optymalizator nie gwarantuje, że zawsze wybrany jest najbardziej optymalny plan zapytania. SQL Serwer oblicza koszt operacji na podstawie algorytmu używanego przez operator fizyczny i szacunkowej liczby wierszy które muszą zostać przetworzone. Oszacowanie liczby wierszy jest również nazywany oszacowanie liczności (ang. cardinality estimation). Koszt wyraża wykorzystanie zasobów fizycznych, takich jak ilość operacji I/O, czas procesora i pamięć potrzebne do realizacji. Po wyliczeniu kosztu wszystkich operatorów w planie optymalizator oblicza koszt całego planu. Obliczanie kosztów z góry może być skomplikowane. Optymalizator potrzebuje informacji na temat szacowanej liczby wierszy przetwarzanych przez każdy operator. Optymalizator bierze te informacje ze statystyk. SQL Serwer zachowuje statystyki dotyczące całkowitej liczby wierszy i rozkładu liczby wierszy dla każdego indeksu. SQL Serwer może wygenerować statystyki dla każdej kolumny nawet jeżeli nie jest ona częścią indeksu. Można również generować i przechowywać statystyki manualnie. Zapis wybranego planu zapytania w pamięci podręcznej może przyspieszyć następne wykonywanie tego samego zapytania lub równoważnego z perspektywy wykonania zapytania. SQL Serwer parametryzuje zapytania tak aby mieć jeden plan zapytania  dla wielu równoważnych zapytań. Zapytania równoważne są zapytaniami które mogą być wykonane w ten sam sposób. Przykładowo poniższe dwa zapytania mogą używać tego samego planu zapytania:

SELECT col1 FROM TableA WHERE col2 = 3;
SELECT col1 FROM TableA WHERE col2 = 5;

SQL Serwer przetwarza zapytania jak te powyżej do postaci sparametryzowanej jak pseudo zapytanie poniżej:

SELECT col1 FROM TableA WHERE col2 = ?;

Oczywiście można pisać własne sparametryzowane zapytania w procedurach składowanych w których wykonaniu przekazujemy parametry. Można również użyć procedury systemowej sys.sp_executesql do parametryzacji zapytań ad hoc. Użycie procedur składowanych jest uważane za dobrą praktykę ponieważ auto-parametryzacja ma wiele ograniczeń które uniemożliwiają parametryzację wielu zapytań. SQL Serwer zapisuje plan wykonania osobno od aktualnej wartości. Dzięki temu może użyć tego samego planu wielokrotnie. Jednakże użycie zapisanego planu nie zawsze musi być najlepszym rozwiązaniem. Przykładowo liczba wierszy w tabeli może wzrosnąć gwałtownie a plan który zawiera skan tabeli który był dobry dla małej tabeli nie jest optymalny po wzroście liczby wierszy. Plany w pamięci podręcznej mogą stać się przestarzałe ponieważ metadane w bazie danych zmieniły się. Przykładowo do tabeli został dodany indeks lub ograniczenie. Optymalizator czasami musi odgadnąć szacowaną liczność ponieważ nie może wykryć jakie są parametry. Ten problem nazywa się parameter sniffing. Problem ten jest procesem w którym SQL Serwer próbuje zgadnąć lub wyśledzić aktualne wartości parametrów podczas kompilacji i wstawia je do optymalizatora. SQL Serwer robi wielką pracę w optymalizowaniu zapytań i zachowywaniu planów w pamięci podręcznej. Jednakże istnieje wiele możliwości w których coś pójdzie nie tak i najlepszy plan nie jest wybrany. Poniżej znajduje się lista takich scenariuszy:

  • Plan nie jest najlepszy ponieważ przestrzeń przeszukiwania planów była za duża.
  • Statystyki nie są aktualne lub ich nie ma wcale. Prowadzi to do błędnego oszacowania liczności.
  • Zapisany plan nie jest optymalny dla danego zestawu parametrów.
  • Parameter sniffing prowadzi do niedokładnego oszacowania liczności.
  • Optymalizator obniża lub zawyża koszt algorytmu operatora fizycznego.
  • Zmiana sprzętu może spowodować, że inny plan jest lepszy.

Ze względu na kompleksowość problemu więcej czynników może wpływać na plan wykonania. Aby zdać sobie sprawę co jest źle trzeba przeanalizować informacje na temat planów szacunkowych i aktualnego, użycia indeksów, statystyk itd. SQL Serwer dostarcza tych informacji na wiele sposobów.

SQL Server Extended Events

Każdy monitoring ma wpływ na wydajność monitorowanego systemu. Jeżeli monitorujesz system który ma problemy z wydajnością to możesz spowolnić go jeszcze bardziej. Oznacza to, że pożądamy jak najlżejszego systemu monitoringu. Extended Events jest bardzo lekkim systemem monitoringu. Korzystając z niego można porównać dane z serwera SQL z danymi systemu operacyjnego i aplikacjami. Cały system Extended Events jest dosyć skomplikowany. SQL Serwer 2012 posiada dwa narzędzia GUI do obsługi Extended Events - New Session Wizard i New Session UI. Dzięki nim można stworzyć śledzenie sesji i wykorzystać Extended Events bardzo szybko. Pakiet Extended Events jest kontenerem dla poniższych obiektów :

  • Events - są to obiekty zainteresowania do monitoringu. Można użyć zdarzeń do monitoringu lub wywoływać działania synchroniczne i asynchroniczne.
  • Targets - są to konsumenci zdarzeń. Można użyć obiektów docelowych zapisujących do pliku, zachowujących dane zdarzeń w pamięci lub agregujących dane zdarzeń. Mogą one przetwarzać dane synchronicznie i niesynchronicznie.
  • Actions - są odpowiedziami na zdarzenia. Są przypisywane do zdarzeń. Akcje mogą przechwycić zrzut stosu i sprawdzić dane, przechować informacje w zmiennej lokalnej, z agregować dane o zdarzeniach a nawet dołączyć dane do danych zdarzeń. Na przykład w SQL Server można użyć akcji detekcji planu wykonania do wykrywania planów wykonania.
  • Predicates - są to zbiory logicznych zasad służących do filtracji przechwyconych zdarzeń. Aby mieć jak najmniejszy wpływ na system poprzez działanie monitoringu należy przechwytywać tylko te zdarzenia których potrzebujemy.
  • Types - typy pomagają w interpretacji zebranych danych. Dane są kolekcją bajtów a typy dają tym danym kontekst. Istnieją typy dla zdarzeń, akcji, celów, predykatów i typów.
  • Maps - są to tabele które mapują wewnętrzne ciągi numeryczne na ciągi tekstowe które coś oznaczają.

Na bazie Extended Events działa również narzędzie SQL Server Audit  używane przez administratorów baz danych (DBA).

SQL Server Trace

SQL Trace jest mechanizmem SQL Serwera przechwytującym zdarzenia. W następnych wersjach SQL Trace będzie przestarzałe co oznacza, że w następnych wersjach będzie niezalecane lub usunięte. Trace można utworzyć za pomocą procedur systemowych. Można tworzyć je manualnie lub za pomocą narzędzia SQL Server Profiler. Źródłem dla zdarzeń śledzenia może być partia kodu T-SQL lub inne zdarzenia serwera jak np zakleszczenie. Po zaistnieniu określonego zdarzenia trace zbiera informacje o zdarzeniu. Informacje te są wstawiane do kolejki. Przed wstawieniem do kolejki zdarzenia są filtrowane w zależności od ustawionych filtrów. Z kolejki informacje mogą trafić do tabeli, pliku lub mogą być użyte przez aplikację.

SQL Server Profiler

SQL Server Profiler jest aplikacją UI dla SQL Trace. Używając SQL Server Profiler'a można zarządzać trace'ami i analizować ich wyniki. Można odtwarzać zdarzenia z zapisanego trace'a krok po kroku. Profiler pozwala również na wygenerowanie skryptu utworzenia trace'a. Niestety istnieją pewne wady korzystania z narzędzia SQL Server profiler:

  • Zwiększa się wpływ na wydajność instancji SQL Serwera w porównaniu do używania jedynie trace'a.
  • Narzędzie SQL Server Profiler zużywa pewną liczbę zasobów.
  • Kiedy używamy go zdalnie to wszystkie zdarzenia są przesyłane przez sieć.
  • Zamknięcie Profiler'a powoduje zamknięcie śledzenia.

Poniżej znajduje się lista terminów używanych zarówno przez Trace jak i Profiler:

  • Event - akcja SQL Serwera np start procedury lub błąd logowania użytkownika.
  • EventClass - typ zdarzenia definiujący dane które zdarzenie może raportować.
  • EventCategory - kategorie zdarzeń.
  • DataColumn - atrybut zdarzenia. Jeżeli zapisujemy wynik śledzenia do tabeli to  zdarzenie jest reprezentowane jako wiersz w tabeli a atrybuty zdarzenia są kolumnami tabeli.
  • Template - zapisana definicja śledzenia.
  • Filter - filtry limitują śledzone zdarzenia. Można je używać na każdej kolumnie zdarzenia.
  • Trace - kolekcja zdarzeń, kolumn, filtrów i zwróconych danych.

W następnych wersjach narzędzie SQL Server Profiler będzie przestarzałe co oznacza, że w następnych wersjach będzie niezalecane lub usunięte.

Ćwiczenia

I. Utworzenie sesji Extended Events dla instrukcji T-SQL.

  1. Utwórz poniższe zapytanie dla bazy TSQL2012 w SSMS: 
    SELECT C.custid
	,C.companyname
	,O.orderid
	,O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O ON C.custid = O.custid
ORDER BY C.custid
	,O.orderid;
  2. W SSMS Object Explorer rozwiń folder Managment. Rozwiń Extended Evets. Prawym guzikiem myszki kliknij na folder Session i wybierz New Session Wizard.
  3. Na stronie Set Session Properties zmień nazwę sesji na TSQL2012OrdersSession i przejdź dalej.
  4. Na stronie Choose Template wybierz Do Not Use A Template i przejdź dalej.
  5. Na stronie Select Events To Capture wpisz jako typ zdarzeń "SQL" w pole Event Library aby odfiltrowywać zdarzenia które posiadają ten ciąg znaków w ich nazwie. Wybierz zdarzenie sql_statement_completed i przeciągnij je do okna Selected Events i przejdź dalej. ExtendedEvents1
  6. Na stronie Capture Global Fields przejrzyj akcje które są wspólne dla wszystkich zdarzeń. Poszczególne ważne pola są wybrane automatycznie dla poszczególnych zdarzeń więc nie trzeba zaznaczać niczego.
  7. Na stronie Set Session Event Filters stwórz filtr który filtruje nazwę bazy danych TSQL2012 poprzez wybór filtru sqlserver.database_name operatora "=" i wartości TSQL2012. Użyj filtru sqlserver.sql_text z operatorem like_i_sql_unicode_string i wartością "SELECT C.custid, C.companyname%". Filtr powinien wyglądać jak na obrazku: ExtendedEvents2
  8. Na stronie Specify Session Data Storage wybierz Work With Only The Most Recent Data (Ring_Buffer Target) i przejdź dalej.
  9. Na stronie Summary przejrzyj podsumowanie i kliknij zakończ.
  10. Na ostatniej stronie Create Event Session wybierz oba dostępne checkboxy: Start the event session immediately after session creation i Watch the live data on the screen as it is captured. Kliknij zamknij po czym powinno pojawić się okno Extended Events Live Data

II. Praca z sesją Extended Evenst

  1. Przełącz okno na zapytanie utworzone w punkcie pierwszym poprzedniego ćwiczenia.
  2. Wykonaj zapytanie.
  3. Przełącz się z powrotem do okna sesji Extended Events i obejrzyj wyniki.
  4. Zamknij okno Extended Events Live Data.
  5. W SSMS Object Explorer rozwiń folder Sessions z folderu Extended Events. W drzewie znajduje sie utworzona sesja oraz inne zdefiniowane wcześniej
  6. Utwórz skrypt z utworzonej sesji poprzez kliknięcie na niej ppm i użycie opcji Script Session as -> Create to -> New Query Editor Window. Przejrzyj instrukcję tworzenia sesji.
  7. Usuń sesję.

Podsumowanie

  1. Optymalizator zapytań tworzy kandydatów na plany zapytań i przelicza ich szacowany koszt.
  2. SQL Serwer posiada wiele narzędzi pomagających analizować zapytania np: Extended Events, SQL Trace, i SQL Server Profiler
  3. Extended Events jest lżejszym mechanizmem niż SQL Trace.
  4. SQL Server Profiler jest nakładką UI na SQL Trace