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.
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.
À 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 !
- 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.
- 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.
- 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.
- Attendez que la requête soit terminée et arrêtez la trace.
- 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 :
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.