• Article

SET SHOWPLAN_XML (Transact-SQL)

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 correct.

Icône Lien de rubriqueConventions de la syntaxe de 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 a la valeur ON, SQL Server retourne des informations sur le plan d'exécution de chaque instruction sans toutefois l'exécuter ; 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_XML a la valeur OFF, SQL Server exécute les instructions sans générer de rapport.

SET SHOWPLAN_XML sert à retourner le résultat sous forme 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.

[!REMARQUE]

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 à partir de la colonne query_plan de la vue 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.

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é. Il est situé sur le lecteur contenant les fichiers d'installation de SQL Server à l'adresse :

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

Le schéma Showplan est également disponible sur ce site Web (en anglais).

[!REMARQUE]

Si l'option Inclure le plan d'exécution réel est sélectionnée dans SQL Server Management Studio, cette option SET ne produira 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.

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 auxquels les instructions Transact-SQL font référence, tels que les tables, les 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.

Pour plus d'informations, consultez Sécurité Showplan et Autorisation SHOWPLAN et traitements Transact-SQL.

Exemples

Les deux instructions suivantes utilisent l'option SET SHOWPLAN_XML, activée puis désactivée, 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 AdventureWorks;
GO
SET SHOWPLAN_XML ON;
GO
-- First query.
SELECT EmployeeID 
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT EmployeeID, ContactID 
FROM HumanResources.Employee
WHERE Title LIKE 'Production%';
GO
SET SHOWPLAN_XML OFF;