Blog

Optimize for ad hoc workloads : Requêtes Ad Hoc et valeurs littérales


Dans un article dédié au tuning d’instance, l’activation de l’option “optimize for ad hoc workloads” a été évoquée. Nous allons mesurer l’impact de cette option sur le moteur.

Démo 1 : “optimize for ad hoc workloads” désactivé

1) En premier lieu, on ouvre une première session pour vider les caches et on désactive l’option “optimize for ad hoc workloads “:

2) On ouvre une seconde session où l’on génère un lot de requêtes auxquelles on passe des valeurs littérales (VS paramètre) :

Copier coller et exécuter le résultat, soit le lot de requêtes obtenu sur la base de données AdventureWork, toujours depuis la seconde session.

Expert SQL Server - Optimize for ad hoc workloads : Requêtes Ad Hoc et valeurs littérales - SQL Server  - result_synselect

3) Une fois l’exécution du lot terminée, retour à la première session pour consulter le cache des plans d’exécution :

Observation : Il y’a un plan d’exécution par requête bien qu’il n’y est qu’une valeur littérale qui varie et la taille par plan est plutôt conséquente :

Expert SQL Server - Optimize for ad hoc workloads : Requêtes Ad Hoc et valeurs littérales - SQL Server  - adhoc2_false

Démo 2 : “optimize for ad hoc workloads” activé

4) On revient à la première session pour vider à nouveau les caches MAIS cette fois, on active l’option “optimize for ad hoc workloads “:

5) On exécute à nouveau le lot de requêtes de l’étape 2, toujours depuis la seconde session

6) Une fois l’exécution du lot terminée, on revient à la première session pour consulter le cache des plans d’exécution :

Observation : Cette fois, il y’a un sous-plan pour l’ensemble des requêtes et la taille de plan a été revue à la baisse, d’où l’importance de cette option.

Expert SQL Server - Optimize for ad hoc workloads : Requêtes Ad Hoc et valeurs littérales - SQL Server  - adhoc2_enable-2

Démo 3 : requête préparée

7) Remplaçons la seconde session pour générer un lot de requêtes préparées ayant le même pattern auxquelles on passe un paramètre (VS valeur littérale) :

8) Exécutons le lot de requêtes préparées, toujours depuis la seconde session

9) Une fois l’exécution du lot terminée, on revient à la première session pour consulter le cache des plans d’exécution en modifiant la clause légèrement WHERE :

Observation : Pour cette 3ème démo, pas de sous-plan mais un plan unique lié à la requête préparée.

Expert SQL Server - Optimize for ad hoc workloads : Requêtes Ad Hoc et valeurs littérales - SQL Server  - requete_préparee

Conclusion

Pourquoi passer des valeurs littérales à une requête au fond ? La principale raison est une méconnaissance de l’impact sur le moteur SQL Server et des alternatives telles que l’utilisation de requêtes préparées. Voici comment procéder en C# :

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare(v=vs.110).aspx

En revanche, si c’est une solution éditeur qui génère majoritairement ce type d’appel, donc que vous n’avez pas la possibilité de changer ce comportement côté applicatif, vous devrez vous poser la question d’activer ou non le paramétrage en mode forcé sur la base de données concernée, autrement dit la conversion automatique par le moteur SQL Server des valeurs littérales par des paramètres, pour réduire drastiquement le nombre de compilations et donc la sollicitation du CPU mais aussi de la mémoire pour le stockage des plans. Le paramétrage est déjà activé en mode simple par défaut mais ne concerne que les plans triviaux. Limitations et recommandations pour le paramétrage en  mode forcé :

https://technet.microsoft.com/fr-fr/library/ms175037(v=sql.105).aspx

Auteur

Expert SQL Server - Optimize for ad hoc workloads : Requêtes Ad Hoc et valeurs littérales - SQL Server  - avatar_ninja_tete-150x150
Sarah Béquet
Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
error: