En plus de la réponse complète déjà affichée, il est parfois utile de pouvoir accéder au plan d'exécution par programme pour extraire des informations. 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 De

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 De

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

En supposant que vous utilisez Microsoft SQL Server Management Studio

• pour plan de requête estimé , vous pouvez appuyer sur Ctrl + l{[7] } ou sur le bouton Suivant.

• pour plan de requête réel, vous pouvez appuyer sur Ctrl + M ou le bouton Suivant avant d'exécuter la requête.

• pour Live Query Plan , (uniquement dans SSMS 2016) utilisez le bouton Suivant avant l'exécution de la requête.

À côté des méthodes décrites dans les réponses précédentes, vous pouvez également utiliser un visualiseur de plan d'exécution gratuit et un outil d'optimisation de requête ApexSQL Plan (que j'ai récemment rencontré).

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

Affichage 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 le fenêtre de texte de requête. Faites un clic droit et sélectionnez L'option" Afficher le Plan D'exécution estimé " dans le menu contextuel.

1. Les diagrammes du plan d'exécution s'affichent sous l'onglet Plan d'exécution dans la section résultats. Ensuite, cliquez avec le bouton droit sur le plan d'exécution et dans le menu contextuel, sélectionnez L'option" Ouvrir dans le Plan ApexSQL".

1. le plan D'exécution estimé sera ouvert dans ApexSQL Plan et il peut être analysé pour optimisation des requêtes.

Affichage des plans D'exécution réels dans ApexSQL Plan

Pour afficher le plan D'exécution réel d'une requête, continuez à partir de la 2ème étape mentionnée précédemment, mais maintenant, une fois le plan estimé affiché, cliquez sur le bouton "réel" de la barre de ruban principale dans ApexSQL Plan.

Une fois le bouton" réel " cliqué, le plan D'exécution réel sera affiché avec un aperçu détaillé du coût paramètres ainsi que d'autres données de plan d'exécution.

Plus d'informations sur l'affichage des plans d'exécution peut être trouvé en suivant ce lien.

Mon outil préféré pour obtenir et analyser en profondeur les plans d'exécution des requêtes est 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 avoir une idée de la fonctionnalité offerte par l'outil:

Ce n'est qu'une des vues disponibles dans l'outil. Notez 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 et des informations supplémentaires utiles.

En outre, je n'ai pas remarqué de limitations de son édition gratuite qui empêche de l'utiliser au quotidien ou vous oblige à acheter la version Pro éventuellement. Donc, si vous préférez rester avec l'édition gratuite, rien ne vous interdit de le faire.

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

Les plans de requête peuvent être obtenus à partir d'une session D'Événements étendus via l'événement query_post_execution_showplan. Voici un exemple de session XEvent:

/*
    Generated via "Query Detail Tracking" template.
*/
CREATE EVENT SESSION [GetExecutionPlan] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)),

/* Remove any of the following events (or include additional events) as desired. */
ADD EVENT sqlserver.error_reported(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.module_end(SET collect_statement=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))) 
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Après avoir créé la session, (dans SSMS) allez dans L'Explorateur D'objets et plongez dans Gestion | événements / Sessions étendus. Cliquez avec le bouton droit sur la session "GetExecutionPlan" et démarrez-la. Cliquez à nouveau avec le bouton droit de la souris et sélectionnez "regarder les données en direct".

Ensuite, ouvrez une nouvelle fenêtre de requête et exécutez une ou plusieurs requêtes. Voici une pour le AdventureWorks:

USE AdventureWorks;
GO

SELECT p.Name AS ProductName, 
    NonDiscountSales = (OrderQty * UnitPrice),
    Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p 
INNER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID 
ORDER BY ProductName DESC;
GO

Après un moment ou deux, vous devriez voir certains résultats dans L'onglet" Getexecutionplan: Live Data". Cliquez sur l'un des événements query_post_execution_showplan dans la grille, puis cliquez sur l'onglet "Plan de requête" sous la grille. Il devrait ressembler à ceci:

EDIT : le code XEvent et la capture d'écran ont été générés à partir de SQL / SSMS 2012 w / SP2. Si vous utilisez SQL 2008 / R2, vous pourriez être capable de modifier le script pour le faire fonctionner. Mais cette version n'a pas D'interface graphique, vous devrez donc extraire le XML showplan, l'enregistrer en tant que *.fichier sqlplan et ouvrez-le dans SSMS. Que du lourd. XEvents n'existait pas dans SQL 2005 ou plus tôt. Donc, si vous n'êtes pas sur SQL 2012 ou plus tard, je suggère fortement l'une des autres réponses affichées ici.

