Dans SQL Server, les plans d’exécution générés par l’optimiseur de requêtes sont conservés en mémoire dans le cache de plans, de façon à pouvoir être réutilisés si on voit passer les mêmes requêtes.

Mais quand vous exécutez des requêtes ad hoc, c’est-à-dire des requêtes qui ne sont pas contenues dans les procédures stockées, vous risquez de stocker plusieurs fois les mêmes plans lorsque les paramètres de recherche changent.

Dans cette vidéo, nous expérimentons de phénomène en interrogeant le cache de plans avec la requête suivante.

SELECT st.text, cp.usecounts, cacheobjtype, size_in_bytes
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE ‘%SELECT Nom, Prenom FROM Contact.Contact%’
OPTION (RECOMPILE);

Pour éviter un remplissage inutile du cache de plans de SQL Server avec des plans d’exécution utilisés seulement une fois, nous pouvons aller dans les propriétés du serveur, au niveau avancé, et activer une option qui s’appelle « optimiser pour les charges de travail ad hoc ».
Cette option a été ajoutée en SQL Server 2008 et elle est à false par défaut. Vous avez vraiment intérêt à la mettre à true dans tous les cas, parce qu’il n’y a pas de contre-indication.

Optimiser pour les charges de travail ad hoc va changer le comportement du cache de plans de SQL Server à chaud. A la première exécution d’une requête, SQL Server conserve en cache un stub de plan, en quelque sorte une signature. C’est seulement à la deuxième exécution que le plan est conservé en cache.

Actuces et tutoriels SQL Server en français, pour SQL Server 2017, SQL Server 2016, et toutes versions.
Par Rudi Bruchez
https://www.linkedin.com/in/rudibruchez/
www.pachadata.com

Optimiser pour les charges de travail ad hoc dans SQL Server