Omówienie planów zapytań

Ukończone

Zrozumienie, jak działają optymalizatory baz danych, jest niezbędne przed zapoznaniem się ze szczegółami planu wykonywania. Program SQL Server używa optymalizatora zapytań opartych na kosztach, który oblicza koszt wielu możliwych planów na podstawie statystyk używanych kolumn i potencjalnych indeksów dla każdej operacji w planie zapytania. Te informacje pomagają optymalizatorowi określić całkowity koszt dla każdego planu. Złożone zapytania mogą mieć tysiące możliwych planów wykonywania, ale optymalizator nie ocenia każdego z nich. Zamiast tego używa heurystyki do identyfikowania planów, które mogą działać dobrze, a następnie wybiera najniższy plan kosztów od tych ocenianych.

Ponieważ optymalizator zapytań jest oparty na kosztach, kluczowe jest zapewnienie mu dokładnych danych wejściowych do podejmowania decyzji. Program SQL Server opiera się na statystykach śledzenia rozkładu danych w kolumnach i indeksach, a te statystyki muszą być aktualne, aby uniknąć generowania nieoptymalnych planów wykonywania. Mimo że program SQL Server automatycznie aktualizuje statystyki w miarę zmian danych w tabeli, częstsze aktualizacje mogą być konieczne do szybkiego zmieniania danych. Optymalizator uwzględnia wiele czynników podczas tworzenia planu, w tym poziom zgodności bazy danych, oszacowania wierszy na podstawie statystyk i dostępnych indeksów.

Gdy użytkownik przesyła zapytanie do aparatu bazy danych, następuje następujący proces:

1. Zapytanie jest analizowane pod kątem prawidłowej składni, a jeśli jest to poprawne, jest generowane drzewo analizy obiektów bazy danych.
2. Drzewo analizy jest następnie wprowadzane do składnika silnika bazy danych o nazwie Algebrizer w celu powiązania. Ten krok sprawdza, czy kolumny i obiekty w zapytaniu istnieją i identyfikuje przetwarzane typy danych. Dane wyjściowe to drzewo procesora zapytań, które służy jako dane wejściowe dla następnego kroku.
3. Optymalizacja zapytań jest wymagająca dla procesora, więc aparatura bazy danych buforuje plany wykonywania w specjalnym obszarze pamięci nazywanym pamięcią podręczną planu. Jeśli plan zapytania już istnieje, jest on pobierany z pamięci podręcznej. Każde zapytanie w pamięci podręcznej ma wartość skrótu wygenerowaną na podstawie języka T-SQL w zapytaniu znanym jako query_hash. Silnik generuje query_hash dla bieżącego zapytania i sprawdza dopasowania w pamięci podręcznej planów.
4. Jeśli żaden plan nie istnieje, optymalizator zapytań używa optymalizatora opartego na kosztach do generowania kilku opcji planu wykonywania na podstawie statystyk dotyczących kolumn, tabel i indeksów używanych w zapytaniu. Dane wyjściowe to plan wykonywania zapytania.
5. Zapytanie jest wykonywane przy użyciu planu wykonania z pamięci podręcznej planu lub nowego planu wygenerowanego w poprzednim kroku. Dane wyjściowe to wyniki zapytania.

Uwaga

Aby dowiedzieć się więcej o sposobie działania procesora zapytań, zobacz Przewodnik po architekturze przetwarzania zapytań

Spójrzmy na przykład. Rozważ następujące zapytanie:

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

W tym przykładzie program SQL Server sprawdza istnienie kolumn OrderDate, ShipDate i SalesAmount w tabeli FactResellerSales . Jeśli te kolumny istnieją, SQL Server generuje wartość skrótu dla zapytania i sprawdza bufor planu dla pasującej wartości skrótu. Jeśli zostanie znaleziona zgodna wartość skrótu, silnik próbuje ponownie użyć planu. Jeśli nie znaleziono pasującej wartości skrótu, program SQL Server sprawdza dostępne statystyki dotyczące kolumn OrderDate i ShipDate .

Klauzula WHERE odwołująca się do kolumny ShipDate jest znana jako predykat w tym zapytaniu. Jeśli istnieje indeks nieklastrowany, który zawiera kolumnę ShipDate , program SQL Server prawdopodobnie uwzględni go w planie, pod warunkiem, że koszty są niższe niż pobieranie danych z indeksu klastrowanego. Optymalizator wybiera najniższy plan kosztów z dostępnych opcji i wykonuje zapytanie.

