sys.dm_exec_query_statistics_xml (Transact-SQL)

Artikel
07/15/2024

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance

Gibt den Abfrageausführungsplan für In-Flight-Anforderungen zurück. Verwenden Sie diesen DMV, um Showplan-XML mit vorübergehenden Statistiken abzurufen.

Syntax

sys.dm_exec_query_statistics_xml(session_id)

Argumente

session_id
Ist die Sitzungs-ID, die den Batch ausführt, der nachgeschlagen werden soll. session_id ist smallint. session_id kann aus den folgenden dynamischen Verwaltungsobjekten abgerufen werden:

Zurückgegebene Tabelle

Spaltenname	Datentyp	BESCHREIBUNG
session_id	smallint	ID der Sitzung. Lässt keine NULL-Werte zu.
request_id	int	ID der Anforderung. Lässt keine NULL-Werte zu.
sql_handle	varbinary(64)	Ein Token, das den Batch oder die gespeicherte Prozedur, zu dem bzw. der die Abfrage gehört, eindeutig identifiziert. NULL-Werte sind zulässig.
plan_handle	varbinary(64)	Ist ein Token, das einen Abfrageausführungsplan für einen aktuell ausgeführten Batch eindeutig identifiziert. NULL-Werte sind zulässig.
query_plan	xml	Enthält die Showplan-Laufzeitdarstellung des Abfrageausführungsplans, der mit plan_handle angegeben wird und Teilstatistiken enthält. Der Showplan liegt im XML-Format 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. NULL-Werte sind zulässig.

Hinweise

Wichtig

Aufgrund einer möglichen zufälligen Zugriffsverletzung (AV) bei der Ausführung einer gespeicherten Überwachungsprozedur mit dem sys.dm_exec_query_statistics_xml-DMV wurde das Showplan XML-Attribut <ParameterList> mit dem Wert ParameterRuntimeValue in SQL Server 2017 (14.x) CU 26 und SQL Server 2019 (15.x) CU 12 entfernt. Dieser Wert kann bei der Problembehandlung bei lang ausgeführten gespeicherten Prozeduren hilfreich sein.

Beginnend mit SQL Server 2017 (14.x) CU 31 und SQL Server 2019 (15.x) CU 19 wurde die Erfassung des Showplan-XML-Attributs <ParameterList> mit dem Wert ParameterRuntimeValue durch die Aufnahme des Ablaufverfolgungsflags 2446 wieder aktiviert. Dieses Ablaufverfolgungsflag ermöglicht die Sammlung des Laufzeitparameterwerts auf Kosten eines zusätzlichen Mehraufwands.

Warnung

Das Ablaufverfolgungsflag 2446 ist nicht dafür konzipiert, dauerhaft in einer Produktionsumgebung aktiviert zu sein, sondern nur zeitlich begrenzt zur Problembehandlung. Das Verwenden dieses Ablaufverfolgungsflags führt zu zusätzlicher und möglicherweise erheblicher CPU- und Speicherauslastung, da ein Showplan-XML-Fragment mit Runtimeparameter-Informationen erstellt wird, unabhängig davon ob die sys.dm_exec_query_statistics_xml-DMV aufgerufen wird oder nicht.

Hinweis

Ab SQL Server 2022 (16.x), Azure SQL Database und Azure SQL Managed Instance kann dies auf Datenbankebene mit der Option FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) erreicht werden.

Diese Systemfunktion ist ab SQL Server 2016 (13.x) SP1 verfügbar. Siehe KB 3190871

Diese Systemfunktion funktioniert sowohl unter der Standard- als auch der einfachen Infrastruktur für die Erstellung von Abfragestatistiken. Weitere Informationen finden Sie unter Profilerstellungsinfrastruktur für Abfragen.

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

Wenn der Abfrageplan, der dem angegebenen session_id entspricht, nicht mehr ausgeführt wird, ist die query_plan-Spalte der zurückgegebenen Tabelle 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_statistics_xml gibt.

Aufgrund einer Beschränkung der im xml -Datentyp zulässigen Anzahl geschachtelter Ebenen kann sys.dm_exec_query_statistics_xml keine Abfragepläne zurückgeben, die 128 oder mehr Ebenen geschachtelter Elemente aufweisen. In früheren Versionen von SQL Server verhinderte diese Bedingung das Zurückgeben des Abfrageplans, wobei der Fehler 6335 generiert wurde. In SQL Server 2005 (9.x) Service Pack 2 und höheren Versionen gibt die query_plan-Spalte NULL zurück.

Berechtigungen

Auf SQL Server ist eine VIEW SERVER STATE-Berechtigung auf dem Server erforderlich.
In SQL-Datenbank Premium-Tarifen ist die VIEW DATABASE STATE-Berechtigung für die Datenbank erforderlich. Auf SQL-Datenbank Standard- und Basicebenen ist das Serveradministrator- oder das Microsoft Entra-Administrator-konto nötig.

Berechtigungen für SQL Server 2022 und höher

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

Beispiele

A. Anzeigen von Liveabfrageplan- und Ausführungsstatistiken für einen ausgeführten Batch

Im folgenden Beispiel werden sys.dm_exec_requests abgefragt, um die entsprechende Abfrage zu suchen und den session_id-Wert aus der Ausgabe zu kopieren.

SELECT * FROM sys.dm_exec_requests;  
GO

Um dann den Live-Abfrageplan und die Ausführungsstatistiken zu erhalten, verwenden Sie das kopierte session_id mit der Systemfunktion sys.dm_exec_query_statistics_xml.

--Run this in a different session than the session in which your query is running.
SELECT * FROM sys.dm_exec_query_statistics_xml(< copied session_id >);  
GO

Oder kombiniert für alle ausgeführten Anforderungen.

--Run this in a different session than the session in which your query is running.
SELECT 
	eqs.query_plan, 
	er.session_id, 
	er.request_id, 
	er.database_id,
	er.start_time,
	er.[status], 
	er.wait_type,
	er.wait_resource, 
	er.last_wait_type,
	(er.cpu_time/1000) AS cpu_time_sec,
	(er.total_elapsed_time/1000)/60 AS elapsed_time_minutes,
	(er.logical_reads*8)/1024 AS logical_reads_KB,
	er.granted_query_memory,
	er.dop,
	er.row_count, 
	er.query_hash, 
	er.query_plan_hash
FROM sys.dm_exec_requests er
	CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) eqs
WHERE er.session_id <> @@spid;
GO

Weitere Informationen

Ablaufverfolgungsflags
Dynamische Verwaltungssichten und Funktionen (Transact-SQL)
Mit der Datenbank verbundene dynamische Verwaltungssichten (Transact-SQL)

Teilen über