Forçage de plan optimisé avec le Magasin des requêtes

Article
05/30/2023

S’applique à : SQL Server 2022 (16.x)

L’optimisation des requêtes est un processus multi-phases de génération d’un plan d’exécution de requête « suffisant ». Dans certains cas, la compilation des requêtes, une partie de l’optimisation des requêtes, peut représenter un pourcentage important du temps d’exécution de requête global et consommer des ressources système importantes. Le forçage de plan optimisé fait partie de la famille intelligente de fonctionnalités de traitement des requêtes. Le forçage de plan optimisé réduit la surcharge de compilation pour les requêtes forcées répétées et nécessite que le Magasin des requêtes soit activé et en mode « lecture-écriture ». Une fois le plan d’exécution de requêtes généré, des étapes de compilation spécifiques sont stockées pour être réutilisées en tant que script de relecture d’optimisation. Un script de relecture d’optimisation est stocké dans le cadre du XML compressé du plan d’exécution de requêtes dans le Magasin des requêtes, dans un attribut OptimizationReplay caché.

Implémentation du forçage de plan optimisé

Quand une requête passe par le processus de compilation, un seuil basé sur l’estimation du temps passé dans la phase d’optimisation (en fonction de l’arborescence d’entrée de l’optimiseur de requête) détermine si un script de relecture d’optimisation est créé.

Une fois la compilation terminée, plusieurs métriques de runtime sont disponibles pour évaluer si l’estimation précédente était correcte. S’il est confirmé que le seuil a été franchi, le script de relecture d’optimisation est éligible à la persistance. Ces métriques de runtime incluent le nombre d’objets accessibles, le nombre de jointures, le nombre de tâches d’optimisation exécutées pendant l’optimisation et la durée réelle de l’optimisation.

L’avantage potentiel de l’utilisation d’un script de relecture d’optimisation est également comparé à la surcharge liée au stockage du script de relecture d’optimisation. Une estimation du temps relatif de relecture du script de relecture d’optimisation est comparée au temps passé à exécuter le processus d’optimisation normal, en fonction du nombre de tâches d’optimisation stockées dans le script de relecture d’optimisation et du nombre de tâches d’optimisation exécutées pendant la compilation normale. Si la relecture du script de relecture d’optimisation présente un avantage considérable en termes de réduction du temps de compilation, le script de relecture d’optimisation est conservé.

Considérations

Quand la fonctionnalité de forçage de plan optimisé est activée, les critères d’éligibilité pour le forçage de plan optimisé sont les suivants :

Seuls les plans de requête qui passent par l’optimisation complète sont éligibles, ce qui peut être vérifié par la présence de la propriété StatementOptmLevel="FULL".
Les instructions avec l’indicateur RECOMPILE et les requêtes distribuées ne sont pas éligibles.

Toutefois, si le Magasin des requêtes capture indépendamment un plan de requête qui a été délimité par le forçage de plan optimisé, le script de relecture d’optimisation est créé pour une deuxième recompilation de cette même requête, qui est soumise aux événements de recompilation par défaut. Découvrez-en plus sur la recompilation dans Recompilation des plans d’exécution.

Même si un script de relecture d’optimisation a été généré, il peut ne pas être conservé dans le Magasin des requêtes si les critères de stratégie de capture configurés du Magasin des requêtes ne sont pas respectés, notamment le nombre d’exécutions de cette instruction et ses temps de compilation et d’exécution cumulés. Dans ce cas, le script de relecture d’optimisation non valide est supprimé de la mémoire de façon asynchrone.

Activer et désactiver le forçage de plan optimisé

Vous pouvez activer ou désactiver le forçage de plan optimisé pour une base de données. Quand le forçage de plan optimisé est activé pour une base de données, vous pouvez le désactiver pour des requêtes individuelles avec l’indicateur de requête DISABLE_OPTIMIZED_PLAN_FORCING. Vous pouvez également désactiver le forçage de plan optimisé pour un plan de requête qui est forcé dans le Magasin des requêtes.

Activer ou désactiver le forçage de plan optimisé pour une base de données

Le forçage de plan optimisé est activé par défaut pour les nouvelles bases de données créées dans SQL Server 2022 (16.x) et versions ultérieures. Le Magasin des requêtes doit être activé pour chaque base de données où le forçage de plan optimisé est utilisé. Les instances mises à niveau avec des bases de données ou des bases de données existantes restaurées à partir d’une version inférieure de SQL Server auront optimisé le forçage de plan activé par défaut.

Pour activer le forçage de plan optimisé au niveau de la base de données, utilisez la configuration délimitée à la base de données ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON. Vous devez activer le Magasin des requêtes s’il n’est pas déjà activé. Découvrez un exemple de code dans l’exemple A ou apprenez-en davantage sur le Magasin des requêtes dans Superviser le niveau de performance avec le Magasin des requêtes.

