Partage via

sys.query_store_plan (Transact-SQL)

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Contient des informations sur chaque plan d’exécution associé à une requête.

Nom de la colonne Type de données Description
plan_id bigint Clé primaire
query_id bigint Clé étrangère. Se joint à sys.query_store_query_text (Transact-SQL).
plan_group_id bigint ID du groupe de plans. Les requêtes de curseur exigent généralement plusieurs plans (remplir et extraire). Les plans de type remplir et extraire qui sont compilés ensemble se trouvent dans le même groupe.

0 signifie que le plan n’est pas dans un groupe.
engine_version nvarchar(32) Version du moteur utilisée pour compiler le plan au <major>.<minor>.<build>.<revision> format.
compatibility_level smallint Niveau de compatibilité de la base de données mentionnée dans la requête.
query_plan_hash binary(8) Hachage MD5 du plan individuel.
query_plan nvarchar(max) Plan d’exécution de requêtes XML du plan de requête.
is_online_index_plan bit Le plan a été utilisé lors d’une opération de construction d’index.

Remarque : Azure Synapse Analytics retourne 0toujours .
is_trivial_plan bit Le plan est un plan trivial (sortie à l’étape 0 de l’optimiseur de requête).

Remarque : Azure Synapse Analytics retourne 0toujours .
is_parallel_plan bit Le plan est parallèle.

Remarque : Azure Synapse Analytics retourne 1toujours .
is_forced_plan bit Le plan est marqué comme forcé lorsque l’utilisateur exécute la procédure sys.sp_query_store_force_planstockée. Le mécanisme de forçage ne garantit pas que ce plan exact sera utilisé pour la requête référencée par query_id. Le forçage de plan entraîne la compilation de la requête, et produit généralement exactement le même plan ou un plan similaire au plan référencé par plan_id. Si le forçage de plan ne réussit pas, force_failure_count est incrémenté et last_force_failure_reason est rempli avec la raison de l’échec.

Remarque : Azure Synapse Analytics retourne 0toujours .
is_natively_compiled bit Le plan inclut des procédures à mémoire optimisée compilées en mode natif (0 = FALSE, 1 = TRUE).

Remarque : Azure Synapse Analytics retourne 0toujours .
force_failure_count bigint Nombre de fois où le forçage de ce plan a échoué. Il ne peut être incrémenté que lorsque la requête est recompilée (et non à chaque exécution). Réinitialise à 0 chaque fois is_plan_forced qu’elle est passée FALSE de à TRUE.

Remarque : Azure Synapse Analytics retourne 0toujours .
last_force_failure_reason int Raison pour laquelle le forçage de plan a échoué.

0 : aucun échec, sinon numéro de l’erreur ayant provoqué l’échec du forçage
3617: COMPILATION_ABORTED_BY_CLIENT
8637: ONLINE_INDEX_BUILD
8675: OPTIMIZATION_REPLAY_FAILED
8683: INVALID_STARJOIN
8684: TIME_OUT
8689: NO_DB
8690: HINT_CONFLICT
8691: SETOPT_CONFLICT
8694: DQ_NO_FORCING_SUPPORTED
8698: NO_PLAN
8712: NO_INDEX
8713: VIEW_COMPILE_FAILED
<autre valeur> : GENERAL_FAILURE

Remarque : Azure Synapse Analytics retourne 0toujours .
last_force_failure_reason_desc nvarchar(128) Description textuelle de last_force_failure_reason.

COMPILATION_ABORTED_BY_CLIENT: compilation de requête abandonnée du client avant sa fin
ONLINE_INDEX_BUILD: la requête tente de modifier les données tandis que la table cible a un index en cours de création en ligne
OPTIMIZATION_REPLAY_FAILED: le script de relecture d’optimisation n’a pas pu s’exécuter.
INVALID_STARJOIN: plan contient une spécification StarJoin non valide
TIME_OUT: l’optimiseur a dépassé le nombre d’opérations autorisées lors de la recherche d’un plan spécifié par un plan forcé
NO_DB: une base de données spécifiée dans le plan n’existe pas
HINT_CONFLICT: La requête ne peut pas être compilée, car le plan est en conflit avec un indicateur de requête
DQ_NO_FORCING_SUPPORTED: Impossible d’exécuter une requête, car le plan est en conflit avec l’utilisation d’opérations de requête distribuée ou de texte intégral.
NO_PLAN: le processeur de requêtes n’a pas pu produire de plan de requête, car le plan forcé n’a pas pu être vérifié comme valide pour la requête
NO_INDEX: l’index spécifié dans le plan n’existe plus
VIEW_COMPILE_FAILED: Impossible de forcer le plan de requête en raison d’un problème dans une vue indexée référencée dans le plan
GENERAL_FAILURE: erreur de forçage générale (non couverte par d’autres raisons)

Remarque : Azure Synapse Analytics retourne NONEtoujours .
count_compiles bigint Statistiques de compilation du plan.
initial_compile_start_time datetimeoffset Statistiques de compilation du plan.
last_compile_start_time datetimeoffset Statistiques de compilation du plan.
last_execution_time datetimeoffset Dernière heure de fin de la requête ou du plan.
avg_compile_duration float Planifier les statistiques de compilation, en microsecondes. Divisez de 1 000 000 pour obtenir des secondes.
last_compile_duration bigint Planifier les statistiques de compilation, en microsecondes. Divisez de 1 000 000 pour obtenir des secondes.
plan_forcing_type int S’applique à : SQL Server 2017 (14.x) et versions ultérieures

