376 votes

Comment puis-je obtenir un plan d'exécution de requête dans SQL Server ?

Dans Microsoft SQL Server, comment puis-je obtenir un plan d'exécution de requête pour une requête / procédure stockée ?

2 votes

Comment fermer l'onglet Plan d'exécution dans SQL Server Management Studio ?

3 votes

@Paul Vous pouvez appuyer sur Ctrl + R pour cela. Cela ferme toute la section des résultats - y compris les messages et le plan d'exécution.

550voto

Justin Points 42106

Il existe plusieurs méthodes pour obtenir un plan d'exécution, celle à utiliser dépendra de votre situation. En général, vous pouvez utiliser SQL Server Management Studio pour obtenir un plan, mais si pour une raison quelconque vous ne pouvez pas exécuter votre requête dans SQL Server Management Studio, il peut être utile d'obtenir un plan via SQL Server Profiler ou en inspectant le cache du plan.

Méthode 1 - Utilisation de SQL Server Management Studio

Le serveur SQL est doté de quelques fonctions astucieuses qui facilitent la capture d'un plan d'exécution. Il suffit de s'assurer que l'élément de menu "Inclure le plan d'exécution réel" (situé dans le menu "Requête") est coché et d'exécuter votre requête normalement.

Include Action Execution Plan menu item

Si vous essayez d'obtenir le plan d'exécution des instructions dans une procédure stockée, vous devez exécuter la procédure stockée, comme suit :

exec p_Example 42

Lorsque votre requête est terminée, vous devriez voir apparaître un onglet supplémentaire intitulé "Plan d'exécution" dans le volet des résultats. Si vous avez exécuté de nombreuses requêtes, vous pouvez voir plusieurs plans affichés dans cet onglet.

Screenshot of an Execution Plan

À partir de là, vous pouvez inspecter le plan d'exécution dans SQL Server Management Studio, ou faire un clic droit sur le plan et sélectionner "Enregistrer le plan d'exécution sous ..." pour enregistrer le plan dans un fichier au format XML.

Méthode 2 - Utilisation des options de SHOWPLAN

