Option "Optimize for Ad hoc Workloads"

Bonjour,

Chez un client, j'ai actuellement quelque 27,000 plans (4GB des 32 disponibles sur la machine, ce qui me semble énorme) contenus dans le cache de procédures, et une grande partie d'entre eux ont un use_count égal à 1.

J'ai donc pensé à utiliser l'option de server Optimize for Ad hoc Workloads, puisque 95% des requêtes exécutées par l'applicatif ne le sont pas par procédure stockée (ce n'est pas mon choix :)).
Néanmoins et c'est normal, nombre de requêtes en cache sont paramétrées.

Outre les avantages "vantés" par cette option que l'on peut trouver sur Internet, je me demande s'il n'y a pas non plus des inconvénients à utiliser une telle option.

Quelqu'un en connait-il ?

@++ ;)

1) les requêtes ont-elle toujours la même casse, forme ?
2) as tu paramétrisé de manière automatique (ALTER DATABASE .... SET PARAMETERIZATION FORCED)
3) les tables sont-elles toujours préfixées par leurs schéma SQL ?

A +

- Oui, il est préférable que la syntaxe des requêtes ad hoc, censées effectuer le même traitement, soient toujours écrites exactement de la même manière. En effet, il suffit qu’il y ait un espace ou une différence de casse dans la requête pour que la fonction de hachage de la requête had hoc soit différente et au quel cas, SQL server génère une nouvel entrée dans le cache !
Par ailleurs, il est stipulé que la correspondance avec un plan ad hoc est effectuée uniquement lorsque les tables sont préfixées par le nom du schéma.

- Pour revenir à l’option Optimize for Ad hoc Workloads,, celle-ci présente effectivement beaucoup d’avantages largement relatés. Le seul inconvénient que je lui trouve est le suivant :
L’option « Optimize for Ad hoc Workloads » met en œuvre une approche proactive afin d’optimiser l’utilisation de la mémoire, mais pour cela, lors de la première exécution et compilation de la requête, Sql server ne garde pas le plan complet. Ceci a pour effet que pour les requêtes qui sont exécutées plus d’une fois (use_count > 1), celles-ci sont compilées 2 fois au lieu d’une seule fois (puisque lors de la première compilation, le plan n’est pas entièrement sauvegardée dans le cache du plan)
A+,

Bonjour et merci de vos réponses ;)

Les requêtes sont écrites systématiquement et strictement avec la même casse.
La base de données n'est pas positionnée à SET PARAMETERIZATION FORCED, et malheureusement les tables ne sont pas qualifiées par le schéma auquel elles appartiennent (j'en ai discuté avec le DBA principal qui dit que ce n'est pas nécessaire, puisque le schéma par défaut est dbo, et que toutes les tables sont dans ce schéma (deux aberrations dans la même phrase, mais je ne peux rien y faire ... :?).

Ceci m'amène à penser que je ne peux pas utiliser cette option.
Je vais regarder la documentation de SET PARAMETERIZATION FORCED ;)

Merci encore pour vos réponses ;)

Citation:

---

Envoyé par hmira

- Oui, il est préférable que la syntaxe des requêtes ad hoc, censées effectuer le même traitement, soient toujours écrites exactement de la même manière. En effet, il suffit qu’il y ait un espace ou une différence de casse dans la requête pour que la fonction de hachage de la requête had hoc soit différente et au quel cas, SQL server génère une nouvel entrée dans le cache !

---

non et oui : au niveau des espaces, aucune importance, le texte de la requêtes est "compressées des espaces, retour chariot et tabulation. Les mots clefs de SQL n'ont pas d'importance vis à vis de la casse. En revanche, les noms des objets, comme l'odre de ces derniers dans les diffférentes clauses a une importance énorme !

A +

http://sqlblog.com/blogs/linchi_shea...rver-2000.aspx

C'est sur les proc, mais c'est la même chose pour les requêtes adhoc.

A +

Merci Frédéric (SQLPro) pour cette précision concernant la gestion des Espaces, Retour Chariot, Tabulations, Mots clés, etc. Je ne croyais pas (ou je ne pensais pas) (1) que cela était géré ainsi sous SQL Server. En tout cas, c’est une bonne nouvelle !
A+

(1) PS : C’est une certitude qui me vient peut être de ma pratique d’ORACLE dans une autre vie !

Il faut savoir que le moteur relationnel de MS SQL Server est plus pointu dans bien des domaines que celui d'Oracle. En revanche l'aspect transactionnel est plus sûr et plus rapide dans Oracle que dans SQL Server.

A +

Regarde plutôt du côté de l'option de bases de données SET PARAMETERIZATION FORCED comme le suggère SQLPRo si les mêmes requêtes sont lancées un certain nombre de fois dans la journée.

Je ne sais pas si tu as d'autres bases de données sur ton serveur mais l'option Optimize for Ad hoc Workloads a une portée de niveau serveur, ce qui peut être gênant pour d'autres bases (et donc d'autres applications). De plus, tu perdras les avantages que propose l'option dès lors que tes mêmes requêtes sont exécutées une seconde fois. (Ce qui est ton cas apparamment).

Attention cependant à l'option PARAMETERIZATION FORCED qui peut te générer quelques problèmes de performance (la face cachée de l'option :) ).
Vu que toutes tes constantes seront traitées comme paramètre, le plan d'exécution qui sera mis en cache peut être inefficace pour certaines valeurs de tes paramètres de requêtes.

++

Citation:

---

Attention cependant à l'option PARAMETERIZATION FORCED qui peut te générer quelques problèmes de performance (la face cachée de l'option ).
Vu que toutes tes constantes seront traitées comme paramètre, le plan d'exécution qui sera mis en cache peut être inefficace pour certaines valeurs de tes paramètres de requêtes.

---

Ta remarque est pertinente. Pour les requêtes qui subiraient le revers de la médaille de l’option PARAMETERIZATION FORCED (c.à.d plan inefficace vu les valeurs des paramètres), on se doit, pour résoudre ce problème, de leur appliquer l’option recompile :

Code:

---

... OPTION (RECOMPILE)

---

A+