Discussion :

[StringBuilder]

Bonjour,

Depuis plusieurs mois, j'ai des performances sur des serveurs SQL Server à mon boulot qui ne me conviennent pas.
En effet, sur une base identique, j'arrive à avoir des performances jusqu'à 10 fois meilleures sur mon PC perso qui n'est ni haut de gamme ni neuf. C'est juste un "bon" PC, avec cependant quelques pièces de récupération qui sont clairement de moindre qualité.

Suite au comparatif de SQLPro entre SQL Server sous Windows et sous Linux, j'ai décidé de rejouer le benchmark sur différents environements :
- Mon PC perso
- Mon portable Pro (clairement désuet, la seule chose qui le sauve, c'est le remplacement de son disque d'origine par un SSD)
- Un des serveurs du boulot incriminés

Voici donc le comparatif initial, qui contient entre autres, le script du bench :
https://sqlpro.developpez.com/tutori...windows-linux/

J'ai retenu ce bench en particulier, car il teste avant tout les IO.
Et de manière très étrange, en pleine charge, mon serveur a un encéphalogramme plat : aucune charge mémoire, CPU et disques en train de se tourner les pouces.
Vu qu'on est en environnement virtualisé, et que j'ai de gros doutes quant à la bonne gestion de nos baies de disque, je soupçonne clairement les IO d'être à l'origine du problème.

Une chose qui me fait douter entre autres, c'est que lorsque je fais un backup de la base de donnés, ce dernier se fait à une vitesse de 300 à 400 Mo/s.
Donc des baies de disque très véloces, qui fonctionnent bien.

Mais si je fais un "select into" d'une table volumineuse dans une autre, je tombe à une consommation des disques entre 20 et 30 Mo/s, data, log et tempdb compris !

Voici les résultats du bench avec mes 4 environnements :
1 Notre serveur après migration de tous les disques sur une baie de disques SSD
2 Notre serveur alors qu'une partie des disques étaient encore sur une baie de disques magnétiques (je n'ai malheureusement pas le détail desquels)
3 Mon PC perso
4 Mon portable Pro

Voici le détail du résultat de la requête de fin du bench détaillant les IO.
Je m'attendais naïvement a avoir à peu de chose les mêmes nombres dans les "number of read/write" et surtout "number of bytes read/write", et n'avoir des différence que dans les temps d'attente...
Et pourtant, j'ai des chiffres drastiquement différents d'un environement à l'autre (avec parfois un facteur de plus de 1/100 !)



J'ai vraiment besoin d'aide pour comprendre... Car quand je vois le nombre d'octets "lus pour rien" sur notre serveur, je me dis que les performances pourraient être drastiquement supérieures, non ?

[Oishiiii]

Plutôt que se focaliser sur les I/O, j'aurais une approche plus globale pour ne pas louper autre chose.

Vous avez une différence de performance d'un facteur 10. Comment vous le mesurez, c'est certaines requêtes en particulier ?

Je regarderais d'abord les temps d'attentes au niveau de l'instance. Est-ce que vous attendez vraiment sur de l'I/O ou autre chose.

Dans vos test, la configuration mémoire et parallélisme CPU étaient identiques ?
Est-ce que sur votre antivirus vous avez exclus les fichiers SQL Server ?

[StringBuilder]

Le détail des tests est détaillé dans le lien.

Je me concentre sur les IO, car c'est clairement le point que j'ai identifié comme source probable de la lenteur : puisque ni le CPU ni la mémoire ne sont sollicités, et que la vitesse brute d'accès aux fichiers est 10 fois moindre que celle constatée en dehors de SQL Server.

Il n'y a pas d'AV sur le serveur : uniquement Windows Defender activé par défaut.
En revanche, nous sommes sur un environement virtualisé, je ne sais pas ce qu'il y a au niveau de l'hôte (à mon avis pas grand chose, l'hôte est sous Linux).

Ensuite, j'ai clairement mis en évidence des différences de nombre le lectures/écritures, autant en termes de nombre d'accès qu'en terme de volumétrie, alors que le test est strictement le même (alimenter une table de 8 Go en remplissant 8 pages d'un coup à chaque transaction).

