optimize for ad hoc workloads (option de configuration de serveur)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

L'option optimize for ad hoc workloads permet d'améliorer l'efficacité du cache du plan pour les charges de travail qui contiennent de nombreux lots ad hoc à usage unique. Lorsque cette option a la valeur 1, le moteur de base de données stocke un petit stub du plan compilé dans le cache du plan lorsqu’un lot est compilé pour la première fois, au lieu du plan compilé complet. Cette option peut contribuer à réduire la sollicitation de la mémoire en évitant que le cache du plan ne devienne rempli avec des plans compilés qui ne sont pas réutilisés. Toutefois, l’activation de cette option peut affecter votre capacité à résoudre les problèmes liés aux plans à usage unique.

Le stub de plan compilé permet au moteur de base de données de reconnaître que ce lot ad hoc a été compilé antérieurement ; il ne stocke qu'un stub de plan compilé. Lorsque ce lot est de nouveau invoqué (compilé ou exécuté), le moteur de base de données compile le lot, supprime le stub du plan compilé du cache du plan et ajoute le plan compilé complet au cache du plan.

Vous pouvez trouver des stubs de plan compilés en interrogeant l’affichage catalogue sys.dm_exec_cached_plans et en recherchant « Plan compilé » dans la colonne cacheobjtype. Le stub a un plan_handle unique. Aucun plan d’exécution n’est associé au stub du plan compilé, et interroger le handle du plan ne retourne pas de plan d’exécution de requêtes graphique ou XML.

L’indicateur de trace 8032 rétablit les paramètres de limitation du cache au paramètre RTM SQL Server 2005 (9.x) qui permet en général aux caches d’être plus volumineux. Utilisez ce paramètre quand les entrées du cache fréquemment utilisées ne tiennent pas dans le cache et que l’option de configuration de serveur optimize for ad hoc workloads ne permet pas de résoudre le problème avec le cache du plan.

Remarques

Le fait de définir l’option optimize for ad hoc workloads sur 1 affecte uniquement les nouveaux plans. Les plans qui se trouvent déjà dans le cache du plan ne sont pas affectés.

Pour affecter immédiatement les plans de requête déjà mis en cache, le cache du plan doit être désactivé à l’aide de ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, ou SQL Server doit redémarrer.

Recommandations

Évitez d’avoir un grand nombre de plans à usage unique dans le cache du plan. Les causes courantes sont les suivantes :

• Types de données de paramètres de requête qui ne sont pas définis de manière cohérente. Cela s’applique en particulier à la longueur des chaînes mais peut s’appliquer aussi à n’importe quel type de données ayant un maxlength, une précision ou une échelle. Par exemple, si un paramètre nommé @Greeting est passé en tant que nvarchar(10) sur un appel, et en tant que nvarchar(20) lors du prochain appel, des plans distincts sont créés pour chaque taille de paramètre.

• Requêtes qui ne sont pas paramétrables. Si une requête comporte un ou plusieurs paramètres pour lesquels des valeurs codées en dur sont envoyées au moteur de base de données, un grand nombre de plans de requête peuvent exister pour chaque requête. Les plans peuvent exister pour chaque combinaison de longueurs et de types de données de paramètre de requête utilisés.

Si le nombre de plans d’usage unique prend une partie significative de la mémoire du Moteur de base de données SQL Server sur un serveur OLTP et que ces plans sont des plans ad hoc, utilisez cette option de serveur pour réduire l’utilisation de la mémoire avec ces objets.

Si l’option optimize for ad hoc workloads est activée, vous ne pouvez pas afficher les plans d’exécution des requêtes à usage unique, car seul le stub du plan est mis en cache. En fonction de votre environnement et de votre charge de travail, vous pouvez bénéficier des deux fonctionnalités suivantes :

• La fonctionnalité Magasin des requêtes, introduite dans SQL Server 2016 (13.x), vous aide à trouver rapidement les différences de performances causées par les modifications du plan de requête. Magasin des requêtes est activé par défaut sur les nouvelles bases de données dans SQL Server 2022 (16.x) et versions ultérieures.

• La paramétrisation forcée peut améliorer les performances de certaines bases de données en réduisant la fréquence des compilations et recompilations des requêtes. Les bases de données qui bénéficient de la paramétrisation forcée sont généralement soumises à des volumes élevés de requêtes simultanées émanant de sources telles que les applications de point de vente.

  La paramétrisation forcée peut entraîner des problèmes de performances en raison de la sensibilité des paramètres. Pour plus d’informations, consultez Examiner et résoudre les problèmes liés à la sensibilité aux paramètres. Pour SQL Server 2022 (16.x) et les versions ultérieures, vous pouvez également activer l’optimisation du plan de sensibilité aux paramètres.

Exemples

Pour trouver le nombre de plans d’usage unique mis en cache, exécutez la requête suivante :

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

Contenu connexe

• sys.dm_exec_cached_plans (Transact-SQL)
• Options de configuration du serveur (SQL Server)
• Superviser le niveau de performance avec le Magasin des requêtes
• Guide d’architecture de traitement des requêtes
• Optimisation du plan de sensibilité aux paramètres