Cette méthode est très similaire à la méthode 1 (en fait, c'est ce que SQL Server Management Studio fait en interne), mais je l'ai incluse par souci d'exhaustivité ou si vous ne disposez pas de SQL Server Management Studio.

Avant d'exécuter votre requête, exécutez un des instructions suivantes. L'instruction doit être la seule instruction du lot, c'est-à-dire que vous ne pouvez pas exécuter une autre instruction en même temps :

SET SHOWPLAN_TEXT ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_XML ON
SET STATISTICS PROFILE ON
SET STATISTICS XML ON -- The is the recommended option to use

Il s'agit d'options de connexion et vous n'avez donc besoin de l'exécuter qu'une seule fois par connexion. À partir de ce moment, toutes les instructions exécutées seront accompagnées d'un signe résultats supplémentaires contenant votre plan d'exécution dans le format souhaité - il suffit d'exécuter votre requête comme vous le feriez normalement pour voir le plan.

Une fois que vous avez terminé, vous pouvez désactiver cette option avec la déclaration suivante :

SET <<option>> OFF

Comparaison des formats de plan d'exécution

À moins que vous n'ayez une préférence marquée, je vous recommande d'utiliser l'option STATISTICS XML option. Cette option est équivalente à l'option "Include Actual Execution Plan" dans SQL Server Management Studio et fournit le plus d'informations dans le format le plus pratique.

  • SHOWPLAN_TEXT - Affiche un plan d'exécution estimé basé sur un texte de base, sans exécuter la requête.
  • SHOWPLAN_ALL - Affiche un plan d'exécution estimé basé sur le texte avec des estimations de coûts, sans exécuter la requête.
  • SHOWPLAN_XML - Affiche un plan d'exécution estimé basé sur XML avec des estimations de coûts, sans exécuter la requête. Ceci est équivalent à l'option "Display Estimated Execution Plan..." dans SQL Server Management Studio.
  • STATISTICS PROFILE - Exécute la requête et affiche un plan d'exécution réel basé sur le texte.
  • STATISTICS XML - Exécute la requête et affiche un plan d'exécution réel basé sur XML. Ceci est équivalent à l'option "Inclure le plan d'exécution réel" dans SQL Server Management Studio.

Méthode 3 - Utilisation de SQL Server Profiler

Si vous ne pouvez pas exécuter votre requête directement (ou si votre requête ne s'exécute pas lentement lorsque vous l'exécutez directement - rappelez-vous que nous voulons un plan de la requête qui fonctionne mal), vous pouvez alors capturer un plan en utilisant une trace SQL Server Profiler. L'idée est d'exécuter votre requête pendant qu'une trace qui capture l'un des événements "Showplan" est en cours d'exécution.

Notez que selon la charge que vous peut utiliser cette méthode dans un environnement de production, mais vous devez évidemment faire preuve de prudence. Les mécanismes de profilage de SQL Server sont conçus pour minimiser l'impact sur la base de données, mais cela ne veut pas dire qu'il n'y aura pas d'impact sur l'environnement de production. tout impact sur les performances. Vous pouvez également avoir des problèmes pour filtrer et identifier le bon plan dans votre trace si votre base de données est fortement utilisée. Vous devez évidemment vérifier auprès de votre DBA s'il est d'accord pour que vous fassiez cela sur sa précieuse base de données !

  1. Ouvrez SQL Server Profiler et créez une nouvelle trace en vous connectant à la base de données souhaitée pour laquelle vous souhaitez enregistrer la trace.
  2. Sous l'onglet "Sélection des événements", cochez "Afficher tous les événements", vérifiez la ligne "Performance" -> "Showplan XML" et exécutez la trace.
  3. Pendant que la trace est en cours d'exécution, faites ce que vous devez faire pour que la requête lente s'exécute.
  4. Attendez que la requête soit terminée et arrêtez la trace.
  5. Pour enregistrer la trace, faites un clic droit sur le plan xml dans SQL Server Profiler et sélectionnez "Extraire les données de l'événement..." pour enregistrer le plan dans un fichier au format XML.

Le plan que vous obtenez est équivalent à l'option "Include Actual Execution Plan" dans SQL Server Management Studio.

Méthode 4 - Inspecter le cache des requêtes

Si vous ne pouvez pas exécuter votre requête directement et que vous ne pouvez pas non plus capturer une trace du profileur, vous pouvez toujours obtenir un plan estimé en inspectant le cache du plan de la requête SQL.

Nous inspectons le cache des plans en interrogeant SQL Server DMVs . La requête suivante est une requête de base qui va lister tous les plans de requête en cache (en xml) avec leur texte SQL. Dans la plupart des bases de données, vous devrez également ajouter des clauses de filtrage supplémentaires afin de filtrer les résultats pour ne retenir que les plans qui vous intéressent.

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Exécutez cette requête et cliquez sur le plan XML pour ouvrir le plan dans une nouvelle fenêtre - faites un clic droit et sélectionnez "Enregistrer le plan d'exécution sous..." pour enregistrer le plan dans un fichier au format XML.

Notes :

En raison du grand nombre de facteurs en jeu (depuis le schéma de la table et de l'index jusqu'aux données stockées et aux statistiques de la table), vous devriez toujours essayer d'obtenir un plan d'exécution de la base de données qui vous intéresse (normalement celle qui connaît un problème de performance).

Vous ne pouvez pas capturer un plan d'exécution pour les procédures stockées cryptées.

Plans d'exécution "réels" et "estimés".

Un site réel Le plan d'exécution est un plan dans lequel SQL Server exécute réellement la requête, alors qu'un plan d'exécution est un plan dans lequel SQL Server exécute réellement la requête. estimé plan d'exécution, SQL Server calcule ce qu'il serait faire sans exécuter la requête. Bien que logiquement équivalent, un plan d'exécution réel est beaucoup plus utile car il contient des détails et des statistiques supplémentaires sur ce qui s'est réellement passé lors de l'exécution de la requête. Cela est essentiel pour diagnostiquer des problèmes lorsque les estimations de SQL Servers sont erronées (par exemple, lorsque les statistiques ne sont pas à jour).

Comment interpréter le plan d'exécution d'une requête ?

Il s'agit d'un sujet suffisamment important pour faire l'objet d'une conférence (gratuite). livre à part entière.

Voir aussi :

10 votes

Une note pour les futurs lecteurs : mettez SET STATISTICS XML ON au début de la requête, et SET STATISTICS XML OFF|ON dans les environs, vous Ne le fais pas. que vous voulez voir apparaître dans le plan d'exécution : J'ai trouvé cela utile lorsque la requête contient une itération (WHILE) que vous ne voulez/devez pas voir dans le plan d'exécution (sinon ce serait trop lourd et long pour SQL SERVER de l'afficher).

2 votes

@MonsterMMORPG vous pouvez utiliser la méthode 4 et ensuite le SELECT. Par exemple, en utilisant <a href=" github.com/StackExchange/dapper-dot-net">Dapper.net</ > connection.Query<string>("SELECT query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE TEXT LIKE N'%Your Original Query Goes Here%'") ; Les % indiquent que vous n'utilisez qu'un sous-ensemble de votre requête.

2 votes

@Justin la 2e édition du livre dont vous avez donné le lien, pour interpréter un plan d'exécution de requête, date de 2009. Diriez-vous qu'il s'agit toujours d'une très bonne ressource pour cet objectif en 2016 ?

45voto

Martin Smith Points 174101

En plus de la réponse complète déjà postée, il est parfois utile de pouvoir accéder au plan d'exécution de manière programmatique pour extraire des informations. Un exemple de code pour cela est ci-dessous.

DECLARE @TraceID INT
EXEC StartCapture @@SPID, @TraceID OUTPUT
EXEC sp_help 'sys.objects' /*<-- Call your stored proc of interest here.*/
EXEC StopCapture @TraceID

Exemple StartCapture Définition

CREATE PROCEDURE StartCapture
@Spid INT,
@TraceID INT OUTPUT
AS
DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)

EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL 

