Forçage de plan optimisé avec le Magasin des requêtes
S’applique à : 
SQL Server 2022 (16.x) Base de données Azure SQL
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 de fonctionnalités de traitement intelligent 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é
Lorsqu’une requête passe d’abord par le processus de compilation, un seuil basé sur l’estimation du temps passé dans l’optimisation (basé sur 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. Si le Moteur de base de données confirme 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. Cette estimation est basée sur le nombre de tâches d’optimisation stockées dans le script de relecture d’optimisation et le 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é.
À propos de l’installation
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, sous réserve d’é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 se peut qu’il ne soit pas conservé dans l’Magasin des requêtes si les critères des stratégies de capture configurées Magasin des requêtes ne sont pas satisfaits, 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 manière 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. Lorsque le forçage de plan optimisé est activé pour une base de données, vous pouvez le désactiver pour des requêtes individuelles à l’aide de l’indicateur DISABLE_OPTIMIZED_PLAN_FORCING de requête. Vous pouvez également désactiver le forçage de plan optimisé pour un plan de requête forcé dans 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 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 existantes ou des bases de données restaurées à partir d’une version inférieure de SQL Server ont activé le forçage de plan optimisé 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ête DISABLE_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 paramètre disable_optimized_plan_forcing. Pour utiliser ce paramètre, un paramètre supplémentaire est requis par la sp_query_store_force_plan procédure stockée. 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 dans le cas où il n’existe aucun réplica secondaire configuré.
Recherchez un exemple d’application des paramètres appropriés à la sp_query_store_force_plan procédure stockée 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é. En savoir plus dans sys.query_store_plan.
Exemples
Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.
R : 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. En savoir plus sur les options permettant d’activer Magasin des requêtes dans les options ALTER DATABASE SET.
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 sp_query_store_force_plan procédure stockée 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 plan optimisé forçant dans Magasin des requêtes. Elle peut être utilisée pour désactiver le forçage de plan optimisé pour un plan forcé particulier sur un réplica spécifique. La valeur utilisée @replica_group_id = 1 pour désactiver la fonctionnalité sur le réplica principal est utilisée.
EXECUTE sp_query_store_force_plan
@query_id = 148,
@plan_id = 4,
@replica_group_id = 1,
@disable_optimized_plan_forcing = 1;
GO
En savoir plus dans sp_query_store_force_plan.
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 Magasin des requêtes où is_optimized_plan_forcing_disabled est défini 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ête DISABLE_OPTIMIZED_PLAN_FORCING.
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