Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

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 :
Publié mardi 22 décembre 2009 09:30 par christian

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01