sys.dm_exec_query_plan (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceSQL-Datenbank in Microsoft Fabric

Die sys.dm_exec_query_plan dynamische Verwaltungsfunktion gibt den Showplan im XML-Format für den vom Planhandle angegebenen Batch zurück. Der vom Planhandle angegebene Plan ist möglicherweise zwischengespeichert oder wird gerade ausgeführt.

Das XML-Schema für den Showplan ist veröffentlicht und auf dieser Microsoft-Websiteverfügbar. Sie ist auch im Verzeichnis verfügbar, in dem SQL Server installiert ist.

Transact-SQL-Syntaxkonventionen

Syntax

sys.dm_exec_query_plan(plan_handle)

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).

Dies plan_handle kann aus den folgenden dynamischen Verwaltungsobjekten abgerufen werden:

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

Zurückgegebene Tabelle

Bemerkungen

Unter den folgenden Bedingungen wird in der query_plan Spalte der zurückgegebenen Tabelle keine Showplan-Ausgabe zurückgegeben für sys.dm_exec_query_plan:

• Wenn der durch die Verwendung plan_handle angegebene Abfrageplan aus dem Plan-Cache entfernt wurde, ist NULLdie Spalte query_plan der zurückgegebenen Tabelle . Zum Beispiel könnte dieser Zustand auftreten, wenn zwischen der Erfassung des Planhandles und der Verwendung mit sys.dm_exec_query_plan

• 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. XML Showplans für solche Anweisungen können nicht durch Verwenden sys.dm_exec_query_plan abgerufen werden, es sei denn, der Batch wird gerade ausgeführt, da sie nicht im Cache existieren.

• Wenn eine Transact-SQL Batch- oder gespeicherte Prozedur einen Aufruf einer benutzerdefinierten Funktion oder einen Aufruf zu dynamischem SQL enthält, zum Beispiel mit EXEC (String), ist das kompilierte XML Showplan für die benutzerdefinierte Funktion nicht in der Tabelle enthalten, die für den Batch oder das gespeicherte Verfahren zurückgegeben sys.dm_exec_query_plan wird. Stattdessen müssen Sie einen separaten Aufruf für sys.dm_exec_query_plan den Planhandle machen, der der benutzerdefinierten Funktion entspricht.

Wenn eine Ad-hoc-Abfrage eine einfache oder erzwungene Parametrisierung verwendet, enthält die Spalte query_plan nur den Anweisungstext und nicht den eigentlichen Abfrageplan. Um den Abfrageplan zurückzugeben, rufen Sie den Planhandle der vorbereiteten parametrisierten Abfrage auf.sys.dm_exec_query_plan Sie können bestimmen, ob die Abfrage parametrisiert wurde, indem Sie auf die sql Spalte der sys.syscacheobjects-Ansicht oder die Textspalte der sys.dm_exec_sql_text dynamischen Verwaltungsansicht verweisen.

Erlaubnisse

Um ausgeführt sys.dm_exec_query_planzu werden, muss ein Benutzer Mitglied der Sysadmin-Fixed-Server-Rolle sein oder die Berechtigung VIEW SERVER STATE auf dem Server besitzen.

Berechtigungen für SQL Server 2022 und neuere Versionen

Erfordert die VIEW SERVER PERFORMANCE STATE-Berechtigung auf dem Server.

Examples

Die folgenden Beispiele zeigen, wie man die sys.dm_exec_query_plan dynamische Management-Ansicht verwendet.

Um die XML-Showplans anzuzeigen, führen Sie die folgenden Abfragen im Abfrage-Editor von SQL Server Management Studio aus und wählen Sie dann ShowPlanXML in der query_plan Spalte der Tabelle aus, die von The XML Showplan im Management Studio Zusammenfassungsfenster angezeigt sys.dm_exec_query_plan wird. Um das XML Showplan in einer Datei zu speichern, klicken Sie mit der rechten Maustaste auf ShowPlanXML in der query_plan Spalte, wählen Sie Ergebnisse speichern als, benennen Sie die Datei im Format <file name>.sqlplan; zum Beispiel, MyXMLShowplan.sqlplan.

A. Abrufen des zwischengespeicherten Abfrageplans für eine langsam ausgeführte Transact-SQL-Abfrage oder einen langsam ausgeführten Transact-SQL-Batch

Abfragepläne für verschiedene Arten von Transact-SQL-Batches, z. B. Ad-hoc-Batches, gespeicherte Prozeduren und benutzerdefinierte Funktionen, werden in einem Speicherbereich zwischengespeichert, der als Plancache bezeichnet wird. Jeder zwischengespeicherte Abfrageplan wird durch einen eindeutigen Bezeichner identifiziert, der Planhandle genannt wird. Sie können diesen Plan-Handle mit der dynamischen sys.dm_exec_query_plan Management-Ansicht angeben, um den Ausführungsplan für eine bestimmte Transact-SQL Abfrage oder einen bestimmten Batch abzurufen.

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 XML-Showplans für eine langsam ausgeführte Abfrage oder einen Batch gezeigt.

Zuerst rufen Sie die Sitzungs-ID (SPID) für den Prozess, der die Abfrage oder den Batch ausführt, mithilfe des gespeicherten sp_who Verfahrens ab:

exec sp_who;  
GO  

Das Ergebnisset, das von zurückgegeben wird sp_who , zeigt an, dass die Sitzungs-ID ist 54. Sie können die Sitzungs-ID mit der dynamischen sys.dm_exec_requests Managementansicht verwenden, um den Planhandle mit folgender Abfrage abzurufen:

SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO  

Die Tabelle, von der zurückgegeben sys.dm_exec_requests wird, zeigt an, dass der Plan-Handle für die langsam laufende Abfrage oder Batch ist 0x06000100A27E7C1FA821B10600, mit dem Sie als Argument plan_handle angeben sys.dm_exec_query_plan können, um den Ausführungsplan im XML-Format wie folgt abzurufen. Der Ausführungsplan im XML-Format für die langsam laufende Abfrage oder Batch befindet sich in der query_plan Spalte der von der Tabelle zurückgegebenen sys.dm_exec_query_planTabelle.

SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO  

B. Abrufen jedes einzelnen 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_query_plan zu übergeben. Die XML-Showplanausgabe für jeden aktuell im Plancache gespeicherten Plan wird in der query_plan -Spalte der zurückgegebenen Tabelle angezeigt.

SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO  

C. Abrufen jedes einzelnen 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_query_plan zu übergeben. Die XML-Showplanausgabe für jeden aktuell im Plancache gespeicherten Plan, für den der Server eine Statistik erfasst hat, wird in der query_plan -Spalte der zurückgegebenen Tabelle angezeigt.

SELECT * 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D: Abrufen von Informationen zu den fünf Abfragen mit dem höchsten durchschnittlichen CPU-Zeitaufwand

Im folgenden Beispiel werden die Pläne und die durchschnittliche CPU-Zeit für die fünf Abfragen mit der höchsten durchschnittlichen CPU-Zeit zurückgegeben.

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_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

Verwandte Inhalte

• Systemdynamische Verwaltungsansichten und -funktionen
• sys.dm_exec_cached_plans (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• sys.dm_exec_requests (Transact-SQL)
• sys.sp_who (Transact-SQL)
• Referenz zu logischen und physischen Showplan-Operatoren
• sys.dm_exec_text_query_plan (Transact-SQL)