Discussion :

[epsilon68]

Bonjour,

J'ai une appli en prod, 250 utilisateurs concurrents.
le server a 8 processeurs, 4 GB RAM et un sqlserver 2005 standard edition (qui utilise donc 4 processors )

quand personne n'utilise le server, un report met 4 secondes,
quand le monde est dessus, le meme report met 1min 30 secondes.

Comment prouver que le server est à la ramasse?

et que faut-il préconiser?
1. passage au 64bits et 16GB de RAM
2. une version entreprise?
3. ?

les reports utilisent beaucoup de tables temporaires et manipule des larges tables de données. (c'est des reports sur une database pour un systeme de tickets)

Merci d'avance pour votre aide.

[elsuket]

Bonjour,

Un bon indicateur est le compteur de performance Page Life Expectancy, que vous pouvez visualiser à l'aide du moniteur de performance de Windows, ou bien à l'aide de la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT	cntr_value AS PLE
FROM	sys.dm_os_performance_counters
WHERE	object_name = 'SQLServer:Buffer Manager'
AND	counter_name = 'Page life expectancy'

Il indique le temps moyen, en secondes, que les pages de données restent dans le cache de données. Plus c'est long, plus c'est bon
Si sa valeur est en dessous de 300, c'est que vous avez de la pression mémoire, et à mon avis ce doit être le cas, spécifiquement quand vous exécutez le rapport.

Vous pouvez donc penser à étudier les requêtes (SET STATISTICS IO, plan de requête, ...) de votre rapport et les modifier / ajouter des indexes / ...

Comme vous dites que vous utilisez des tables temporaires, il se peut également que vous deviez simplement réécrire vos requêtes.
Dès la version 2005 de SQL Server, vous pouvez vous en remettre aux expressions de table commune (CTE dans la littérature) pour éviter leur usage.

Vous dites également utiliser des tables volumineuses : c'est-à-dire ?
Quel est leur taille, leur nombre de ligne, leur définition ?
Quels sont les index sur cette table ? Sont-ils correctement utilisés par les requêtes de votre rapport ?

Si après avoir répondu à toutes ces questions et correctement adressé les performances de vos requêtes la base de données est toujours "lente", vous pourrez considérer augmenter la quantité de RAM.
En effet, SQL Server travaille exclusivement en RAM, puisque les temps d'accès sont au moins 1000 fois plus rapide que sur disque

Le fait de passer en 64 bits ne vous apportera rien sauf si vous comptez mettre plus de 64Go de RAM sur la machine hébergeant l'instance SQL Server.
En effet vous pouvez utiliser les options d'AWE (flag /PAE dans le fichier boot.ini, voyez l'article de Mikedavem à ce sujet) pour adresser au plus 64Go de RAM.

L'édition Enterprise ne comporte aucune différence avec l'édition Standard à ce niveau là.
Elle est simplement plus riche en fonctionnalités que l'édition Standard.

@++

[SQLpro]

Quelle est la taille de la base ?

Faite sp_spaceused sur la base.

Comment avez vous mesuré les 250 utilisateurs concurrents ?


Pour voir le nombre d'utilisateurs connectés, faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT session_id, connect_time, text, COUNT(*) OVER() AS NOMBRE
FROM   sys.dm_exec_connections
       CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)

Nombre vous donnera le nombre total de connexions utilisateur.

Pour voir le nombre de requêtes simultanées en cours d'exécution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT * 
FROM   sys.dm_exec_requests  AS r
       INNER JOIN sys.dm_exec_connections AS c
             ON r.session_id = c.session_id
WHERE  status = 'running'

Est une appli Web ou client lourd ?

A +

[Philippe Robert]

Il indique le temps moyen, en secondes, que les pages de données restent dans le cache de données. Plus c'est long, plus c'est bon
Si sa valeur est en dessous de 300, c'est que vous avez de la pression mémoire, et à mon avis ce doit être le cas, spécifiquement quand vous exécutez le rapport.

Existe-t'il une bonne litérature à ce sujet. En effet, je quitte malgré moi le domaine de la programmation SQL pour faire de l'administration SQL Server pure, domaine qsue je maîtrise mal.

[elsuket]

Malheureusement pas grand chose sur le sujet.
On trouve au mieux un paragraphe consacré à ce compteur, et un autre au Buffer Cache Hit Ratio (qui doit être le plus proche de 100% possible), dans les livres qui traitent de la performance, comme Pro SQL Server 2008 Query Performance Tuning Distilled, écrit par Grant Fritchey et Sajal Dam, ...

@++

[Philippe Robert]

çà promet

[epsilon68]

Merci beaucoup pour votre aide,