Type de forçage de plan.

0: NONE
1: MANUAL
2: AUTO
plan_forcing_type_desc nvarchar(60) S’applique à : SQL Server 2017 (14.x) et versions ultérieures

Description textuelle de plan_forcing_type.

NONE: Aucun plan forçant
MANUAL: Planifier forcé par l’utilisateur
AUTO: Planifier forcé par le réglage automatique.
has_compile_replay_script bit S’applique à : SQL Server 2022 (16.x) et versions ultérieures

Indique si un script de relecture d’optimisation est associé au plan :
0 = aucun script de relecture d’optimisation (ou même un script non valide).
1 = script de relecture d’optimisation enregistré.

Non applicable à Azure Synapse Analytics.
is_optimized_plan_forcing_disabled bit S’applique à : SQL Server 2022 (16.x) et versions ultérieures

Indique si le forçage de plan optimisé a été désactivé pour le plan :
0 = Désactivé.
1 = non désactivé.

Non applicable à Azure Synapse Analytics.
plan_type int S’applique à : SQL Server 2022 (16.x) et versions ultérieures

Type de plan.
0 : Plan compilé
1 : Plan de répartiteur
2 : Plan variant de requête

Non applicable à Azure Synapse Analytics.
plan_type_desc nvarchar(120) S’applique à : SQL Server 2022 (16.x) et versions ultérieures

Description textuelle du type de plan.
Plan compilé : indique que le plan est un plan non sensible aux paramètres optimisé
Plan de répartiteur : indique que le plan est un plan de répartiteur optimisé pour les paramètres
Plan variant de requête : indique que le plan est un plan de variante de requête optimisé pour les paramètres

Non applicable à Azure Synapse Analytics.

Notes

Plusieurs plans peuvent être forcés lorsque Magasin des requêtes pour les réplicas secondaires est activé.

Dans Azure Synapse Analytics, à l’aide de colonnes has_compile_replay_script, is_optimized_plan_forcing_disabled, plan_typeplan_type_desc entraîne une Invalid Column Name erreur, car elles ne sont pas prises en charge. Consultez l’exemple B pour obtenir un exemple d’utilisation sys.query_store_plan dans Azure Synapse Analytics.

Limites de l'application forcée d'un plan

Le Magasin des requêtes a un mécanisme qui permet de forcer l’optimiseur de requête à utiliser un certain plan d’exécution. Toutefois, il existe certaines limitations qui peuvent empêcher l’application d’un plan.

Premièrement, si le plan contient les constructions suivantes :

  • Insérer une instruction en bloc
  • Référence à une table externe
  • Requête distribuée ou opérations de recherche en texte intégral
  • Utilisation de requêtes élastiques
  • Curseurs dynamiques ou de jeux de clés
  • Spécification de jointure en étoile non valide

Remarque

Azure SQL Database et SQL Server 2019 et versions ultérieures prennent en charge le plan forçant les curseurs statiques et rapides vers l’avant.

Deuxièmement, quand les objets sur lesquels s’appuie le plan ne sont plus disponibles :

  • Base de données (si la base de données, où provient le plan, n’existe plus)
  • Index (absent ou désactivé)

Enfin, s’il y a des problèmes avec le plan lui-même :

  • Non conforme pour la requête
  • L’optimiseur de requête a dépassé le nombre d’opérations autorisées
  • Code XML du plan incorrect

autorisations

Nécessite l’autorisation VIEW DATABASE STATE.

Exemples

R. Recherchez la raison pour laquelle SQL Server n’a pas pu forcer un plan via QDS

Faites attention aux colonnes et force_failure_count aux last_force_failure_reason_desc colonnes :

SELECT TOP 1000
    p.query_id,
    p.plan_id,
    p.last_force_failure_reason_desc,
    p.force_failure_count,
    p.last_compile_start_time,
    p.last_execution_time,
    q.last_bind_duration,
    q.query_parameterization_type_desc,
    q.context_settings_id,
    c.set_options,
    c.STATUS
FROM sys.query_store_plan p
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_context_settings c
    ON c.context_settings_id = q.context_settings_id
LEFT JOIN sys.query_store_query_text t
    ON q.query_text_id = t.query_text_id
WHERE p.is_forced_plan = 1
    AND p.last_force_failure_reason != 0;

B. Requête pour afficher les résultats du plan de requête dans Azure Synapse Analytics

Utilisez l’exemple de requête suivant pour rechercher les 100 plans d’exécution les plus récents dans le Magasin des requêtes dans Azure Synapse Analytics.

SELECT TOP 100
    plan_id,
    query_id,
    plan_group_id,
    engine_version,
    compatibility_level,
    query_plan_hash,
    query_plan,
    is_online_index_plan,
    is_trivial_plan,
    is_parallel_plan,
    is_forced_plan,
    is_natively_compiled,
    force_failure_count,
    last_force_failure_reason,
    last_force_failure_reason_desc,
    count_compiles,
    initial_compile_start_time,
    last_compile_start_time,
    last_execution_time,
    avg_compile_duration,
    last_compile_duration,
    plan_forcing_type,
    plan_forcing_type_desc
FROM sys.query_store_plan
ORDER BY last_execution_time DESC;

Contenu connexe