Discussion :

[usf70]

Bonjour,

J'ai un problème de performances sur une requête SQL. Le plan d'exécution m'indique que le bon index est pris et que je peux difficilement l'optimiser plus que cela.
La table fait 1 million d'enregistrement. Le serveur est un quadri-processeur avec 4Gb de RAM (même si seulement 3,5 Gb sont reconnus ... Windows server 2000).
Cette table contient des données par societe. Les utilisateurs la consultent via une application. En fait, selon que les utilisateurs lancent leur requêtes sur une societé, la première exécution est très longue (environ 5/6 minutes pour une dizaine d'enregistrements). Mais s'ils la relancent dans la foulée, c'est rapide (car les données sont dans le buffer cache). Mais si quelqu'un d'autre le lance sur une autre socièté, le buffer cache est vidé, et rebelotte pour la deuxième société. Et cela tout le long de la journée.
En traçant la requête, je me rend compte que c'est le sp_cursorfetch qui est très long. (en lançant la requête dans un analyseur de requête, elle met une vingtaine de secondes). C'est l'utilisation des curseurs qui plombe l'appli.

Une des solutions à la quelle j'ai pensé serait (si c'est possible) de forcer la table dans le buffer cache d'office (comme l'on pourrait le faire avec les tables bufferisées sous Oracle). Mais serait-ce raisonnable (edit : personnellement je ne pense pas ...) pour une table d'un million d'enregistrements ? Si c'est faisable, comment faire ?

[SQLpro]

Vous avez visiblement beaucoup de problèmes et franchement je pense (à vue de nez) que votre problème n'est pas franchement soluble...

Votre problème est apparament un défaut de cache. Cela fait que les lignes des tables nécessaire aux requêtes montent en mémoire mais sont déchargées parce que d'autres requêtes ont besoin de placer les lignes d'autres tables en mémoire.
Si vous envisager de forcer la table en mémoire, par exemple avec un DBCC PINTABLE, alors le remède risque d'être bien pire que le mal. En effet ce seront d'autres tables qui ne pourront être mises en mémoire... Donc des temps de requêtes bien plus longs et très erratiques !

Vous dite que c'est le curseur qui rame. En général programmer à l'aide des curseur est généralement la pire des solutions en matière de performances. SI vous voulez des perfs, commencez par éliminer la plupart des, curseurs (lorsque j'intervient en audit j'enlève environ 80% des curseurs des dévelopeurs en V 2000 et près de 100% en v 2005). Prévoyez aussi une bonne indexation.
Une bonne indexation réduit drastiquement les coûts des SELECT.

