SET SHOWPLAN_XML (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics (nur dedizierte SQL-Pools)

Dies bewirkt, dass SQL Server Transact-SQL-Anweisungen nicht ausführt. Stattdessen gibt SQL Server detaillierte Informationen zur Ausführung der Anweisungen in Form eines genau definierten XML-Dokuments zurück.

Transact-SQL-Syntaxkonventionen

Syntax

SET SHOWPLAN_XML { ON | OFF }

Hinweise

Die Einstellung von SET SHOWPLAN_XML wird zur Ausführungszeit und nicht zur Analysezeit festgelegt.

Wenn SET SHOWPLAN_XML auf ON festgelegt ist, gibt SQL Server Informationen zum Ausführungsplan jeder Anweisung zurück, ohne sie auszuführen, und Transact-SQL-Anweisungen werden nicht ausgeführt. Wenn diese Option auf ON festgelegt ist, werden Ausführungsplaninformationen zu allen weiteren Transact-SQL-Anweisungen zurückgegeben, bis die Option auf OFF festgelegt wird. Wenn beispielsweise eine CREATE TABLE-Anweisung ausgeführt wird, während SET SHOWPLAN_XML auf ON festgelegt ist, gibt SQL Server bei einer darauf folgenden SELECT-Anweisung, die dieselbe Tabelle betrifft, die Fehlermeldung zurück, dass diese Tabelle nicht vorhanden ist. Daher schlagen spätere Verweise auf diese Tabelle fehl. Wenn SET SHOWPLAN_XML auf OFF festgelegt ist, führt SQL Server die Anweisungen aus, ohne einen Bericht zu generieren.

SET SHOWPLAN_XML soll die Ausgabe als nvarchar(max) für Anwendungen zurückgeben, wie z.B. das Hilfsprogramm sqlcmd, wobei die XML-Ausgabe nachfolgend von weiteren Tools für die Anzeige und Verarbeitung der Abfrageplaninformationen verwendet wird.

Hinweis

Die dynamische Verwaltungssicht sys.dm_exec_query_plan gibt dieselben Informationen wie SET SHOWPLAN XML im Datentyp XML zurück. Diese Informationen werden aus der Spalte query_plan von sys.dm_exec_query_plan zurückgegeben. Weitere Informationen finden Sie unter sys.dm_exec_query_plan (Transact-SQL).

SET SHOWPLAN_XML kann innerhalb einer gespeicherten Prozedur nicht angegeben werden. Sie muss die einzige Anweisung in einem Batch sein.

SET SHOWPLAN_XML gibt Informationen als eine Gruppe von XML-Dokumenten zurück. Jeder Batch nach der SET SHOWPLAN_XML ON-Anweisung ist in der Ausgabe als einzelnes Dokument enthalten. Jedes Dokument enthält den Text der Anweisungen im Batch gefolgt von den Details der Ausführungsschritte. Das Dokument zeigt die geschätzten Kosten, Anzahl von Zeilen, Indexzugriffe und Typen der ausgeführten Operatoren, Joinreihenfolge und weitere Informationen zu den Ausführungsplänen.

Hinweis

Wenn Tatsächlichen Ausführungsplan einschließen in SQL Server Management Studio ausgewählt ist, generiert diese SET-Option keine XML-Showplanausgabe mehr. Deaktivieren Sie das Kontrollkästchen Tatsächlichen Ausführungsplan einschließen, bevor Sie diese SET-Option verwenden.

Geschätzte Ausführungspläne über SSMS und SET-SHOWPLAN_XML sind für dedizierte SQL-Pools (früher SQL DW) und dedizierte SQL-Pools in Azure Synapse Analytics verfügbar. Zum Abrufen eines tatsächlichen Ausführungsplans für dedizierte SQL-Pools (früher SQL DW) und dedizierte SQL-Pools in Azure Synapse Analytics gibt es verschiedene Befehle. Weitere Informationen finden Sie unter Überwachen der Workload Ihres dedizierten SQL-Pools von Azure Synapse Analytics mit DMVs.

Speicherort der SHOWPLAN-Ausgabe

Das Dokument mit dem XML-Schema für die XML-Ausgabe von SET SHOWPLAN_XML wird beim Setup in ein lokales Verzeichnis auf dem Computer kopiert, auf dem Microsoft SQL Server installiert ist. Das Dokument findet sich auf dem Laufwerk, das die SQL Server-Installationsdateien enthält, und ist unter einem Pfad ähnlich dem folgenden zugänglich:

  • \Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

Im vorherigen Pfad wird der Knoten 130\ von SQL Server 2016 verwendet. Die Zahl 130 wird vom ersten Knoten des von SELECT @@VERSION zurückgegebenen Werts abgeleitet, also 13. Für SQL Server 2017 würde der Pfad 140\ verwenden, da der erste Knoten des @@VERSION-Werts 14 ist. Bei SQL Server 2019 ist der erste Wert von @@VERSION 15. Bei SQL Server 2022 ist der erste Wert von @@VERSION 16.

Das Showplan-Schema finden Sie auch unter Microsoft SQL Server XML Schemas.

Berechtigungen

Für die Verwendung von SET SHOWPLAN_XML benötigen Sie für die Ausführung der Anweisungen, auf die SET SHOWPLAN_XML angewendet wird, ausreichende Berechtigungen sowie die SHOWPLAN-Berechtigung für alle Datenbanken mit Objekten, auf die verwiesen wird.

Um für SELECT-, INSERT-, UPDATE-, DELETE-, EXEC *stored_procedure*- und EXEC *user_defined_function*-Anweisungen einen Showplan zu erstellen, muss der Benutzer :

  • Die Berechtigungen für die Ausführung der Transact-SQL-Anweisungen

  • Die SHOWPLAN-Berechtigung für alle Datenbanken mit Objekten, auf die von den Transact-SQL-Anweisungen verwiesen wird, wie z. B. Tabellen, Sichten usw.

Für alle anderen Anweisungen, z. B. DDL, USE *database_name*, SET, DECLARE, dynamische SQL-Anweisungen usw. werden nur die entsprechenden Berechtigungen für die Ausführung der Transact-SQL-Anweisungen benötigt.

Beispiele

In den beiden folgenden Anweisungen werden die SET SHOWPLAN_XML-Einstellungen verwendet, um zu zeigen, wie SQL Server die Verwendung von Indizes in Abfragen analysiert und optimiert.

In der ersten Abfrage wird der Vergleichsoperator Gleich (=) in der WHERE-Klausel auf eine indizierte Spalte angewendet. In der zweiten Abfrage wird der LIKE-Operator in der WHERE-Klausel verwendet. Deshalb muss SQL Server einen Scan des gruppierten Indexes ausführen, um die Daten zu finden, die der Bedingung in der WHERE-Klausel entsprechen. Die Werte in den Attributen EstimateRows und EstimatedTotalSubtreeCost sind bei der ersten indizierten Abfrage kleiner, was auf eine deutlich schnellere Verarbeitung und die Verwendung von weniger Ressourcen als bei der nicht indizierten Abfrage hinweist.

USE AdventureWorks2022;
GO
SET SHOWPLAN_XML ON;
GO
-- First query.
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO
SET SHOWPLAN_XML OFF;

Nächste Schritte