sys.dm_exec_query_optimizer_info (Transact-SQL)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics 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 l’utilisation de diagnostic interne SQL Server. Ces compteurs indiquent la mention « Interne uniquement ».

Remarque

Pour appeler cela à partir d’Azure Synapse Analytics ou du système de plateforme Analytics (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 Identificateur du nœud sur lequel cette distribution est activée.

S’applique à : Azure Synapse Analytics, Analytics Platform System (PDW)

autorisations

SQL Server 2019 (15.x) et versions antérieures, et Azure SQL Managed Instance, nécessitent VIEW SERVER STATE une autorisation.

SQL Server 2022 (16.x) et versions ultérieures, nécessite VIEW SERVER PERFORMANCE STATE une autorisation sur le serveur.

Sur les objectifs de service Azure SQL Database Basic, S0 et S1, et pour les bases de données dans des pools élastiques, le compte d’administrateur du serveur, le compte d’administrateur Microsoft Entra ou l’appartenance au rôle serveur ##MS_ServerStateReader### est requis. 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. Toutes les valeurs des champs de valeur sont définies 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 générées par le système. L’exécution d’un plan déjà mis en cache ne change pas les valeurs en 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
tasks 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
timeout Interne uniquement Interne uniquement
memory limit exceeded Interne uniquement Interne uniquement
insert stmt Nombre d’optimisations destinées aux INSERT instructions. Non applicable
delete stmt Nombre d’optimisations destinées aux DELETE instructions. Non applicable
update stmt Nombre d’optimisations destinées aux UPDATE instructions. 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
tables Nombre total d'optimisations. Nombre moyen de tables référencées par requête optimisée.
hints Nombre de définitions d'un certain indicateur. Les indicateurs comptabilisés sont les suivants : JOIN, UNION GROUPet FORCE ORDER indicateurs de requête, FORCE PLAN option set et indicateurs de jointure. Non applicable
order hint Nombre de fois où l’ordre de jointure a été forcé. Ce compteur n’est pas limité à l’indicateur FORCE ORDER . La spécification d’un algorithme de jointure au sein d’une requête, telle qu’une INNER HASH JOINrequête, force également l’ordre de jointure, qui incrémente le compteur. Non applicable
join hint Nombre de fois que l'algorithme de jointure a été forcé par un indicateur de jointure. L’indicateur FORCE ORDER de requête n’incrémente pas ce compteur. Non applicable
view reference Nombre de fois qu’une vue est référencée dans une requête. Non applicable
remote query Nombre d’optimisations où la requête a référencé au moins une source de données distante, telle qu’une table avec un nom en quatre parties ou un OPENROWSET résultat. Non applicable
maximum DOP Nombre total d'optimisations. Valeur effective MAXDOP moyenne pour un plan optimisé. Par défaut, effective MAXDOP est déterminée par l’option de configuration maximale du serveur de parallélisme et peut être remplacée pour une requête spécifique par la valeur de l’indicateur MAXDOP de requête.
maximum recursion level Nombre d’optimisations dans lesquelles un MAXRECURSION niveau supérieur 0 à celui spécifié avec l’indicateur de requête. Niveau moyen MAXRECURSION dans les optimisations où un niveau de récursivité maximal a été spécifié avec 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 sont mises en correspondance. 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 dans lesquelles une demande de curseur dynamique a été spécifiée. Non applicable
fast forward cursor request Nombre d’optimisations dans lesquelles une demande de curseur à transfert rapide a été spécifiée. Non applicable
merge stmt Nombre d’optimisations destinées aux MERGE instructions. Non applicable

Exemples

R. Afficher les statistiques sur l’exécution de l’optimiseur

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

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Afficher le 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;

E. Afficher le nombre total d’indicateurs pendant l’optimisation

Combien d’indicateurs sont comptabilisés lorsqu’ils FORCE ORDER sont inclus en tant qu’indicateur de requête ?

-- Check hint count before query execution
SELECT ISNULL('', 0) AS [Before],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

SELECT poh.PurchaseOrderID,
    poh.OrderDate,
    pod.ProductID,
    pod.DueDate,
    poh.VendorID
FROM Purchasing.PurchaseOrderHeader AS poh
INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod
    ON poh.PurchaseOrderID = pod.PurchaseOrderID
OPTION (
    FORCE ORDER,
    RECOMPILE
);

-- check hint count after query execution
SELECT ISNULL('', 0) AS [After],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);