sys.dm_exec_text_query_plan (Transact-SQL)
Gilt für:
SQL Server Azure SQL-Datenbank Azure SQL verwaltete Instanz
Gibt den Showplan im Textformat für einen Transact-SQL-Batch oder für eine bestimmte Anweisung innerhalb des Batches zurück. Der vom Planhandle angegebene Abfrageplan kann entweder zwischengespeichert oder zurzeit ausgeführt werden. Diese Tabellenwertfunktion ähnelt sys.dm_exec_query_plan (Transact-SQL), weist aber folgende Unterschiede auf:
- Die Ausgabe des Abfrageplans wird im Textformat zurückgegeben.
- Die Größe der Ausgabe des Abfrageplans ist nicht beschränkt.
- Im Batch können einzelne Anweisungen angegeben werden.
Gilt für: SQL Server (SQL Server 2008 (10.0.x) und höher), Azure SQL-Datenbank
Transact-SQL-Syntaxkonventionen
Syntax
sys.dm_exec_text_query_plan
(
plan_handle
, { statement_start_offset | 0 | DEFAULT }
, { statement_end_offset | -1 | DEFAULT }
)
Argumente
plan_handle
Ein Token, das einen Abfrageausführungsplan für einen Batch eindeutig identifiziert, der ausgeführt wurde und dessen Plan sich im Plancache befindet, oder der derzeit ausgeführt wird. plan_handle ist varbinary(64)
plan_handle kann aus den folgenden dynamischen Verwaltungsobjekten abgerufen werden:
statement_start_offset | 0 | DEFAULT
Gibt die Startposition der Abfrage an (in Bytes), die von der Zeile im Text des zugehörigen Batches oder persistenten Objekts beschrieben wird. statement_start_offset ist int. Der Wert 0 gibt den Anfang des Batches an. Der Standardwert ist 0.
Der Startoffset der Anweisung kann aus den folgenden dynamischen Verwaltungsobjekten abgerufen werden:
statement_end_offset | -1 | DEFAULT
Gibt (in Bytes) die Endposition der Abfrage an, die die Zeile innerhalb des Texts des Batches oder des persistenten Objekts beschreibt.
statement_start_offset ist int.
Der Wert -1 gibt das Ende des Batches an. Der Standardwert ist -1.
Zurückgegebene Tabelle
| Spaltenname | Datentyp | Beschreibung |
|---|---|---|
| dbid | smallint | ID der Kontextdatenbank, die gültig war, als die diesem Plan entsprechende Transact-SQL-Anweisung kompiliert wurde. Für Ad-hoc-Anweisungen und vorbereitete SQL-Anweisungen, die ID der Datenbank, in der die Anweisungen kompiliert wurden. Die Spalte lässt NULL-Werte zu. |
| objectid | int | ID des Objekts (z. B. gespeicherte Prozedur oder benutzerdefinierte Funktion) für diesen Abfrageplan. Für Ad-hoc- und vorbereitete Batches entspricht diese Spalte dem Wert NULL. Die Spalte lässt NULL-Werte zu. |
| Zahl | smallint | Gespeicherte Prozedur mit ganzer Zahl. Eine Gruppe von Prozeduren für die orders -Anwendung kann z. B. die Namen orderproc;1, orderproc;2usw. haben. Für Ad-hoc- und vorbereitete Batches entspricht diese Spalte dem Wert NULL. Die Spalte lässt NULL-Werte zu. |
| encrypted | bit | Zeigt an, ob die entsprechende Prozedur verschlüsselt ist. 0 = nicht verschlüsselt 1 = verschlüsselt NULL-Werte sind in der Spalte nicht zulässig. |
| query_plan | nvarchar(max) | Enthält eine zur Kompilierzeit erstellte Showplandarstellung des Abfrageausführungsplans, der mit plan_handleangegeben ist. Der Showplan liegt im Textformat vor. Für jeden Batch, der z. B. Ad-hoc-Transact-SQL-Anweisungen, Aufrufe von gespeicherten Prozeduren sowie benutzerdefinierte Funktionsaufrufe enthält, wird jeweils ein Plan generiert. Die Spalte lässt NULL-Werte zu. |
Hinweise
Unter den folgenden Bedingungen wird keine Showplan-Ausgabe in der Planspalte der zurückgegebenen Tabelle für sys.dm_exec_text_query_plan zurückgegeben:
Falls der mit plan_handle angegebene Abfrageplan aus dem Plancache entfernt wurde, enthält die query_plan -Spalte der zurückgegebenen Tabelle den Wert NULL. Diese Bedingung kann z. B. auftreten, wenn zwischen dem Zeitpunkt der Erfassung des Planhandles und der Verwendung mit sys.dm_exec_text_query_plan eine Zeitverzögerung besteht.
Einige Transact-SQL -Anweisungen werden nicht zwischengespeichert. Beispiele hierfür sind Anweisungen für Massenvorgänge oder Anweisungen mit Zeichenfolgenliteralen, die größer als 8 KB sind. Showplans für solche Anweisungen können nicht mithilfe von sys.dm_exec_text_query_plan abgerufen werden, da sie nicht im Cache vorhanden sind.
Wenn ein Transact-SQL-Batch oder eine gespeicherte Prozedur einen Aufruf für eine benutzerdefinierte Funktion oder einen Aufruf für eine dynamische SQL-Anweisung z. B. mit EXEC (string) enthält, ist der kompilierte XML-Showplan für die benutzerdefinierte Funktion nicht in der Tabelle enthalten, die von sys.dm_exec_query_plan für den Batch oder die gespeicherte Prozedur zurückgegeben wird. Stattdessen müssen Sie einen separaten Aufruf von sys.dm_exec_text_query_plan für das Planhandle erstellen, das der benutzerdefinierten Funktion entspricht.
Wenn bei einer Ad-hoc-Abfrage einfache oder erzwungene Parametrisierung verwendet wird, enthält die Spalte query_plan nur den Anweisungstext, nicht den tatsächlichen Abfrageplan. Rufen Sie zum Zurückgeben des Abfrageplans sys.dm_exec_text_query_plan für das Planhandle der vorbereiteten parametrisierten Abfrage auf. Sie können anhand der sql -Spalte der sys.syscacheobjects -Sicht oder anhand der Textspalte der dynamischen sys.dm_exec_sql_text -Verwaltungssicht ermitteln, ob die Abfrage parametrisiert wurde.
Berechtigungen
Um sys.dm_exec_text_query_plan auszuführen, muss ein Benutzer Mitglied der festen Serverrolle "sysadmin" sein oder über die VIEW SERVER STATE-Berechtigung auf dem Server verfügen.
Berechtigungen für SQL Server 2022 und höher
Erfordert die VIEW SERVER PERFORMANCE STATE-Berechtigung auf dem Server.
Beispiele
A. Abrufen des zwischengespeicherten Abfrageplans für eine langsam ausgeführte Transact-SQL-Abfrage oder einen langsam ausgeführten Transact-SQL-Batch
Wenn eine Transact-SQL-Abfrage oder ein Transact-SQL-Batch sehr lange über eine bestimmte Verbindung mit SQL Server ausgeführt wird, rufen Sie den Ausführungsplan für diese Abfrage oder diesen Batch ab, um die Ursache der Verzögerung zu ermitteln. Im folgenden Beispiel wird das Abrufen des Showplans für eine langsam ausgeführte Abfrage oder einen Batch gezeigt.
Hinweis
Ersetzen Sie zum Ausführen dieses Beispiels die Werte für session_id und plan_handle mit den Werten Ihres Servers.
Rufen Sie zunächst die Serverprozess-ID (SPID) für den Prozess ab, der die Abfrage oder den Batch ausführt, indem Sie die gespeicherte Prozedur sp_who verwenden:
USE master;
GO
EXEC sp_who;
GO
Das von sp_who zurückgegebene Resultset gibt die SPID 54an. Verwenden Sie die SPID nun mit der dynamischen Verwaltungssicht sys.dm_exec_requests , um das Planhandle mithilfe der folgenden Abfrage abzurufen:
USE master;
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = 54;
GO
Die von sys.dm_exec_requests zurückgegebene Tabelle gibt an, dass der Planhandle für die langsam ausgeführte Abfrage oder den Batch ist0x06000100A27E7C1FA821B10600. Im folgenden Beispiel wird der Abfrageplan für das angegebene Planhandle zurückgegeben, und die Standardwerte 0 und -1 werden verwendet, um alle Anweisungen in der Abfrage oder im Batch zurückzugeben.
USE master;
GO
SELECT query_plan
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);
GO
B. Abrufen jedes Abfrageplans aus dem Plancache
Wenn Sie eine Momentaufnahme aller im Plancache gespeicherten Abfragen abrufen möchten, rufen Sie die Planhandles aller Abfragepläne im Cache ab, indem Sie die dynamische Verwaltungssicht sys.dm_exec_cached_plans abfragen. Die Planhandles sind in der plan_handle -Spalte von sys.dm_exec_cached_plansgespeichert. Verwenden Sie dann den CROSS APPLY-Operator, um die Planhandles wie folgt an sys.dm_exec_text_query_plan zu übergeben. Die Showplanausgabe für jeden aktuell im Plancache gespeicherten Plan wird in der query_plan-Spalte der zurückgegebenen Tabelle angezeigt.
USE master;
GO
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);
GO
C. Abrufen jedes Abfrageplans aus dem Plancache, für den der Server eine Abfragestatistik erfasst hat
Wenn Sie eine Momentaufnahme aller im Plancache gespeicherten Abfragen abrufen möchten, für die der Server eine Statistik erfasst hat, rufen Sie die Planhandles dieser Pläne im Cache ab, indem Sie die dynamische Verwaltungssicht sys.dm_exec_query_stats abfragen. Die Planhandles sind in der plan_handle -Spalte von sys.dm_exec_query_statsgespeichert. Verwenden Sie dann den CROSS APPLY-Operator, um die Planhandles wie folgt an sys.dm_exec_text_query_plan zu übergeben. Die Showplanausgabe für die einzelnen Pläne befindet sich in der query_plan-Spalte der zurückgegebenen Tabelle.
USE master;
GO
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);
GO
D: Abrufen von Informationen zu den fünf Abfragen mit dem höchsten durchschnittlichen CPU-Zeitaufwand
Im folgenden Beispiel werden die Abfragepläne und die durchschnittliche CPU-Zeit für die fünf Abfragen mit der höchsten durchschnittlichen CPU-Zeit zurückgegeben. Die sys.dm_exec_text_query_plan-Funktion gibt die Standardwerte 0 und -1 an, um alle Anweisungen im Batch im Abfrageplan zurückzugeben.
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 0, -1)
ORDER BY total_worker_time/execution_count DESC;
GO