sys.dm_exec_query_plan

Aktualisiert: 12. Dezember 2006

Gibt für den vom Planhandle angegebenen Batch den Showplan im XML-Format 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-Website verfügbar. Zudem steht es in dem Verzeichnis, in dem SQL Server 2005 installiert ist, an folgender Position zur Verfügung:

\\Programme\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

Transact-SQL-Syntaxkonventionen

Syntax

sys.dm_exec_query_plan ( plan_handle )

Argumente

• plan_handle
  Identifiziert eindeutig einen Abfrageplan für einen Batch, der zwischengespeichert ist oder gerade ausgeführt wird.

  plan_handle ist varbinary(64). plan_handle kann aus den folgenden dynamischen Verwaltungsobjekten abgerufen werden:

  sys.dm_exec_cached_plans

  sys.dm_exec_query_stats

  sys.dm_exec_requests

Zurückgegebene Tabelle

Hinweise

Unter den folgenden Bedingungen wird keine Showplanausgabe in der query_plan-Spalte der zurückgegebenen Tabelle für sys.dm_exec_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 es eine Zeitverzögerung zwischen der Erfassung des Planhandles und seiner Verwendung mit sys.dm_exec_query_plan gibt.
• 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-Showpläne für diese Anweisungen können mit sys.dm_exec_query_plan nur abgerufen werden, wenn der Batch gerade ausgeführt wird, da sie im Cache nicht 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_query_plan für das Planhandle erstellen, das der benutzerdefinierten Funktion entspricht.

Wenn bei einer Ad-hoc-Abfrage einfache oder erzwungene Parametrisierung verwendet wird, ist in der Spalte query_plan nur der Anweisungstext enthalten, nicht der tatsächliche Abfrageplan. Rufen Sie zum Zurückgeben des Abfrageplanes sys.dm_exec_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. Weitere Informationen zur Parametrisierung finden Sie unter Einfache Parametrisierung und Erzwungene Parametrisierung.

Aufgrund einer Beschränkung der im xml-Datentyp zulässigen Anzahl geschachtelter Ebenen kann sys.dm_exec_query_plan keine Abfragepläne zurückgeben, die 128 oder mehr Ebenen geschachtelter Elemente aufweisen. In früheren Versionen von SQL Server 2005 verhindert diese Bedingung das Zurückgeben des Abfrageplanes, wobei der Fehler 6335 generiert wird. In Service Pack 2 gibt die query_plan-Spalte NULL zurück. Mit der dynamischen Verwaltungsfunktion sys.dm_exec_text_query_plan können Sie die Ausgabe des Abfrageplanes im Textformat zurückgeben.

Berechtigungen

Zum Ausführen von sys.dm_exec_query_plan muss ein Benutzer Mitglied der festen Serverrolle sysadmin sein oder über die VIEW SERVER STATE-Berechtigung auf dem Server verfügen.

Beispiele

Im folgenden Beispiel wird die Verwendung der dynamischen Verwaltungssicht sys.dm_exec_query_plan gezeigt.

Führen Sie zum Anzeigen der XML-Showpläne die folgenden Abfragen im Abfrage-Editor von SQL Server Management Studio aus, und klicken Sie dann in der query_plan-Spalte der von sys.dm_exec_query_plan zurückgegebenen Tabelle auf ShowPlanXML. Der XML-Showplan wird im Management Studio-Zusammenfassungsbereich angezeigt. Klicken Sie zum Speichern des XML-Showplanes in einer Datei mit der rechten Maustaste in der query_plan-Spalte auf ShowPlanXML, klicken Sie auf Ergebnisse speichern unter, und geben Sie einen Namen für die Datei im Format <file_name>.sqlplan ein, z. B. MyXMLShowplan.sqlplan.

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

In SQL Server 2005 werden Abfragepläne für unterschiedliche Typen von Transact-SQL-Batches (z. B. Ad-hoc-Batches, gespeicherte Prozeduren und benutzerdefinierte Funktionen) in einem Bereich des Arbeitsspeichers zwischengespeichert, der als Plancache bezeichnet wird. Jeder zwischengespeicherte Abfrageplan wird durch einen eindeutigen Bezeichner identifiziert, der Planhandle genannt wird. Dieses Planhandle kann mit der dynamischen Verwaltungssicht sys.dm_exec_query_plan angegeben werden, um den Ausführungsplan für bestimmte Transact-SQL-Abfragen oder -Batches abzurufen.

Wenn eine Transact-SQL-Abfrage oder ein -Batch für lange Zeit mit einer bestimmten Verbindung mit SQL Server ausgeführt wird, können Sie den Ausführungsplan für diese Abfrage oder diesen Batch abrufen, um die Ursache der Verzögerung zu ermitteln. Im folgenden Beispiel wird das Abrufen des XML-Showplanes 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 54 an. 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

In der von sys.dm_exec_requests zurückgegebenen Tabelle wird angezeigt, dass das Planhandle für die langsam ausgeführte Abfrage oder den Batch 0x06000100A27E7C1FA821B10600 lautet. Geben Sie diesen Wert als plan_handle-Argument mit sys.dm_exec_query_plan an, um den Ausführungsplan im XML-Format wie folgt abzurufen. Der Ausführungsplan im XML-Format für die langsam ausgeführte Abfrage oder den Batch ist in der query_plan-Spalte der von sys.dm_exec_query_plan zurückgegebenen Tabelle enthalten.

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

B. Abrufen jedes einzelnen Abfrageplanes aus dem Plancache

Wenn Sie einen Snapshot 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_plans gespeichert. 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.

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

C. Abrufen jedes einzelnen Abfrageplanes aus dem Plancache, für den der Server eine Abfragestatistik erfasst hat

Wenn Sie einen Snapshot 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_stats gespeichert. 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.

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats 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

Siehe auch

Verweis

Dynamische Verwaltungssichten und -funktionen
sys.dm_exec_cached_plans
sys.dm_exec_query_stats
sys.dm_exec_requests
sp_who (Transact-SQL)
sys.dm_exec_text_query_plan

Andere Ressourcen

XML-Showplans
Verwenden von APPLY
Zwischenspeichern und Wiederverwenden von Ausführungsplänen
Logische und physikalische Operatoren (Referenz)
Abfrageleistung

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf