SQL Server 2008 : Meilleure réutilisation des plans d’exécution avec 'optimize for ad hoc workloads' ?
C'est une nouvelle option de niveau serveur, qui a vu le jour dans le but d'éviter de noyer un serveur sous le poids de son cache de plan d'exécution.
En pratique tout code SQL avant son exécution doit être compilé. Le résultat de cette compilation s'appelle le plan d'exécution qui régira le comportement de la requête à l'exécution. Cette opération peut être longue et est couteuse en termes de temps processeur et doit généralement être évité quand cela est possible.
C'est là qu'intervient le cache des plans (pour être plus complet j'avais fait un topo là-dessus : http://blogs.codes-sources.com/christian/archive/2007/04/27/sql-server-requ-tes-sql-texte-adhoc-contre-proc-dures-stock-es.aspx ), qui permet lors de la deuxième exécution de repêcher le plan dans la cache, là où il a été stocké à la première exécution du code SQL.
Le problème est que si un très grand nombre de requête n'est exécuté qu'une seule fois, le cache de plan est rempli de plans inutiles. Résultat il consomme beaucoup de mémoire (surtout en 64 bits) et la recherche dans celui-ci devient moins efficace.
Pour éviter cela, SQL Server 2008 a introduit l'option serveur 'optimize for ad hoc workloads'.
Elle s'active avec le code suivant :
EXEC
sys.sp_configure
N'show advanced options', N'1'
GO
RECONFIGURE
GO
EXEC
sys.sp_configure
N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE
GO
EXEC
sys.sp_configure
N'show advanced options', N'0'
GO
RECONFIGURE
GO
Il est aussi possible de passer dans les propriétés serveur de l'instance par l'interface graphique de Management Studio :o)
Ce que permet cette option, est que lors de la première exécution, on compile le requête on obtient le plan. Jusque-là pas de changement, mais ce plan n'est pas mis en cache, on conserve juste la trace de son emprunte(Hash). La mise en cache se fera uniquement à la 2ème exécution si la comparaison de l'emprunte se fait.
A noter que cette emprunte (Hash) est nouvelle sous SQL Server 2008, on la retrouve :
select query_hash, query_plan_hash from
sys.dm_exec_query_stats
Ce qui fait que dans ce mode, le plan n'est pas stocké à la première, mais à la seconde exécution. Cela se révèle très efficace si les requêtes sont générées à la volée par l'application ou que vous avez beaucoup de code dynamique, non réutilisé. Par contre, peu d'intérêt en cas d'utilisation systématique de procédures stockées.
Bonne exécution…
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
