Wyjaśnienie szacowanych i rzeczywistych planów zapytań
Rzeczywiste i szacowane plany wykonania mogą być mylące. Różnica polega na tym, że rzeczywisty plan obejmuje statystyki środowiska uruchomieniowego, które nie są przechwytywane w szacowany plan. Używane operatory i kolejność wykonywania będą takie same jak szacowany plan w prawie wszystkich przypadkach. Innym zagadnieniem jest to, że przechwytywanie rzeczywistego planu wykonania wymaga wykonania zapytania, co może być czasochłonne lub niemożliwe. Na przykład instrukcja UPDATE może być uruchamiana tylko raz. Jeśli jednak musisz zobaczyć wyniki zapytania i plan, musisz użyć jednej z rzeczywistych opcji planu.
Jak pokazano, możesz wygenerować szacowany plan w programie SSMS, wybierając przycisk wskazany w polu szacowanego planu zapytania (lub za pomocą Control+L). Możesz wygenerować rzeczywisty plan, wybierając wyświetloną ikonę (lub używając klawiatury Control+M), a następnie wykonując zapytanie. Dwa przyciski opcji działają inaczej. Przycisk Uwzględnij szacowany plan zapytania natychmiast odpowiada na dowolne wyróżnione zapytanie (lub cały obszar roboczy, jeśli nic nie zostało wyróżnione), natomiast przycisk Uwzględnij rzeczywisty plan zapytania wymaga wykonania zapytania.
Istnieje obciążenie związane zarówno z wykonywaniem zapytania, jak i generowaniem szacowanego planu wykonania, więc wyświetlanie planów wykonania powinno być dokładnie wykonywane w środowisku produkcyjnym.
Zazwyczaj można użyć szacowany plan wykonywania podczas pisania zapytania, aby zrozumieć jego charakterystykę wydajności, zidentyfikować brakujące indeksy lub wykryć anomalie zapytań. Rzeczywisty plan wykonania najlepiej jest użyć do zrozumienia wydajności środowiska uruchomieniowego zapytania i, co najważniejsze, luk w danych statystycznych, które powodują optymalizator zapytań, aby dokonać nieoptymalnych wyborów na podstawie dostępnych danych.
Odczytywanie planu zapytania
Plany wykonywania pokazują, jakie zadania wykonuje aparat bazy danych podczas pobierania danych wymaganych do spełnienia zapytania. Przyjrzyjmy się planowi.
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;
To zapytanie łączy tabelę StockItems z tabelą StockItemHoldings , gdzie wartości w kolumnie StockItemID są równe. Aparat bazy danych musi najpierw zidentyfikować te wiersze, zanim będzie mógł przetworzyć resztę zapytania.
Każda ikona w planie reprezentuje określoną operację, która odpowiada różnym akcjom i decyzjom, które tworzą plan wykonania. Aparat bazy danych programu SQL Server ma ponad 100 operatorów zapytań, które mogą być częścią planu wykonywania. W obszarze każdej ikony operatora istnieje procent kosztów względem całkowitego kosztu zapytania. Nawet operacja przedstawiająca koszt 0% nadal reprezentuje jakiś koszt. W rzeczywistości 0% wynika z zaokrąglania, ponieważ koszty planu graficznego są zawsze wyświetlane jako liczby całkowite, a rzeczywista wartość procentowa jest mniejsza niż 0,5%.
Przepływ wykonywania w planie wykonywania jest od prawej do lewej i od góry do dołu, więc w tym planie operacja skanowania indeksu klastrowanego na indeksie klastrowanym StockItemHoldings.PK_Warehouse_StockItemHoldings jest pierwszą operacją w zapytaniu. Szerokości wierszy łączących operatory są oparte na szacowanej liczbie wierszy danych, które przepływają dalej do następnego operatora. Gruba strzałka jest wskaźnikiem dużego operatora do przeniesienia operatora i może wskazywać na możliwość dostosowania zapytania. Możesz również trzymać wskaźnik myszy nad operatorem i wyświetlić dodatkowe informacje w etykietce narzędzia.
Etykietka narzędzia wyróżnia koszt i oszacowania dla szacowanego planu oraz rzeczywisty plan zawiera porównania z rzeczywistymi wierszami i kosztami. Każdy operator ma również właściwości, które zawierają więcej szczegółów niż etykietka narzędzia. Klikając prawym przyciskiem myszy określony operator, możesz wybrać opcję Właściwości z menu kontekstowego, aby wyświetlić pełną listę właściwości. Ta opcja otwiera oddzielne okienko Właściwości w programie SQL Server Management Studio, które domyślnie znajduje się po prawej stronie. Po otwarciu okienka Właściwości wybranie dowolnego operatora spowoduje wypełnienie listy Właściwości szczegółami tego operatora. Możesz też otworzyć okienko Właściwości, wybierając pozycję Widok w głównym menu programu SQL Server Management Studio i wybierając pozycję Właściwości.
Okienko Właściwości zawiera dodatkowe informacje i wyświetla listę danych wyjściowych, szczegółowo kolumny przekazywane do następnego operatora. Te kolumny mogą wskazywać, że indeks nieklastrowany jest potrzebny do zwiększenia wydajności zapytań podczas analizowania przy użyciu skanowania indeksu klastrowanego. Ponieważ operacja skanowania indeksu klastrowanego odczytuje całą tabelę, indeks nieklastrowany w kolumnie StockItemID w każdej tabeli może być bardziej wydajny w tym scenariuszu.
Uproszczone profilowanie zapytań
Podczas generowania rzeczywistych planów wykonywania, niezależnie od tego, czy korzystasz z programu SSMS, czy infrastruktury monitorowania zdarzeń rozszerzonych, może to spowodować znaczne obciążenie. W związku z tym ten proces jest zwykle zarezerwowany na potrzeby rozwiązywania problemów z witryną na żywo. Obciążenie obserwatora, jak wiadomo, jest kosztem monitorowania uruchomionej aplikacji. W niektórych scenariuszach ten koszt może wynosić tylko kilka punktów procentowych wykorzystania procesora CPU, ale w innych przypadkach, takich jak przechwytywanie rzeczywistych planów wykonywania, może znacznie spowolnić wydajność poszczególnych zapytań. Starsze profilowanie w a aparatze programu SQL Server może wygenerować do 75% narzut na potrzeby przechwytywania informacji o zapytaniach, podczas gdy uproszczone profilowanie ma maksymalne obciążenie około 2%.
W pierwszej wersji uproszczonego profilowania zebrano informacje o liczbie wierszy i wykorzystaniu operacji we/wy (liczba odczytów logicznych i fizycznych i zapisów wykonywanych przez aparat bazy danych w celu spełnienia wymagań danego zapytania). Ponadto wprowadzono nowe zdarzenie rozszerzone o nazwie query_thread_profile , aby umożliwić inspekcję danych z każdego operatora w planie zapytania. W początkowej wersji uproszczonego profilowania użycie funkcji wymaga włączenia flagi śledzenia 7412 globalnie.
Jeśli uproszczone profilowanie nie jest włączone globalnie, możesz użyć USE HINT wskazówki dotyczącej zapytania z poleceniem QUERY_PLAN_PROFILE , aby włączyć uproszczone profilowanie na poziomie zapytania. Gdy zapytanie z tą wskazówką zakończy wykonywanie, generowany jest query_plan_profile zdarzenie rozszerzone, zapewniając rzeczywisty plan wykonania. Oto przykład zapytania z tą wskazówką:
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[ QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItems] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox
OPTION(USE HINT ('QUERY_PLAN_PROFILE'));
Statystyki ostatnich planów zapytań
Uproszczone profilowanie jest domyślnie włączone zarówno w programie SQL Server 2019, jak i usłudze Azure SQL Database oraz w wystąpieniu zarządzanym. Uproszczone profilowanie jest również dostępne jako opcja konfiguracji w zakresie bazy danych o nazwie LIGHTWEIGHT_QUERY_PROFILING. Za pomocą opcji o określonym zakresie bazy danych można wyłączyć funkcję dla dowolnych baz danych użytkowników niezależnie od siebie.
Ponadto istnieje funkcja dynamicznego zarządzania o nazwie sys.dm_exec_query_plan_stats, która umożliwia wyświetlenie ostatniego znanego rzeczywistego planu wykonywania zapytania dla danego dojścia planu. Aby wyświetlić ostatni znany rzeczywisty plan zapytania za pośrednictwem funkcji, można włączyć flagę śledzenia 2451 dla całego serwera. Alternatywnie można włączyć tę funkcję przy użyciu opcji konfiguracji o określonym zakresie bazy danych o nazwie LAST_QUERY_PLAN_STATS.
Tę funkcję można połączyć z innymi obiektami, aby uzyskać ostatni plan wykonywania dla wszystkich buforowanych zapytań:
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO
Ta funkcja umożliwia szybkie identyfikowanie statystyk środowiska uruchomieniowego dla ostatniego wykonania dowolnego zapytania w systemie przy minimalnym nakładzie pracy. Na poniższej ilustracji przedstawiono sposób pobierania planu. Jeśli wybierzesz kod XML planu wykonania, który będzie pierwszą kolumną wyników, zostanie wyświetlony plan wykonania pokazany na drugim obrazie poniżej.
Jak widać na podstawie właściwości skanowania indeksu magazynu kolumn na poniższej ilustracji, plan pobrany z pamięci podręcznej zawiera rzeczywistą liczbę wierszy pobranych w zapytaniu.