Ce que je crois c'est que vous devrier commencer par auditer le "cache hit ratio" du buffer manager (gestionnaire de tampon / Taux d'accès au cache...)
Si ce paramètre est en moyenne inférieur à 95%, cela est mauvais. Si, il est inférieur à 90 c'est exécrable. En dessous, c'est pourri ! Un bon cache hit ratio, commence à 98%...
Maitenant il vous reste différentes solutions :
1) rajouter de la RAM (solution de facilité) mais dans votre cas il est probable qu'il faille changer d'édition de Windows.
2) réduire la fenêtre des données exploitées (et là c'est déjà plus costaud...)
Bref, inspirez vous des papiers que j'ai écrit sur l'optimisation des données dans MS SQL Server. Les 3 premiers sont disponible sur mon site d'entreprise SQL spot à :
http://www.sqlspot.com/Voulez-vous-optimiser-votre.html
Les deux suivants sont parus récemment dans SQL Server magazine.

A +

[usf70]

Bonjour et merci de la réponse.
Le problème est que l'application utilisée est une application commerciale propriétaire. Donc nous ne pouvons pas toucher au code en soit. Donc il nous est impossible de modifier le comportement et l'utilisation des curseurs.
De plus, en regardant la requête utilisée, l'index à prendre y est forcé (clause INDEX nom_index dans la requête). Donc pas moyen non plus de créer un autre index. Mais là ce n'est pas trop grave car l'index utilisé reprend les colonnes de la clause WHERE et dans l'ordre. Donc on va dire que ça passe.
Pour ce qui est des curseurs, je suis d'accord ... mauvaise solution. Mais pareil, on ne peut rien changer.
Une question me turlupine quand même ... C'est l'utilisation en mémoire de SQL server. Il est parametré pour utiliser dynamiquement la mémoire sans limites. Or je vois qu'il se limite à utiliser 1,7Gb. Jamais plus. Et donc j'ai toujours à peu près 1,5Gb de libre sur le serveur. Pourquoi ne prend-t'il pas le reste ? S'il en a besoin pour son buffer cache ne peut-il pas prendre plus ? Est-ce lié à la valeur max server memory qui est limitée à 2Gb ? Comment utiliser plus ?

EDIT : je viens de trouver ce lien : http://support.microsoft.com/kb/274750
je vais regarder ça de plus près ... Si le buffer cache est plus gros, ça devrait résorber (un peu) le problème de temps de réponse que nous avons ...

[SQLpro]

La version 2000 n'utilise pas directement le 3 GB mais est seulement capable de 2 GB au max. Regardez aussi la version de Windows...

A +

[usf70]

Bonjour,

J'ai vérifié aujourd'hui le "cache hit ratio" du buffer manager (dans perfmon, j'ai pris l'option Gestionnaire de tampon => taux de présence dans le cache des tampons) et je tourne à environ entre 80% et 90%. Donc autant dire que c'est assez mauvais.
J'exclue le rajout de RAM car j'ai en permanence 1,5Gb de libre (comme dit plus haut). Les versions sont:

OS : Windows Server 2003 standart edition
SQL : SQL Server 2000 standart edition SP4

Est-ce que, avec ces versions, je peux activer la gestion AWE ? Que faire exactement à part modifier le 'awe enabled' dans SQL Server ? Dois-je aussi modifier les paramètres (/3GB) dans le boot.ini ?

[SQLpro]

80% et 90%. de cache hit ratio, c'est effectivement exécrable.

80% de cache hit ratio signifie que 80 % des données sont en cache et 20% sont lues sur le disque.
Sachant que la différence de vitesse entre une lecture mémoire et une lecture disque est de 1000 (au moins), cela signifie que si le temps moyen d'une requête "mémoire" est de 1 ms alors il y a 80 requêtes qui prennent 1ms et 20 requêtes qui prennent 1 seconde, soit au total une moyenne de 20,08 seconde soit 20.08/100 = 0,2 seconde en moyenne
à 90% le temps global est de 10,09 seconde soit en moyenne 0,1009 seconde
à 99% le temps moyen est de 0,01099
Autrement dit entre 80% et 99% le rapport est de 18,2

Cela sigifie que votre base de données tourne environ 20 fois moins rapidement que si elle était à 99%

OS : Windows Server 2003 standard edition => limité à 4 Go de RAM. Donc migration à prévoir vers une version advanced.

Effectivement le seul moyen rapide est d'augmenter la mémoire.... Faites attention de voir si le serveur (PC, machine physique) le permet. Ce n'est pas toujours le cas.

Ensuite le second moyen est de réduire la fenêtre des données. C'est souvent plus payant, mais beaucoup plus ciomplexe car cela signifie d'auditer globalement le serveur sur tous les plans y compris modèle de données, écriture des requêtes et proc stoc, indexation... etc. Le but étant de réduire drastiquement la fenêtre de données.

Pour cela lisez l'article 1/5 que j'ai écrit sur l'optimisation :
http://www.sqlspot.com/Voulez-vous-optimiser-votre.html

A +

[usf70]

Merci SQLpro pour tes conseils plus qu'avisés. Ce que nous avons fait:

1) migrer la version de SQL server de 2000 standard vers 2000 enterprise
2) rajouter /3GB dans le boot.ini de la machine

En rebootant le serveur, SQL server utilise maintenant 2,7Gb au lieu de 1,7 avant. Les problèmes de perf ont disparu. La contention venait bien de là. Mon "cache hit ratio" tourne maintenant pratiquement en permanence autour de 98/99% et il y a beaucoup moins d'accès disque du coup ....
Mon problème est donc résolu.

[Sacha999]

Pour info (et si je ne dis pas de betise), aucun system 32bits ne reconnait pleinement 4go, il reconnaitra au mieu 3,5go environ, il faut passer a un systeme d'exploitation 64 bits qui lui peut aller plus de 4go (tout depend de la carte mere apres)

[usf70]

Je confirme cette info ... Notre système ne "voit" que 3,5Gb

[SQLpro]

Pour info (et si je ne dis pas de betise), aucun system 32bits ne reconnait pleinement 4go, il reconnaitra au mieu 3,5go environ, il faut passer a un systeme d'exploitation 64 bits qui lui peut aller plus de 4go (tout depend de la carte mere apres)

et bien si... une bétise !

En fait un système 32 bits ne peut pas adresser plus de 4 Go car 4 Go c'est 2 puissance 32. Or dans un système 32 bits l'adresse la plus éloignée est l'adresse : 1111111111111111111111111111111 soit 4 go !

Cepandant rien ne l'empêche d'utiliser des pans de mémoire au delà des 4 Go grace à la translation d'adresse. L'OS ne verra toujours que 4 go, mais on lui fait glisser sa fenêtre de scrutation entre deux valeurs données.
C'est ainsi que windows 32 bits est capable de gérer jusqu'à 64 Go de RAM à l'aide d'AWE (Address Windowing Extensions). Et cela remplace la mémoire virtuelle et le fameux fichier pagefile.sys !
C'est ce que l'on appelle la mémoire étendue et c'est connu depuis des lustes... Pensez que dans les années 80 on avait des systèmes 16 bits avec 340 Ko de RAM. Or 2^32 = 65 536. Il y avait donc déjà de la mémoire étendue dans ces années là...
Fabuleux non ???

A +