Pour désactiver le forçage de plan optimisé au niveau de la base de données, utilisez la configuration délimitée à la base de données ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF.

Désactiver le forçage de plan optimisé avec un indicateur de requête

Quand la fonctionnalité de forçage de plan optimisé est activée dans une base de données, vous pouvez désactiver le forçage de plan optimisé pour une requête individuelle en utilisant l’indicateur de requêteDISABLE_OPTIMIZED_PLAN_FORCING.

Découvrez un exemple d’application de cet indicateur de requête dans l’exemple E.

Forcer un plan avec le Magasin des requêtes, mais désactiver le forçage de plan optimisé

La procédure sp_query_store_force_plan inclut un disable_optimized_plan_forcing paramètre. Pour utiliser ce paramètre, un paramètre supplémentaire est requis par la procédure stockée sp_query_store_force_plan. Le paramètre supplémentaire est appelé replica_group_id. Par défaut, le réplica principal replica_group_id a une valeur (1) même si aucun réplica secondaire n’est configuré.

Recherchez un exemple d’application des paramètres appropriés à la procédure stockée sp_query_store_force_plan dans l’exemple C.

L’affichage catalogue sys.query_store_plan inclut des colonnes qui indiquent si le plan a un script de relecture d’optimisation associé et ajoute un nouvel état à une colonne de raison d’échec existante spécifique au script de relecture d’optimisation associé. Découvrez-en plus dans sys.query_store_plan (Transact-SQL).

Examples

A. Activer le Magasin des requêtes et le forçage de plan optimisé pour une base de données

Le code suivant active le Magasin des requêtes sur une base de données, puis active le forçage de plan optimisé sur celle-ci. Découvrez-en plus sur les options permettant d’activer le Magasin des requêtes dans Options ALTER DATABASE SET (Transact-SQL).

Avant d’exécuter le code, connectez-vous à la base de données utilisateur appropriée.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. Sélectionner toutes les requêtes qui ont un script de relecture d’optimisation

L’exemple de code suivant sélectionne tous les query_ids qui ont un script de relecture d’optimisation dans le Magasin des requêtes. Connectez-vous à la base de données utilisateur appropriée avant d’exécuter l’exemple de code.

SELECT q.query_id,
    t.query_sql_text,
    p.plan_id,
    TRY_CAST(p.query_plan AS XML) AS query_plan,
    p.is_forced_plan,
    p.count_compiles
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
    ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. Forcer un plan et désactiver le forçage de plan optimisé dans le Magasin des requêtes

Le code suivant force un plan dans le Magasin des requêtes, mais désactive le forçage de plan optimisé. Avant d’exécuter le code suivant, remplacez @query_id et @plan_id par une combinaison appropriée pour votre instance. La procédure stockée sp_query_store_force_plan s’attend à ce que le @replica_group_id paramètre soit transmis en tant que troisième valeur de paramètre lors de la tentative de désactivation du forçage de plan optimisé dans le Magasin des requêtes. Cela peut être utilisé pour désactiver le forçage de plan optimisé pour un plan forcé particulier sur un réplica spécifique. La valeur 1 @replica_group_id=1 est utilisée pour désactiver la fonctionnalité sur le réplica principal.

EXEC sp_query_store_force_plan @query_id=148, @plan_id=4, @replica_group_id=1, @disable_optimized_plan_forcing=1;
GO

Découvrez-en plus dans sp_query_store_force_plan (Transact-SQL).

D. Sélectionner toutes les requêtes où le forçage de plan optimisé est désactivé par le Magasin des requêtes

L’exemple suivant interroge tous les plans qui ont été forcés dans le Magasin des requêtes où is_optimized_plan_forcing_disabled a été défini sur 1. Avant d’exécuter le code, connectez-vous à la base de données utilisateur appropriée.

SELECT q.query_id,
    t.query_sql_text,
    p.plan_id,
    TRY_CAST(p.query_plan AS XML) AS query_plan,
    p.is_forced_plan,
    p.count_compiles
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
    ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. Désactiver le forçage de plan optimisé pour une requête

L’exemple suivant désactive le forçage de plan optimisé pour une requête en utilisant l’indicateur de requêteDISABLE_OPTIMIZED_PLAN_FORCING. L’exemple utilise l’exemple de base de données AdventureWorks.

SELECT ProductID,
    OrderQty,
    SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID,
    OrderQty
ORDER BY ProductID,
    OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

Étapes suivantes

Découvrez-en plus sur le Magasin des requêtes et le forçage de plan optimisé dans les articles suivants :