Allgemeine Informationen zu geschätzten und tatsächlichen Abfrageplänen

Abgeschlossen

Tatsächliche im Vergleich zu geschätzten Ausführungsplänen können verwirrend sein. Ein Unterschied besteht darin, dass der tatsächliche Plan im Gegensatz zum geschätzten Plan Laufzeitstatistiken enthält. Die verwendeten Operatoren und die Reihenfolge der Ausführung entsprechen dem geschätzten Plan in fast allen Fällen. Ein weiterer Aspekt ist, dass das Erfassen eines tatsächlichen Ausführungsplans erfordert, dass die Abfrage ausgeführt werden muss, was zeitaufwändig oder nicht möglich sein kann. Beispielsweise kann eine UPDATE Anweisung nur einmal ausgeführt werden. Wenn Sie jedoch Abfrageergebnisse und den Plan anzeigen müssen, müssen Sie eine der tatsächlichen Planoptionen verwenden.

Wie gezeigt, können Sie einen geschätzten Plan in SSMS generieren, indem Sie die Schaltfläche auswählen, die durch das Feld für den geschätzten Abfrageplan (oder mithilfe des Tastaturbefehls CTRL+L) angegeben ist. Sie können den tatsächlichen Plan generieren, indem Sie das angezeigte Symbol auswählen (oder den Tastaturbefehl CTRL+M verwenden), und dann die Abfrage ausführen. Die beiden Optionsfelder funktionieren unterschiedlich. Die Schaltfläche " Geschätzter Abfrageplan einschließen " antwortet sofort auf alle hervorgehobenen Abfragen (oder den gesamten Arbeitsbereich, wenn nichts hervorgehoben ist), während die Schaltfläche "Ist-Abfrageplan einschließen " die Abfrage ausführen muss.

Sowohl für das Ausführen einer Abfrage, als auch für das Erstellen eines geschätzten Ausführungsplans entsteht ein Mehraufwand. Das Anzeigen von Ausführungsplänen in einer Produktionsumgebung sollte also wohl überlegt geschehen.

Normalerweise können Sie den geschätzten Ausführungsplan verwenden, während Sie Ihre Abfrage schreiben, um die Leistungsmerkmale zu verstehen, fehlende Indizes zu identifizieren oder Abfrageanomalien zu erkennen. Der tatsächliche Ausführungsplan wird am besten verwendet, um die Laufzeitleistung der Abfrage zu verstehen, und vor allem Lücken in statistischen Daten, die dazu führen, dass der Abfrageoptimierer suboptimale Entscheidungen basierend auf den verfügbaren Daten treffen kann.

Lesen eines Abfrageplans

In Ausführungsplänen sehen Sie, welche Tasks von der Datenbank-Engine ausgeführt werden, während die Daten abgerufen werden, die zum Erfüllen einer Abfrage erforderlich sind. Sehen Sie sich den Plan im Detail an.

SELECT [stockItemName]
 ,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
 ,[QuantityonHand]

FROM [Warehouse].[StockItems] s
 JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;

Diese Abfrage verknüpft die Tabelle StockItems mit der Tabelle StockItemHoldings. Die Werte in der Spalte StockItemID sind gleich. Die Datenbank-Engine muss zunächst diese Zeilen ermitteln, bevor die restliche Abfrage verarbeitet werden kann.

Jedes Symbol im Plan stellt einen bestimmten Vorgang dar, der den verschiedenen Aktionen und Entscheidungen entspricht, die einen Ausführungsplan bilden. Das SQL Server-Datenbankmodul verfügt über mehr als 100 Abfrageoperatoren, die Teil eines Ausführungsplans sein können. Unter jedem Operatorsymbol gibt es einen Kostenprozentsatz im Verhältnis zu den Gesamtkosten der Abfrage. Selbst ein Vorgang mit den Kosten von 0% stellt weiterhin einige Kosten dar. Tatsächlich ist 0% auf das Runden zurückzuführen, da grafische Plankosten immer als ganze Zahlen angezeigt werden und der reale Prozentsatz etwas kleiner als 0,5%ist.

Der Ausführungsfluss in einem Ausführungsplan liegt von rechts nach links und von oben nach unten, daher ist in diesem Plan der Clustered Index Scan-Vorgang im StockItemHoldings.PK_Warehouse_StockItemHoldings gruppierter Index der erste Vorgang in der Abfrage. Die Strichstärke der Linien, die die Operatoren verbinden, basieren jeweils auf der geschätzten Anzahl Zeilen für den Datenfluss in Richtung des nächsten Operators. Ein dicker Pfeil ist ein Indikator für eine Übertragung großer Datenmengen von Operator zu Operator und weist möglicherweise auf eine Gelegenheit hin, eine Abfrage zu optimieren. Sie können die Maus auch über einen Operator halten und zusätzliche Informationen in einer QuickInfo anzeigen.

Die QuickInfo hebt die Kosten und Schätzungen für den geschätzten Plan hervor, und für einen tatsächlichen Plan enthält sie Vergleiche mit den tatsächlichen Zeilen und Kosten. Jeder Operator verfügt auch über Eigenschaften, die mehr Details als die QuickInfo bereitstellen. Wenn Sie mit der rechten Maustaste auf einen bestimmten Operator klicken, können Sie im Kontextmenü die Option "Eigenschaften" auswählen, um die vollständige Eigenschaftenliste anzuzeigen. Mit dieser Option wird ein separater Eigenschaftenbereich in SQL Server Management Studio geöffnet, der sich standardmäßig auf der rechten Seite befindet. Sobald der Eigenschaftenbereich geöffnet ist, füllt jeder Operator die Liste "Eigenschaften" mit Details für diesen Operator auf. Alternativ können Sie den Eigenschaftenbereich öffnen, indem Sie im Sql Server Management Studio-Hauptmenü die Option "Ansicht" und dann "Eigenschaften" auswählen.

