sys.dm_exec_query_optimizer_info (Transact-SQL)

S’applique à :yesSQL Server (toutes les versions prises en charge) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform System (PDW)

Retourne des statistiques détaillées sur l’opération de l’optimiseur de requête SQL Server. Vous pouvez utiliser cette vue lorsque vous paramétrez une charge de travail pour identifier des problèmes ou des améliorations d'optimisation des requêtes. Par exemple, vous pouvez utiliser le nombre total des optimisations, la valeur du temps écoulé et la valeur de coût final pour comparer les optimisations de requête de la charge en cours et les modifications observées au cours du processus de paramétrage. Certains compteurs fournissent des données pertinentes uniquement pour SQL Server utilisation de diagnostic interne. Ces compteurs indiquent la mention « Interne uniquement ».

Notes

Pour appeler cela à partir de Azure Synapse Analytics ou Analytics Platform System (PDW), utilisez le nom sys.dm_pdw_nodes_exec_query_optimizer_info. Cette syntaxe n’est pas prise en charge par le pool SQL serverless dans Azure Synapse Analytics.

Name Type de données Description
Counter nvarchar(4000) Nom de l'événement statistique de l'optimiseur.
occurrence bigint Nombre d'occurrences de l'événement d'optimisation pour ce compteur.
value float Valeur moyenne de la propriété par occurrence de l'événement.
pdw_node_id int S’applique à : Azure Synapse Analytics, Analytics Platform System (PDW)

Identificateur du nœud sur lequel cette distribution est activée.

Autorisations

Sur SQL Server et SQL Managed Instance, l’autorisation VIEW SERVER STATE est requise.

Pour les objectifs de service De base, S0 et S1 de SQL Database, et les bases de données situées dans des pools élastiques, le compte d’administrateur du serveur, le compte d’administrateur Azure Active Directory ou l’appartenance au ##MS_ServerStateReader##rôle serveur est nécessaire. Sur tous les autres objectifs de service SQL Database, l’autorisation VIEW DATABASE STATE sur la base de données ou l’appartenance au rôle serveur ##MS_ServerStateReader## est requise.

Notes

sys.dm_exec_query_optimizer_info contient les propriétés suivantes (compteurs). Toutes les valeurs d'occurrence sont cumulatives et sont définies à 0 au redémarrage du système. Tous les champs de valeurs sont initialisés à NULL au redémarrage du système. Toutes les colonnes de valeurs qui indiquent une moyenne utilisent la valeur d'occurrence de la ligne comme dénominateur pour le calcul de la moyenne. Toutes les optimisations de requête sont mesurées lorsque SQL Server détermine les modifications apportées à dm_exec_query_optimizer_info, y compris les requêtes générées par l’utilisateur et le système. L’exécution d’un plan déjà mis en cache ne modifie pas les valeurs dans dm_exec_query_optimizer_info, seules les optimisations sont significatives.

