SET SHOWPLAN_XML (Transact-SQL)

  • Article

S’applique à : SQL Server base de données Azure SQL Azure SQL Managed Instance Azure Synapse Analytics (pools SQL dédiés uniquement)

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

L'option SET SHOWPLAN_XML est définie lors de l'exécution, et non pas durant l'analyse.

Si SET SHOWPLAN_XML est activé, SQL Server retourne des informations sur le plan d’exécution de chaque instruction sans l’exécuter et les instructions Transact-SQL 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 instruction CREATE TABLE est exécutée alors que l'option SET SHOWPLAN_XML est activée, SQL Server retourne un message d'erreur pour toute instruction SELECT ultérieure se rapportant à cette table car elle n'existe pas. Par conséquent, les prochaines références à cette table échoueront. Lorsque SET SHOWPLAN_ALL est désactivé, SQL Server exécute les instructions sans générer de rapport.

SET SHOWPLAN_XML est censé retourner le résultat en nvarchar(max) pour des applications telles que l’utilitaire sqlcmd, où la sortie XML est utilisée ultérieurement par d’autres outils pour afficher et traiter les informations du plan de requête.

Notes

La vue de gestion dynamique, sys.dm_exec_query_plan, retourne les mêmes informations que SET SHOWPLAN XML dans le type de données xml. Ces informations sont retournées de la colonne query_plan de sys.dm_exec_query_plan. Pour plus d’informations, consultez sys.dm_exec_query_plan (Transact-SQL).

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 retourne les informations sous forme d'un jeu de documents XML. Chaque traitement après l'instruction SET SHOWPLAN_XML ON est présenté dans le résultat par un document unique. 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.

Notes

Si l’option Inclure le plan d’exécution réel est sélectionnée dans SQL Server Management Studio, cette option SET ne produit aucune sortie de la représentation au format XML. Désactivez l’option Inclure le plan d’exécution réel avant d’utiliser l’option SET.

Les plans d’exécution estimés via SSMS et SET SHOWPLAN_XML sont 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 provenant de SET SHOWPLAN_XML est copié pendant l'installation sur 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 SET SHOWPLAN_XML, vous devez disposer des autorisations suffisantes pour exécuter les instructions sur lesquelles SET SHOWPLAN_XML est exécuté, ainsi que de l'autorisation SHOWPLAN pour toutes les bases de données contenant les objets auxquels elles font référence.

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 suivantes utilisent les paramètres SET SHOWPLAN_XML, activés puis désactivés, 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