À partir de SQL Server 2016+, la fonctionnalité Query Store a été introduite pour surveiller les performances. Il fournit un aperçu du choix et des performances du plan de requête. Ce n'est pas un remplacement complet des événements trace ou extended, mais comme il évolue de version en version, nous pourrions obtenir un magasin de requêtes entièrement fonctionnel dans les futures versions de SQL Server. Le flux principal du magasin de requêtes

1. Les composants existants de SQL Server interagissent avec le magasin de requêtes en utilisant le magasin de requêtes Gestionnaire.
2. Query Store Manager détermine quel magasin doit être utilisé, puis transmet l'exécution à ce magasin (statistiques de Plan ou D'exécution ou statistiques D'attente de requête)
   • Magasin de Plan-persistance des informations du plan d'exécution
   • Magasin de statistiques D'exécution-persistance des informations sur les statistiques d'exécution
   • Magasin de statistiques D'attente de requête-informations sur les statistiques D'attente persistantes.
3. Plan, statistiques D'exécution et Wait store utilise Query Store comme extension de SQL Serveur.

1. Activation du magasin de requêtes : Le Magasin de requêtes fonctionne au niveau de la base de données sur le serveur.

   • Le Magasin de requêtes n'est pas actif par défaut pour les nouvelles bases de données.
   • vous ne pouvez pas activer le magasin de requêtes pour la base de données maître ou tempdb.
   • DMV disponible

     sys.database_query_store_options (Transact-SQL)

2. Collecter des informations dans le magasin de requêtes : nous collectons toutes les informations disponibles à partir des trois magasins en utilisant Query Store DMV (vues de gestion de données).

   • Magasin De Plan De Requête: La persistance des informations du plan d'exécution et il est responsable de la capture de toutes les informations liées à la compilation des requêtes.

     sys.query_store_query (Transact-SQL) sys.query_store_plan (Transact-SQL) sys.query_store_query_text (Transact-SQL)

   • Magasin De Statistiques D'Exécution: La persistance de l' informations statistiques d'exécution et il est probablement le magasin le plus fréquemment mis à jour. Ces statistiques représentent des données d'exécution de requête.

     sys.query_store_runtime_stats (Transact-SQL)

   • Requête Attente Stats Magasin: Persistance et capture des informations statistiques d'attente.

     sys.query_store_wait_stats (Transact-SQL)

Remarque: Query wait stats Store est disponible uniquement dans SQL Server 2017 +

Voici une chose importante à savoir en plus de tout ce qui a été dit auparavant.

Les plans de requête sont souvent trop complexes pour être représentés par le type de colonne XML intégré qui a une limitation de 127 niveaux d'éléments imbriqués. C'est l'une des raisons pour lesquelles sys.dm_exec_query_plan peut renvoyer NULL ou même lancer une erreur dans les versions antérieures de MS SQL, il est donc généralement plus sûr d'utiliser sys.dm_exec_text_query_plan à la place. Ce dernier a également une fonctionnalité de bonus utile de sélectionner un plan pour une instruction particulière plutôt que le lot entier. Voici comment vous l'utilisez pour afficher les plans des instructions en cours d'exécution:

SELECT p.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_text_query_plan(
                r.plan_handle,
                r.statement_start_offset,
                r.statement_end_offset) AS p

La colonne de texte dans la table résultante n'est cependant pas très pratique par rapport à une colonne XML. Pour pouvoir cliquer sur le résultat à ouvrir dans un onglet séparé en tant que diagramme, sans avoir à enregistrer son contenu dans un fichier, vous pouvez utiliser un petit truc (rappelez-vous que vous ne pouvez pas simplement utiliser CAST(... AS XML)), bien que cela ne fonctionne que pour un seul ligne:

SELECT Tag = 1, Parent = NULL, [ShowPlanXML!1!!XMLTEXT] = query_plan
FROM sys.dm_exec_text_query_plan(
                -- set these variables or copy values
                -- from the results of the above query
                @plan_handle,
                @statement_start_offset,
                @statement_end_offset)
FOR XML EXPLICIT

Comme avec SQL Server Management Studio (déjà expliqué), C'est aussi possible avec Datagrip comme expliqué ici.

1. cliquez avec le bouton droit sur une instruction SQL et sélectionnez expliquer le plan.
2. dans le volet sortie, cliquez sur Plan.
3. par défaut, vous voyez la représentation arborescente de la requête. Pour voir la plan de requête, cliquez sur L'icône Afficher la visualisation ou appuyez sur Ctrl + Maj + Alt + U