Compteur Occurrence Valeur
optimizations Nombre total d'optimisations. Non applicable
elapsed time Nombre total d'optimisations. Temps moyen écoulé par optimisation d'une instruction (requête) individuelle, en secondes.
final cost Nombre total d'optimisations. Estimation du coût moyen d'un plan optimisé en unités de coût internes.
trivial plan Interne uniquement Interne uniquement
tâches Interne uniquement Interne uniquement
no plan Interne uniquement Interne uniquement
search 0 Interne uniquement Interne uniquement
search 0 time Interne uniquement Interne uniquement
search 0 tasks Interne uniquement Interne uniquement
search 1 Interne uniquement Interne uniquement
search 1 time Interne uniquement Interne uniquement
search 1 tasks Interne uniquement Interne uniquement
search 2 Interne uniquement Interne uniquement
search 2 time Interne uniquement Interne uniquement
search 2 tasks Interne uniquement Interne uniquement
gain stage 0 to stage 1 Interne uniquement Interne uniquement
gain stage 1 to stage 2 Interne uniquement Interne uniquement
délai d'expiration Interne uniquement Interne uniquement
memory limit exceeded Interne uniquement Interne uniquement
insert stmt Nombre d'optimisations destinées à des instructions INSERT. Non applicable
delete stmt Nombre d'optimisations destinées à des instructions DELETE. Non applicable
update stmt Nombre d'optimisations destinées à des instructions UPDATE. Non applicable
contains subquery Nombre d'optimisations associées à une requête qui contient au moins une sous-requête. Non applicable
unnest failed Interne uniquement Interne uniquement
dans des tables Nombre total d'optimisations. Nombre moyen de tables référencées par requête optimisée.
indications Nombre de définitions d'un certain indicateur. Les indicateurs pris en charge sont : les indicateurs de requête JOIN, GROUP, UNION et FORCE ORDER, l'option de configuration FORCE PLAN et les indicateurs de jointure. Non applicable
indicateur de commande Nombre de définitions d'un indicateur de commande forcée. Non applicable
indicateur de jointure Nombre de fois que l'algorithme de jointure a été forcé par un indicateur de jointure. Non applicable
view reference Nombre de fois qu'une vue a été référencée dans une requête Non applicable
requête distante Nombre d'optimisations dans lesquelles la requête faisait référence à au moins une source de données distante, par exemple une table dont le nom est en quatre parties ou un jeu de résultats OPENROWSET. Non applicable
maximum DOP Nombre total d'optimisations. Valeur moyenne réelle de MAXDOP pour un plan optimisé. Par défaut, maxDOP effectif est déterminé par le degré maximal de configuration du serveur parallélisme et peut être remplacé par une requête spécifique par la valeur de l’indicateur de requête MAXDOP.
maximum recursion level Nombre d'optimisations dans lesquelles un niveau MAXRECURSION supérieur à 0 a été spécifié à l'aide de l'indicateur de requête. Niveau MAXRECURSION moyen dans les optimisations où un niveau de récursivité maximum est spécifié à l'aide de l'indicateur de requête.
indexed views loaded Interne uniquement Interne uniquement
indexed views matched Nombre d'optimisations où une ou plusieurs vues indexées ont été trouvées. Nombre moyen de vues mises en correspondance.
indexed views used Nombre d'optimisations où une ou plusieurs vues indexées sont utilisées dans le plan de sortie après avoir trouvé leur correspondance. Nombre moyen de vues utilisées.
indexed views updated Nombre d'optimisations d'une instruction DML produisant un plan qui tient à jour une ou plusieurs vues indexées. Nombre moyen de vues tenues à jour.
dynamic cursor request Nombre d'optimisations où une demande de curseur dynamique a été spécifiée. Non applicable
fast forward cursor request Nombre d'optimisations où une demande de curseur vers l'avant a été spécifiée. Non applicable
merge stmt Nombre d'optimisations destinées à des instructions MERGE. Non applicable

Exemples

R. Affichage de statistiques sur l'exécution de l'optimiseur

Quelles sont les statistiques actuelles d’exécution de l’optimiseur pour cette instance de SQL Server ?

SELECT * FROM sys.dm_exec_query_optimizer_info;  

B. Affichage du nombre total d'optimisations

Quel est le nombre d'optimisations effectué ?

SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info  
WHERE counter = 'optimizations';  

C. Temps moyen écoulé par optimisation

Quel est le temps moyen consacré à chaque optimisation ?

SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization  
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time';  

D. Proportion des optimisations qui impliquent des sous-requêtes

Quelle est la proportion des requêtes optimisées qui contenaient une sous-requête ?

SELECT (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery') /  
       (SELECT CAST (occurrence AS float)   
        FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')  
        AS ContainsSubqueryFraction;  

Voir aussi

Fonctions et vues de gestion dynamique (Transact-SQL)
Fonctions et vues de gestion dynamique relatives à l'exécution (Transact-SQL)