SET SHOWPLAN_ALL (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Empêche Microsoft SQL Server d'exécuter des instructions Transact-SQL. Au lieu de cela, SQL Server renvoie des informations détaillées sur la façon dont les instructions seraient exécutées (un plan de requête) et fournit des estimations des exigences en ressources pour les instructions et les lignes attendues (estimation de la cardinalité).

Conventions de la syntaxe Transact-SQL

Syntaxe

  
SET SHOWPLAN_ALL { ON | OFF }  

Notes

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

Si SET SHOWPLAN_ALL a la valeur ON, SQL Server retourne des informations sur l’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 toutes les instructions Transact-SQL consécutives sont retournées, jusqu'à ce que l'option soit désactivée (OFF). Par exemple, si une instruction CREATE TABLE est exécutée alors que l’option SET SHOWPLAN_ALL a la valeur ON, SQL Server retourne un message d’erreur pour toute instruction SELECT ultérieure se rapportant à cette table, informant les utilisateurs qu’elle n’existe pas. Par conséquent, les prochaines références à cette table échoueront. Lorsque SET SHOWPLAN_ALL a la valeur OFF, SQL Server exécute les instructions sans générer de rapport.

L’option SET SHOWPLAN_ALL est conçue pour être utilisée avec des applications capables de gérer ses résultats. Utilisez SET SHOWPLAN_TEXT pour retourner des résultats pouvant être interprétés par les applications de ligne de commande Microsoft Win32, notamment l’utilitaire osql.

Les options SET SHOWPLAN_TEXT et SET SHOWPLAN_ALL ne peuvent pas être spécifiées dans une procédure stockée ; elles doivent être les seules instructions dans le traitement.

SET SHOWPLAN_ALL retourne les informations sous la forme d'un ensemble de lignes formant une arborescence hiérarchique, qui représente les étapes effectuées par le processeur de requêtes SQL Server à mesure qu'il exécute chaque instruction. Chaque instruction reflétée dans la sortie contient une ligne unique comportant le texte de l'instruction, suivie de plusieurs lignes contenant les détails des étapes d'exécution. Le tableau suivant illustre les colonnes figurant dans les résultats.

*Les unités de coût sont basées sur une mesure interne d'heure, et non sur l'horloge murale. Elles permettent de déterminer le coût relatif d'un plan par rapport à d'autres plans.

Autorisations

Pour utiliser SET SHOWPLAN_ALL, vous devez disposer des autorisations suffisantes pour exécuter les instructions sur lesquelles SET SHOWPLAN_ALL 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 une sortie 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, notamment DDL, USE database_name, SET, DECLARE, SQL dynamique, etc., seules les autorisations appropriées pour exécuter les instructions Transact-SQL sont nécessaires.

Exemples

Les deux instructions suivantes utilisent l'option SET SHOWPLAN_ALL, 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 valeur Clustered Index Seek est insérée dans la colonne LogicalOp et le nom de l’index est inséré dans la colonne Argument.

La seconde requête utilise l'opérateur LIKE dans la clause WHERE. Cela oblige SQL Server à utiliser une analyse d'index cluster et à rechercher les données répondant à la condition spécifiée par la clause WHERE. Cette requête place la valeur Clustered Index Scan dans la colonne LogicalOp et le nom de l’index dans la colonne Argument. La valeur de filtrage est insérée dans la colonne LogicalOp et la condition de la clause WHERE est placée dans la colonne Argument.

Les valeurs des colonnes EstimateRows et TotalSubtreeCost 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_ALL ON;  
GO  
-- First query.  
SELECT BusinessEntityID   
FROM HumanResources.Employee  
WHERE NationalIDNumber = '509647174';  
GO  
-- Second query.  
SELECT BusinessEntityID, EmergencyContactID   
FROM HumanResources.Employee  
WHERE EmergencyContactID LIKE '1%';  
GO  
SET SHOWPLAN_ALL OFF;  
GO  

Voir aussi

Instructions SET (Transact-SQL)
SET SHOWPLAN_TEXT (Transact-SQL)
SET SHOWPLAN_XML (Transact-SQL)