Der Eigenschaftenbereich enthält zusätzliche Informationen und zeigt die Ausgabeliste an, wobei die spalten, die an den nächsten Operator übergeben werden. Diese Spalten können darauf hinweisen, dass ein nicht gruppierter Index erforderlich ist, um die Abfrageleistung zu verbessern, wenn sie mit einer gruppierten Indexüberprüfung analysiert wird. Da ein Gruppierter Indexscanvorgang die gesamte Tabelle liest, könnte ein nicht gruppierter Index in der Spalte StockItemID in jeder Tabelle in diesem Szenario effizienter sein.

Einfache Abfrageprofilerstellung

Wenn Sie tatsächliche Ausführungspläne generieren, unabhängig davon, ob sie SSMS oder die Überwachungsinfrastruktur für erweiterte Ereignisse verwenden, kann ein erheblicher Aufwand entstehen. Daher ist dieser Prozess in der Regel für Die Problembehandlung bei Livewebsites reserviert. Bei Beobachtungsmehraufwand handelt es sich um die Kosten für die Überwachung einer Anwendung, die ausgeführt wird. In einigen Szenarien kann diese Kosten nur ein paar Prozentpunkte der CPU-Auslastung sein, aber in anderen Fällen, z. B. beim Erfassen tatsächlicher Ausführungspläne, kann die Leistung einzelner Abfragen erheblich verlangsamt werden. Die Legacyprofilerstellung im Modul von SQL Server kann bis zu 75% Mehraufwand für die Erfassung von Abfrageinformationen verursachen, während die einfache Profilerstellung einen maximalen Mehraufwand von ca. 2%hat.

In der ersten Version wurden bei der Lightweightprofilerstellung die Anzahl der Zeilen sowie Informationen zur E/A-Auslastung erfasst, also die Anzahl logischer und physischer Lese- und Schreibvorgänge, die von der Datenbank-Engine für die Erfüllung einer gegebenen Abfrage durchgeführt wurden. Darüber hinaus wurde ein neues erweitertes Ereignis namens query_thread_profile eingeführt, um daten von jedem Operator in einem Abfrageplan zu prüfen. In der ersten Version der Lightweightprofilerstellung musste für die Verwendung des Features das Ablaufverfolgungsflag 7412 global aktiviert sein.

Wenn die einfache Profilerstellung nicht global aktiviert ist, können Sie den USE HINT Abfragehinweis QUERY_PLAN_PROFILE verwenden, um einfache Profilerstellung auf Abfrageebene zu ermöglichen. Wenn eine Abfrage mit diesem Hinweis die Ausführung abgeschlossen hat, wird ein query_plan_profile erweitertes Ereignis generiert und stellt einen tatsächlichen Ausführungsplan bereit. Hier ist ein Beispiel für eine Abfrage mit diesem Hinweis:

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'));

Statistik zu den letzten Abfrageplänen

Einfache Profilerstellung ist standardmäßig sowohl in SQL Server 2019 als auch in der Azure SQL-Datenbank und in der verwalteten Instanz aktiviert. Die Lightweightprofilerstellung ist auch als Konfigurationsoption für Datenbanken verfügbar. Hier wird sie als LIGHTWEIGHT_QUERY_PROFILING bezeichnet. Bei der datenbankbezogenen Optionen können Sie das Feature für jede Ihrer Benutzerdatenbanken unabhängig deaktivieren.

Außerdem gibt es eine dynamische Verwaltungsfunktion sys.dm_exec_query_plan_statsnamens , die Ihnen den letzten bekannten tatsächlichen Abfrageausführungsplan für einen bestimmten Planhandle anzeigen kann. Damit der letzte bekannte tatsächliche Ausführungsplan mithilfe der Funktion angezeigt wird, können Sie das Ablaufverfolgungsflag 2451 für den gesamten Server aktivieren. Alternativ können Sie diese Funktion mithilfe der datenbankbezogenen Konfigurationsoption LAST_QUERY_PLAN_STATS aktivieren.

Sie können diese Funktion mit anderen Objekten kombinieren, um den letzten Ausführungsplan für alle zwischengespeicherten Abfragen abzurufen:

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

Diese Funktion ermöglicht es Ihnen, innerhalb kurzer Zeit Runtimestatistiken für die letzte Ausführung einer beliebigen Abfrage in Ihrem System zu ermitteln. Der dabei entstehende Mehraufwand ist minimal. Die folgende Abbildung zeigt, wie Sie den Plan abrufen. Wenn Sie den XML-Ausführungsplan auswählen, der die erste Spalte der Ergebnisse ist, wird der Ausführungsplan in der zweiten Abbildung unten angezeigt.

Wie Sie aus den Eigenschaften der Columnstore Index Scan in der folgenden Abbildung sehen können, weist der aus dem Cache abgerufene Plan die tatsächliche Anzahl von Zeilen auf, die in der Abfrage abgerufen wurden.