Remarque
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
S’applique à :
Sql Server 2022 (16.x) Base de données SQL Azure SQL Databasedans Microsoft Fabric
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 des requêtes répétées forcé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 comparative du temps nécessaire pour exécuter le script de répétition de l’optimisation est comparée au temps consacré à l’exécution du processus d’optimisation habituel. 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é.
Considerations
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 Magasin des requêtes capture indépendamment un plan de requête qui a été exclu du périmètre du forçage de plan optimisé, le script de relecture de l’optimisation est créé lors d’une deuxième recompilation de cette même requête, sous réserve des é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 le Magasin des requêtes si les critères des stratégies de capture configurées pour le Magasin des requêtes ne sont pas remplis, notamment le nombre d’exécutions de cette instruction ainsi que ses temps cumulés de compilation et d’exécution. Dans ce cas, le script de reprise de l’optimisation invalide est supprimé de la mémoire de façon asynchrone.
Activer et désactiver le forçage optimisé du plan
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 spécifiques en utilisant 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 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 supérieures. Magasin des requêtes doit être activé pour chaque base de données dans laquelle le forçage de plan optimisé est utilisé. Les instances mises à niveau avec des bases de données existantes, ou les bases de données restaurées à partir d’une version antérieure de SQL Server, ont le forçage de plan optimisé activé par défaut.
Pour activer le forçage optimisé du plan au niveau de la base de données, utilisez la configuration étendue à 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 limitée à la base de données ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF.
Désactiver le forçage optimisé du plan avec un indicateur de requête
Lorsque 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 spécifique à l’aide de l’DISABLE_OPTIMIZED_PLAN_FORCINGquery hint.
Découvrez un exemple d’application de cet indicateur de requête dans l’exemple E.
Forcer un plan avec Magasin des requêtes, mais désactiver le forçage optimisé du plan
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, la réplique principale @replica_group_id a une valeur de un (1) même lorsqu’il n’y a aucune réplique secondaire configurée.
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.
Examples
Les exemples de code de cet article utilisent les bases de données d'exemple AdventureWorks2025 ou AdventureWorksDW2025, que vous pouvez télécharger à partir de la page d'accueil Microsoft SQL Server Samples and Community Projects.
A. Activer Magasin des requêtes et le forçage de plan optimisé pour une base de données
Le code suivant active Magasin des requêtes pour une base de données, puis active le forçage de plan optimisé pour cette base de données. En savoir plus sur les options permettant d’activer Magasin des requêtes dans ALTER DATABASE SET les options.
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 pour lesquels il existe un script de relecture de l’optimisation dans 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 optimisé de plan dans Magasin des requêtes
Le code suivant force un plan dans Magasin des requêtes, mais désactive le forçage optimisé du plan. 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 attend que le paramètre @replica_group_id soit transmis comme 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. Ceci peut être utilisé pour désactiver le forçage optimisé du plan pour un plan forcé particulier sur une réplique spécifique. Une valeur de @replica_group_id = 1 est utilisée pour désactiver la fonctionnalité sur le réplica principal.
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 pour lesquelles le forçage de plan optimisé est désactivé par 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’DISABLE_OPTIMIZED_PLAN_FORCINGindication de requête.
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