j'ai executé la commande pour "page life expectancy",
J'ai eu 172, puis j'ai lancé un report, et la j'ai eu 12.

c'est une catastrophe, j'ai remonté l'info, je vous tiendrais au courant.

[epsilon68]

j'ai executé sp_spaceused
resultats en image

[epsilon68]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT session_id, connect_time, text, COUNT(*) OVER() AS NOMBRE
FROM   sys.dm_exec_connections
       CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)

cela ne marche pas, je suis sur SQL2005

Est une appli Web ou client lourd ?

C'est une appli web où les reports sont tres lourd.
1 stored procedure par report (en moyenne) avec utilisation de tables temporaires

[elsuket]

J'ai eu 172, puis j'ai lancé un report, et la j'ai eu 12.

172 c'est déjà pas bon, mais 12, je suis d'accord avec vous, c'est catastrophique.
Cependant si elle remonte assez vite après l'exécution du rapport, cela signifie que les données ont été lues à partir du disque, et que le cache a été vidé pour recevoir ces pages.
Il vous faut donc rechercher pourquoi la requête lit autant de données; peut-être pouvez-vous restreindre les données lues par le rapport en filtrant sur une plage de dates par exemple.
Nous pouvons vous y aider si nous avions la structure des tables et des indexes, et le code des procédures stockées

@++

[epsilon68]

non je ne peux pas donner la structure ni mes stored procedures.

je filtre deja par date mais ta reponse m'a fait penser à quelque chose:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select xx, yy, zz
from app
join ticket t on t.app = app.name and t.date < ... and t.date > ...

app est une petite table, ticket est large

vaudrait-il mieux faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select xx, yy, zz
from ticket
join app on ticket.app = app.name
where t.date < ... and t.date > ...

je dois dire j'ai des doutes que ces 2 requetes soient differentes...
mais ne sait-on jamais :-)

[mikedavem]

Bonjour,

Sur une clause INNER JOIN il n'y aura pas de différence en principe.
Il suffit de regarder les 2 plans d'exécution pour s'en rendre compte.

++

[epsilon68]

bon a priori on migre sur du 64 bits, 32 GB de RAM et SQL Server enterprise (pour gerer plus que 4GB)

super non?

[mikedavem]

D'après ce qui a été dit sur ce poste, vous devriez avoir en effet une amélioration de vos temps de réponse.

Cependant faites attention ... la mise à jour au niveau hardware ne fait en principe que cacher les erreurs de conception du modèle ou des requêtes (je ne dis pas forçement que c'est le cas pour vous mais en général il est quand même bon de voir si le code des requêtes les plus consommatrices peut être optimisé).

L'optimisation du modèle et du code = 70%
L'optimisation hardware = 30%

++

[epsilon68]

je suis d'accord mais la partie reports se base sur un produit acheté donc je ne peux rien modifier. Depuis Vendredi j'ai identifé 3 stored procedures tres lentes que j'ai pu optimiser aujourd'hui. Quand peu de monde est connecté, ce report s'execute en 3 secondes, quand il y a du monde, alors il peut mettre jusqu'a 20 secondes. (Jeudi dernier, c'etait 2 minutes)

bref je pense qu'avec le nouveau hardware, les reports s'executeront en 3 secondes quelque soit le nombre d'utilisateur. Je vous tiendrais au courant.

[SQLpro]

De toute façon, votre config de base n'est pas adaptée....
En effet 8 processeurs, si c'est du physique, c'est taillé pour 4000 utilisateurs en principe. Quand à 4 Go de RAM, c'est effectivement assez faible, mais tout dépend :
1) de la taille de la base
2) de son indexation
3) de sa structure.

On ne le dira jamais assez un respect studieux des formes normales et donc une bonne modélisation économise drastiquement la RAM !

A +

[epsilon68]

on avait SQLServer standard edition qui ne gerait que 4 processeurs alors qu'on en avait 8.

[SQLpro]

SQL Server standard edition est "bridé" à 4 CPU Physique. Tout dépend donc de votre config. Comme vous n'êtes pas précis, difficile de vous en dire plus.

Par exemple si vous avez un bi pro quadri core tout va bien et SQL Server peut paralléliser sur les 8 processeurs logiques.

Attention cependant à ne pas trop paralléliser. Le parallélisme outrancier peut se révéler plus gourmand et donc plus couteux que le mono thread. En effet il faut gérer la parallélisation ce qui a un cout, et il faut que dans l'architecture du serveur, tous soit parallélisé à même hauteur. Par exemple multiplier le nombre d'espace de stockage, afin qu'il y ait une affinité entre CPU et fichiers situés sur des axes physiques différents.

A +