Et ce sont ces différences que je souhaite comprendre.
Peut-être une fausse piste, mais avant de délaisser cette piste, je veux avoir une explication.

[hmira]

Bonjour,
Je m'attendais naïvement a avoir à peu de chose les mêmes nombres dans les "number of read/write" et surtout "number of bytes read/write", et n'avoir des différence que dans les temps d'attente...
Et pourtant, j'ai des chiffres drastiquement différents d'un environement à l'autre (avec parfois un facteur de plus de 1/100 !)
J'ai vraiment besoin d'aide pour comprendre...

Bonjour StringBuilder,

Tu fais manifestement une mauvaise interprétation des données fournies par les vues de gestion dynamiques utilisées pour dans le bench et le comparatif de SQLPro (lien ci-après https://sqlpro.developpez.com/tutoriels/comparatif-performances-sql-server-windows-linux/

Les données fournies par ces vues systèmes donnent des indications sur les I/O physiques et uniquement sur les I/O physiques. Elles ne concernent pas les I/O logiques.
Ta remarque et interrogation auraient pu avoir du sens s'il s'agissait des I/O logiques. Or dans le cas présent ce n'est pas du tout le cas.
Si l'on peut considérer que les I/O logiques sont immuables (en encore que), les I/O physiques quant à elle peuvent largement varier, et ce, en fonction du contexte du système et de nombre d'autres paramètres.

A+

[StringBuilder]

Oui, j'ai parfaitement compris que ces IO physiques étaient très différentes en fonction de l'environement physique.

Mais justement, je souhaite comprendre :
1/ L'impact de valeurs élevées vs faibles
2/ Quels facteurs peuvent expliquer des valeurs différentes

Si il faut 1000 fois plus d'accès physiques aux disques pour obtenir le même résultat, pas besoin de faire polytechnique pour comprendre que ça va être 1000 fois plus lent (ou pas loin).
Et je souhaite comprendre d'où ça vient, pour voir si on peut améliorer ce comportement qui me semble très pénalisant.

[hmira]

Les problèmes de performance sur un Serveurs de production peuvent avoir pour origine, en plus d'éventuels problème I/O, plusieurs facteurs divers et variés, notamment l'activité transactionnelle généralement très forte en environnement OLTP.

Comparer les performance d'un Serveur en environnement de production et ton PC perso, personnellement, cela me laisse perplexe, sauf si, sur l'environnement de production, tu mets fin à l'ensemble des sessions utilisateurs et tu redémarres l'instance en mode mono-utilisateur ! Chose que tu pourras peut-être faire entre 03:00 et 04:00 du matin !, et encore tu auras toujours quelques utilisateurs insomniaques qui viendront se plaindre !

Il te faudra donc surveiller, comparer et corréler, d'autres indicateurs aussi importants que sont (liste non exhaustive) :

"Buffer Cache Hit Ratio"
"Page Life Expectancy"
"Batch Request Per Second"
"Compilation Per Second"
"Recompilation Per Second"
"User Connections"
"Lock Wait Per Second"
"Page Split Per Second"
"Blocked Processes"
"CheckPoint per Second"
Etc.


A+

[StringBuilder]

Comparer les performance d'un Serveur en environnement de production et ton PC perso, personnellement, cela me laisse perplexe

Ca tombe bien, puisque c'est le serveur de DEV et que mise à part un collègue qui a fait une action quelques instant pendant mon test (pas de chance) il n'y avait aucune activité.
Qui plus est, on travaille dans une base de données dédiée au test, qui est, elle, totalement inutilisée durant le test.

Si j'en crois la requête, les IO en question portent sur la base du test, pas sur l'instance complète.

En revanche ce comportement d'IO qui m'interroge est probablement le même sur la PROD dans la mesure où :
- Ils sont virtualisés sur le même hôte
- C'est moi qui ai procédé à l'installation des deux machines
- Et du coup j'ai mis les mêmes paramètres

[hmira]

Si j'en crois la requête, les IO en question portent sur la base du test, pas sur l'instance complète.

Oui, tout à fait les IO portent uniquement sur les fichiers de données et les fichiers des journaux de transaction de la base considérée, c.à.d. la base de test. Donc Oui, les I/O ne concernent pas l'ensemble de l'instance.

Indépendamment des comparatifs entre environnements que l'on peut juger pertinentes ou pas, les mesures que tu nous as fournies relatives au cas 1 (Serveur Full SSD > DATA > avg_write_stall_ms) pour une valeur de 69.8 ms, me paraissent préoccupantes.
En effet, c'est incontestablement une mauvaise valeur (69.8 ms) pour une latence en écriture.
Il y a apparemment des problèmes de latence notamment pour les opérations d'écriture, et donc, tu as raison de soulever la question.
Plus généralement, les latences au-dessus de 30 ms dénotent l'existence d'un problème.

A+

[SQLpro]

Dans les environnement ou les disques sont virtualisés il y a souvent des SAN "intelligent" qui entrainent les performances de certaines bases vers le bas.
En effet le SAN "intelligent" propose la plupart du temps de placer les fichiers (et même souvent les "blocs") peu accédés dans des espaces de stockage peu rapide et ceux qui le sont le plus dans des espaces de stockage très rapide.
Si votre base bosse fort (comme dirait le turc... ! ) par exemple de 9h à 12h et de 14h à 17h et pas du tout les autres moment de la journée et pas non plus les dimanche, samedi et jours fériés, alors il est possible que votre SAN bien plus sollicité par les téléchargement illégale des séries TV par les utilisateurs lambda, ait repoussé vos fichiers et leurs IO dans le tréfonds du bout du panier du SAN...

Regardez en lançant continuellement le tests, si les temps s'améliore au cours de la journée...

Si c'est le cas, optez pour un SAN débile !

A +

[StringBuilder]

Merci pour ces retours Hmira et SQLPro.

Je vais me focuser sur ces latences > 30 ms
Depuis le départ c'est un point effectivement que je soupçonne : les serveurs, en utilisation bureautique, sont extrêmement rapides (au détail des CPU qui sont un peu faibles).

Mais en bureautique, même quand on manipule de gros fichiers, on fait surtout des accès séquentiels et rarement parallèles : l'inverse de ce que fait une base de données (qui fait énormément de random dans de multiples fichiers en même temps).

Et depuis le début, même sur une petite base avec des opérations simples, je trouve les performances des bases de données très décevantes.

Je vais donc remonté ce souci à nos administrateurs, avec cette fois pas seulement un ressenti, mais une base de comparaison.


Après, pour le SAN intelligent ou débile, je pense qu'on a plutôt du très haut de gamme (on est sur une grappe de serveurs destinée à héberger plusieurs centaines de serveurs virtuels).

Cependant, même si je ne peux clairement pas m'avancer sur le comportement de tout le monde, ce SAN est dédié aux serveurs de nos clients hébergés : donc il est peu probable que les disques soient sollicités par des films de vacance trouvés sur Bittorent, d'autant qu'on fait payer l'espace disque aux clients et que par conséquent ils évitent de trop s'étaler quand c'est pas nécessaire).

En revanche, effectivement il y a des bases très sollicitées, plus que d'autres, et mon test qui ne dure que quelques minutes, sur un serveur de DEV qui est donc peu sollicité, ne suffit peut-être pas à décider le SAN à utiliser une zone rapide. A creuser. Je lancerai le test à plusieurs reprises dans la journée et le week-end pour voir s'il y a une évolution.