SET SHOWPLAN_XML (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (pool SQL dédié uniquement)Endpoint SQL analytics dans Microsoft FabricEntrepôt dans Microsoft FabricBase de données SQL dans Microsoft Fabric

Empêche SQL Server d'exécuter des instructions Transact-SQL. À la place, SQL Server retourne des informations détaillées sur le mode d’exécution des instructions, sous la forme d’un document XML bien défini.

Conventions de la syntaxe Transact-SQL

Syntaxe

SET SHOWPLAN_XML { ON | OFF }

Notes

Le paramètre est défini au moment de SET SHOWPLAN_XML l’exécution ou de l’exécution et non au moment de l’analyse.

Lorsque SET SHOWPLAN_XML c’est ACTIVÉ, SQL Server renvoie les informations du plan d’exécution pour chaque instruction sans l’exécuter, et Transact-SQL instructions ne sont pas exécutées. Une fois cette option activée, des informations sur le plan d'exécution de toutes les instructions Transact-SQL suivantes sont retournées jusqu'à sa désactivation (OFF). Par exemple, si une CREATE TABLE instruction est exécutée alors qu’elle SETSET SHOWPLAN_XML est activée, SQL Server renvoie un message d’erreur provenant d’une instruction SELECT ultérieure impliquant cette même table ; la table spécifiée n’existe pas. Par conséquent, les prochaines références à cette table échoueront. Lorsque SET SHOWPLAN_XML c’est désactivé, SQL Server exécute les instructions sans générer de rapport.

SET SHOWPLAN_XML est destiné à retourner la sortie sous forme de nvarchar(max) pour des applications telles que l’utilitaire SQLCMD , où la sortie XML est ensuite utilisée par d’autres outils pour afficher et traiter les informations du plan de requête.

SET SHOWPLAN_XML ne peut pas être spécifié dans une procédure stockée. Elle doit être la seule instruction d'un traitement.

SET SHOWPLAN_XML renvoie les informations sous forme d’un ensemble de documents XML. Chaque lot après l’instruction SET SHOWPLAN_XML ON est reflété dans la sortie par un seul document. Chaque document contient le texte des instructions du traitement, suivi des détails des étapes de l'exécution. Le document présente les coûts estimés, le nombre de lignes, les index accédés et les types d'opérateurs utilisés, l'ordre de jointure et d'autres informations relatives aux plans d'exécution.

Plans d’exécution estimés via SSMS et SET SHOWPLAN_XML disponibles pour les pools SQL dédiés (anciennement SQL DW) et les pools SQL dédiés dans Azure Synapse Analytics. Pour récupérer un plan d’exécution réel pour les pools SQL dédiés (anciennement SQL DW) et les pools SQL dédiés dans Azure Synapse Analytics, il existe différentes commandes. Pour plus d'informations, consultez Superviser la charge de travail de votre pool SQL dédié Azure Synapse Analytics à l’aide de vues de gestion dynamique.

Emplacement de la sortie de SHOWPLAN

Le document contenant le schéma XML pour la sortie XML par SET SHOWPLAN_XML est copié lors de la configuration dans un répertoire local de l’ordinateur sur lequel Microsoft SQL Server est installé. Le document se trouve sur le lecteur contenant les fichiers d’installation de SQL Server, dans un chemin similaire à celui-ci :

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

Dans le chemin précédent, le nœud 130\ est utilisé par SQL Server 2016. Le nombre 130 est dérivé du premier nœud de la valeur retournée par SELECT @@VERSION, qui est 13. Pour SQL Server 2017, le chemin utilise 140\, car le premier nœud de sa valeur pour @@VERSION est 14. Pour SQL Server 2019, la première valeur de @@VERSION est 15. Pour SQL Server 2022, la première valeur de @@VERSION est 16.

Le schéma du plan d'exécution est également disponible sur Schémas XML SQL Server.

Autorisations

Pour utiliser SETSET SHOWPLAN_XML, vous devez disposer d’autorisations suffisantes pour exécuter les instructions sur lesquelles SETSET SHOWPLAN_XML est exécuté, et vous devez avoir l’autorisation SHOWPLAN pour toutes les bases de données contenant des objets référencés.

Concernant les instructions SELECT, INSERT, UPDATE, DELETE, EXEC *stored_procedure* et EXEC *user_defined_function*, pour produire un plan d'exécution (Showplan), l'utilisateur doit :

• disposer des autorisations appropriées pour exécuter les instructions Transact-SQL ;

• Disposer de l'autorisation SHOWPLAN sur toutes les bases de données contenant les objets référencés par les instructions Transact-SQL, par exemple des tables, des vues, etc.

Pour toutes les autres instructions, telles que DDL, USE *database_name*, SET, DECLARE, dynamic SQL, etc., seules les autorisations appropriées pour exécuter les instructions Transact-SQL sont nécessaires.

Exemples

Les deux instructions qui suivent utilisent les SET SHOWPLAN_XML paramètres pour montrer comment SQL Server analyse et optimise l’utilisation des index dans les requêtes.

La première requête utilise l'opérateur de comparaison Égal à (=) dans la clause WHERE sur une colonne indexée. La seconde requête utilise l'opérateur LIKE dans la clause WHERE. SQL Server doit utiliser une analyse d'index cluster et rechercher les données répondant à la condition spécifiée par la clause WHERE. Les valeurs des attributs EstimateRows et EstimatedTotalSubtreeCost sont plus petites pour la première requête indexée, ce qui indique qu’elle est traitée beaucoup plus rapidement que la requête non indexée, tout en utilisant moins de ressources.

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;

Étapes suivantes

• SET Instructions (Transact-SQL)
• Afficher le plan d’exécution estimé