exec sp_trace_setevent @TraceID, 122, 1, 1
exec sp_trace_setevent @TraceID, 122, 22, 1
exec sp_trace_setevent @TraceID, 122, 34, 1
exec sp_trace_setevent @TraceID, 122, 51, 1
exec sp_trace_setevent @TraceID, 122, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1

Exemple StopCapture Définition

CREATE  PROCEDURE StopCapture
@TraceID INT
AS
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql), 
      CTE
     as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData,
                ObjectID,
                ObjectName,
                EventSequence,
                /*costs accumulate up the tree so the MAX should be the root*/
                MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
         FROM   fn_trace_getinfo(@TraceID) fn
                CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
                CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
                CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost',
                                            'float') AS EstimatedTotalSubtreeCost
                             FROM   xPlan.nodes('//sql:RelOp') T(relop)) ca
         WHERE  property = 2
                AND TextData IS NOT NULL
                AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' )
         GROUP  BY CAST(TextData AS VARCHAR(MAX)),
                   ObjectID,
                   ObjectName,
                   EventSequence)
SELECT ObjectName,
       SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM   CTE
GROUP  BY ObjectID,
          ObjectName  

-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2
GO

22voto

Tigerjz32 Points 2351

En supposant que vous utilisez Microsoft SQL Server Management Studio

  • Pour Plan de requête estimé vous pouvez appuyer sur Ctrl + L ou le bouton suivant.

enter image description here

  • Pour Plan de requête réel vous pouvez appuyer sur Ctrl + M ou le bouton suivant avant d'exécuter la requête.

enter image description here

  • Pour Plan d'interrogation en direct (uniquement dans SSMS 2016), utilisez le bouton suivant avant d'exécuter la requête.

enter image description here

17voto

Marcin Czyz Points 259

Outre les méthodes décrites dans les réponses précédentes, vous pouvez également utiliser un outil gratuit de visualisation du plan d'exécution et d'optimisation des requêtes. Plan d'ApexSQL (sur lequel je suis tombé récemment).

Vous pouvez installer et intégrer ApexSQL Plan dans SQL Server Management Studio, afin que les plans d'exécution puissent être visualisés directement depuis SSMS.

Visualisation des plans d'exécution estimés dans ApexSQL Plan

  1. Cliquez sur le bouton Nouvelle requête dans SSMS et collez le texte de la requête dans la fenêtre de texte de la requête. Cliquez avec le bouton droit de la souris et sélectionnez l'option "Display Estimated Execution Plan" dans le menu contextuel.

New Query button in SSMS

  1. Les diagrammes du plan d'exécution seront affichés dans l'onglet Plan d'exécution de la section des résultats. Ensuite, faites un clic droit sur le plan d'exécution et dans le menu contextuel, sélectionnez l'option "Ouvrir dans ApexSQL Plan".

Execution Plan

  1. Le plan d'exécution estimé sera ouvert dans ApexSQL Plan et il peut être analysé pour l'optimisation de la requête.

Estimated execution plan

Visualisation des plans d'exécution réels dans ApexSQL Plan

Pour visualiser le plan d'exécution réel d'une requête, continuez à partir de la deuxième étape mentionnée précédemment, mais maintenant, une fois que le plan estimé est affiché, cliquez sur le bouton "Réel" du ruban principal dans ApexSQL Plan.

click the “Actual” button from the main ribbon bar

Une fois que vous aurez cliqué sur le bouton "Réel", le plan d'exécution réel s'affichera avec un aperçu détaillé des paramètres de coût ainsi que d'autres données du plan d'exécution.

Actual execution plan

Vous trouverez plus d'informations sur la visualisation des plans d'exécution en suivant les indications suivantes ce lien .

16voto

Mon outil préféré pour obtenir et analyser en profondeur les plans d'exécution des requêtes est le suivant Explorateur de plans SQL Sentry . Il est beaucoup plus convivial, pratique et complet pour l'analyse détaillée et la visualisation des plans d'exécution que SSMS.

Voici un exemple de capture d'écran pour vous donner une idée des fonctionnalités offertes par l'outil :

SQL Sentry Plan Explorer window screen shot

Ce n'est qu'une des vues disponibles dans l'outil. Remarquez un ensemble d'onglets au bas de la fenêtre de l'application, qui vous permet d'obtenir différents types de représentation de votre plan d'exécution ainsi que des informations supplémentaires utiles.

En outre, je n'ai pas remarqué de limitations de son édition gratuite qui empêchent de l'utiliser au quotidien ou vous obligent à acheter la version Pro à terme. Donc, si vous préférez vous en tenir à l'édition gratuite, rien ne vous en empêche.

UPDATE : (Merci à Martin Smith ) Plan Explorer est maintenant gratuit ! Voir http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view pour les détails.

2 votes

Qui parlait d'outils tiers ?

12 votes

@basher : Le PO n'a pas limité les moyens avec des outils MS ou autre. Alors qu'est-ce qui vous fait penser qu'une réponse impliquant un outil tiers est une réponse inappropriée ?

3 votes

Je plaisantais juste sur la façon dont vous avez formulé le début de votre réponse. Speaking of third-party tools alors que personne n'a mentionné d'outils tiers.

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X