Plany zapytań łączą szereg operatorów relacyjnych w celu pobierania danych i przechwytywania informacji, takich jak szacowane liczby wierszy. Innym elementem planu wykonywania jest pamięć wymagana dla operacji, takich jak łączenie lub sortowanie danych, nazywanych udzielaniem pamięci. Przyznawanie pamięci podkreśla znaczenie statystyk. Jeśli program SQL Server szacuje, że operator zwraca 10 000 000 wierszy, gdy faktycznie zwraca 100, większe przyznanie pamięci zostanie przydzielone do zapytania. Nadmiernie duża ilość pamięci może powodować dwa problemy. Najpierw zapytanie może napotkać RESOURCE_SEMAPHORE oczekiwanie, wskazując, że oczekuje na przydzielenie dużej ilości pamięci przez program SQL Server. SQL Server domyślnie czeka przez czas równy 25-krotności kosztu zapytania (w sekundach) przed jego wykonaniem, maksymalnie do 24 godzin. Po drugie, jeśli podczas wykonywania zapytania nie ma wystarczającej ilości dostępnej pamięci, jest ona przeniesiona do bazy danych tempdb, co jest wolniejsze niż operowanie w pamięci.

Plan wykonywania przechowuje również inne metadane dotyczące zapytania, takie jak poziom zgodności bazy danych, stopień równoległości i parametry podane, jeśli zapytanie jest sparametryzowane.

Plany zapytań można wyświetlać w postaci graficznej reprezentacji lub formatu tekstowego. Opcje oparte na tekście są wywoływane za pomocą poleceń SET i mają zastosowanie tylko do bieżącego połączenia. Te plany można wyświetlać w dowolnym miejscu, w którym można uruchamiać zapytania T-SQL.

Większość administratorów baz danych preferuje plany graficzne, ponieważ pozwalają one zobaczyć plan jako całość, w tym kształt planu. Istnieje kilka sposobów wyświetlania i zapisywania graficznych planów zapytań. Najbardziej typowym narzędziem do tego celu jest program SQL Server Management Studio. Ponadto istnieją narzędzia innych firm, które obsługują wyświetlanie graficznych planów wykonywania.

Istnieją trzy różne typy planów wykonywania.

Szacowany plan wykonania

Ten typ planu wykonywania jest generowany przez optymalizator zapytań. Metadane i rozmiar przydziału pamięci zapytania są oparte na oszacowaniach statystyk znajdujących się w bazie danych w czasie kompilacji zapytań. Aby wyświetlić szacowany plan oparty na tekście, uruchom polecenie SET SHOWPLAN_ALL ON przed wykonaniem zapytania. Po uruchomieniu zapytania zobaczysz kroki planu wykonania, ale zapytanie nie zostanie wykonane i nie będą widoczne żadne wyniki. Opcja SET pozostaje w mocy, dopóki nie zostanie ustawiona.

Rzeczywisty plan wykonania

Ten typ planu jest taki sam jak szacowany plan; jednak zawiera również kontekst wykonywania zapytania. Ten kontekst zawiera szacowane i rzeczywiste liczby wierszy, wszelkie ostrzeżenia dotyczące wykonywania, rzeczywisty stopień równoległości (liczba używanych procesorów) oraz czas, który upłynął i czas użycia procesora CPU podczas wykonywania. Aby wyświetlić rzeczywisty plan oparty na tekście, uruchom polecenie SET STATISTICS PROFILE ON przed wykonaniem zapytania. Zapytanie zostaje wykonane, a Ty otrzymujesz zarówno plan, jak i wyniki.

Statystyki zapytań na żywo

Ta opcja wyświetlania planu łączy szacowane i rzeczywiste plany w animowany plan, który wyświetla postęp wykonywania przez operatory. Jest odświeżany co sekundę i pokazuje rzeczywistą liczbę wierszy przepływających przez operatory. Kolejną zaletą statystyk zapytań na żywo jest to, że pokazuje przekazywanie operacji między operatorami, co może być przydatne w rozwiązywaniu problemów z wydajnością. Ponieważ ten typ planu jest animowany, jest dostępny tylko jako plan graficzny.