Discussion :

[Mothership]

Bonjour,

Nous rencontrons comme chaque année quelques lenteurs liées à notre forte activité estivale et j'aimerai savoir si les performances de notre base sont bonnes.
Coté disque en regardant le perfmon, il semble que tout est ok.
Coté RAM 28 go sont allouées à l'instance
Coté Base elle même, elle n'est pas exempt de tout reproche mais directement liée à notre produit et notre éditeur
Coté Maintenance, nous utilisons la procédure (irréprochable) de SQLPro et ma foi tout se passe bien.

Je me demande aujourd'hui si nos performances pourraient être améliorées en augmentant la RAM sur cette machine physique. Y a t'il des compteurs ou des requêtes permettant de voir que des "waits" ou des latences sont provoquées faute de RAM disponible ?
J'ai regardé le compteur "buffer cache hit ratio" et nous ne descendons que rarement en deçà des 98%

Quelqu'un a une idée ?

[mikedavem]

Bonjour,

Quelle est la config de votre serveur ? (RAM, disques, CPU, OS, environnement virtualisé ou non etc ...) ?

Nous rencontrons comme chaque année quelques lenteurs liées à notre forte activité estivale et j'aimerai savoir si les performances de notre base sont bonnes.

Est ce que vous avez une baseline pour pouvoir faire des comparaisons de métrique ?

Coté disque en regardant le perfmon, il semble que tout est ok.

C'est à dire ? Que disent les compteurs [%disk time] et [disk avg queue length] sur une période significative ?

Y a t'il des compteurs ou des requêtes permettant de voir que des "waits" ou des latences sont provoquées faute de RAM disponible ?
J'ai regardé le compteur "buffer cache hit ratio" et nous ne descendons que rarement en deçà des 98%

Une bonne chose à regarder déjà ce sont les compteurs buffer cache hit ratio et page esperance life time. Apparemment votre cache est bien utilisé mais quelle valeur pour l'espérance de vie d'une page dans le cache ?

Avez vous regardé aussi si le nombre de connexion, batch /s n'augmente pas lors des périodes de lenteurs ? Avez vous corrélé cela avec les compteurs SQL Server liés aux nombre d'attente par seconde (Wait/s) et éventuellement les pageiolatch /s ?

Que donne la CPU également ? (%CPU Time, etc ...)

Enfin voila quelques pistes pour vous aider à commencer à aller plus loin dans votre diagnostic ....

++

[elsuket]

Bonjour,

Petite correction sur les bonnes indications de Mikedavem : le compteur s'appelle Page Life Expectancy

Que vous retourne la requête suivante ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR)
GO
 
WITH
	CTE_WAIT AS
	(	
		SELECT	wait_type, 
			wait_time_ms / 1000.0 AS wait_time_s,
			100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
			ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS n
		FROM	sys.dm_os_wait_stats
		WHERE	wait_type NOT IN
		(
			'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE'
			, 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH'
			, 'WAITFOR', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT'
		)
	)
SELECT		W.wait_type,
		CAST(W.wait_time_s AS decimal(12,2)) AS wait_time_s,
		CAST(W.pct AS decimal(4,2)) AS pct,
		CAST(SUM(W2.pct) AS decimal(12,2)) AS running_pct
FROM		CTE_WAIT AS W
INNER JOIN	CTE_WAIT AS W2 ON W2.n <= W.n
GROUP BY	W.n, 
		W.wait_type,
		W.wait_time_s,
		W.pct
HAVING		SUM(W2.pct) - W.pct < 95

@++

[SQLpro]

De même de mémoire il y a un compteur de RAM expecté par SQL (target memory size ou quelque chose comme ça).

A +

[Mothership]

Alors le serveur MSSQL :
CPU : 4 x Quacore Intel Xeon X7350 @ 2.93 GHz
RAM : 32 Gb
HD : SAN IBM DS5300 et SVC
OS : 2003 Ent R2 x64

Disques:
C:\ (RAID1 local)
L:\ bases systèmes MSSQL
M:\ Base de données
N:\ Journal des transactions
O:\ Disque pour backup logs

Pour les compteurs, l'historique a été "perdu" suite à une erreur humaine
Je vais en générer de nouveaux pour la semaine prochaine...

Pour créer mes compteurs je me suis basé sur quelques liens MSFT, dites moi si cela vous parait suffisant/cohérent (je n'ai que peu d'expérience dans ces analyses)
CPU : Processor: % Privileged Time + Processor: %User Time + System: Processor Queue Length
Disk : % disk time et Avg Disk Queue Length

[Mothership]

Merci pour vos réponses.

elsuket, la requête renvoie ceci :

wait_type wait_time_s pct running_pct
PAGEIOLATCH_SH 0.02 50.00 50.00
WRITELOG 0.02 50.00 100.00

[SQLpro]

AMHA les principaux problémes peuvent venir de la config SAN et de la gestion de vos espaces de stockeg. Lisez les article que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro/p8...akge/#more8589
http://blog.developpez.com/sqlpro/p5...fichiers-et-t/

A +

[zinzineti]

Voici les infos et les métriques que j'avais sous les coudes (infos glané sur Internet) qui peuvent être utiles.

=========================
DISQUE DUR
=========================
Disque logique\% Espace libre Ce compteur mesure le pourcentage d'espace libre sur le lecteur de disque logique sélectionné. Notez que s'il descend en dessous de 15 %, vous risquez de manquer d'espace libre lorsque le système d'exploitation enregistre des fichiers critiques. Une solution logique consiste à ajouter de l'espace disque.

Disque physique\% temps d'inactivité Ce compteur mesure le pourcentage de temps durant lequel le disque était inactif pendant l'intervalle d'échantillonnage. Si ce compteur descend en dessous de 20 %, cela signifie que le système de disque est saturé. Vous pouvez envisager de remplacer le système de disque actuel par un système de disque plus rapide.

Disque physique\Moyenne disque s/lecture Ce compteur mesure, en secondes, le temps moyen nécessaire pour lire les données du disque. Si le chiffre est supérieur à 25 millisecondes (ms), cela signifie que le système de disque a des problèmes de latence lorsqu'il lit du disque. Pour les serveurs stratégiques hébergeant SQL Server, le seuil acceptable est beaucoup plus bas, environ 10 ms. La solution la plus logique dans ce cas est de remplacer le système de disque actuel par un système de disque plus rapide.

Disque physique\Longueur moyenne disque s/écriture Ce compteur mesure, en secondes, le temps moyen nécessaire pour écrire les données sur disque. Si le chiffre est supérieur à 25 ms, cela signifie que le système de disque a des problèmes de latence lorsqu'il écrit sur disque. Pour les serveurs stratégiques hébergeant SQL Server, le seuil acceptable est beaucoup plus bas, environ 10 ms. La solution la plus logique dans ce cas est de remplacer le système de disque par un système de disque plus rapide.

Disque physique\Longueur moyenne de file d'attente du disque Ce compteur indique le nombre d'opérations d'E/S qui attendent que le disque dur soit disponible. Si la valeur est supérieure au nombre de piles plus deux, cela signifie que le disque lui-même peut constituer le goulot d'étranglement.

Mémoire\Octets du cache Ce compteur indique la quantité de mémoire utilisée pour le cache du système de fichiers. Il peut y avoir un goulot d'étranglement de disque si cette valeur est supérieure à 200 Mo.

=========================
MEMOIRE
=========================
Mémoire\% octets valides utilisés Ce compteur mesure le rapport octets validés/limite de mémoire dédiée, en d'autres termes, la quantité de mémoire virtuelle utilisée. Il indique une mémoire insuffisante si le chiffre est supérieur à 80 %. La solution la plus logique dans ce cas est d'ajouter de la mémoire.

Mémoire\% mégaoctets disponibles Ce compteur mesure la quantité de mémoire physique, en mégaoctets, disponible pour l'exécution des processus. Si cette valeur est inférieure à 5 % de la mémoire RAM physique totale, cela signifie qu'il y a une insuffisance de mémoire, ce qui risque d'accroître l'activité de pagination. Pour corriger ce problème, il vous suffit d'ajouter de la mémoire.

Mémoire\Entrées libres en table des pages système Ce compteur indique le nombre d'entrées de la table de pagination qui ne sont pas actuellement utilisées par le système. Si ce nombre est inférieur à 5 000, il est fort possible que vous ayez une fuite de mémoire

Mémoire\Octets de réserve non paginée Ce compteur mesure la taille, en octets, de la réserve non paginée. Cette zone de mémoire système est destinée aux objets qui ne peuvent pas être écrits sur disque mais qui doivent rester dans la mémoire physique tant qu'ils sont alloués. Si la valeur est supérieure à 175 Mo (ou 100 Mo avec le commutateur /3GB), il est possible que vous ayez une fuite de mémoire. Un ID d’événement 2019 typique est enregistré dans le journal des événements système.

Mémoire\Octets de réserve paginée Ce compteur mesure la taille, en octets, de la réserve paginée. Cette zone de mémoire système est destinée aux objets qui peuvent être écrits sur disque lorsqu'ils ne sont pas utilisés. Si la valeur est supérieure à 250 Mo (ou 170 Mo avec le commutateur /3GB), il est possible que vous ayez une fuite de mémoire. Un ID d’événement 2020 typique est enregistré dans le journal des événements système

Mémoire\Pages par seconde Ce compteur mesure la vitesse à laquelle les pages sont lues du disque ou écrites sur disque pour corriger les défauts des pages matérielles. Si la valeur est supérieure à 1 000, suite à une pagination excessive, il peut y avoir une fuite de mémoire

=========================
CPU
=========================
Processeur\% temps processeur Ce compteur mesure le pourcentage de temps que le processeur passe à exécuter un thread actif. Si le pourcentage est supérieur à 85 %, cela signifie que le processeur est surchargé et que le serveur peut nécessiter un processeur plus rapide.

Processeur\% temps utilisateur Ce compteur mesure le pourcentage de temps que le processeur passe en mode utilisateur. Si cette valeur est élevée, cela signifie que le serveur est occupé par l'application. Une solution possible consiste à optimiser l'application qui utilise trop les ressources du processeur

Processeur\% temps d'interruption Ce compteur mesure le temps que le processeur passe à recevoir et à traiter des interruptions matérielles durant des intervalles d'échantillonnage spécifiques. Ce compteur indique la possibilité d'un problème de matériel si la valeur est supérieure à 15 %.

Système\Longueur de la file d'attente du processeur Ce compteur indique le nombre de threads existant dans la file d'attente du processeur. Le serveur ne reçoit pas suffisamment de puissance processeur si la valeur est plus de deux fois supérieure au nombre de processeurs pendant un certain temps.

Toute remarque est la bienvenue

A+

[dbaffaleuf]

De même de mémoire il y a un compteur de RAM expecté par SQL (target memory size ou quelque chose comme ça).

Buffer Counts -> Target , Buffer Counts -> Committed dans dbcc memorystatus, il doit y avoir des compteurs perfmon aussi...

Tant que Target est en avance sur Committed, SQL Server peut faire les VirtualAlloc(), lorsque la tendance est inversée, il décommite de la mémoire à la demande du kernel => pression mémoire externe.

David B.

[mikedavem]

Quelle config pour vos LUNS SAN pour :

M:\ Base de données
N:\ Journal des transactions

?

++

[Mothership]

Nous utilisons la technologie SVC qui virtualise le stockage...
Ainsi les LUNS sont taillés en RAID5 et regroupés en grappe, les vLUNs attribués aux disques sont répartis (striped) sur l'ensemble des LUNs physiquement défini dans la baie.
Nos disques sont taillés à l'identique pour tous nos disques SAN attachés, que ce soient pour du stockage de fichiers, une base de données (oracle ou MSSQL) ou nos VMFS vmware.

[SQLpro]

Nous utilisons la technologie SVC qui virtualise le stockage...
Ainsi les LUNS sont taillés en RAID5 et regroupés en grappe, les vLUNs attribués aux disques sont répartis (striped) sur l'ensemble des LUNs physiquement défini dans la baie.
Nos disques sont taillés à l'identique pour tous nos disques SAN attachés, que ce soient pour du stockage de fichiers, une base de données (oracle ou MSSQL) ou nos VMFS vmware.

C'est à 100% tout à fait ce qu'il faut faire pour pourrir au maximum les performances de SQL Server : combinaisons de RAID 5 + agrégats taillés dans la masse du SAN !!!
Pour la 2e fois, lisez l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p8...t-le-stocakge/

A +

[Mothership]

Pour la 2e fois, lisez l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p8...t-le-stocakge/

En fait je répondais aussi à mikedavem, d'où ma précision sur notre configuration du stockage...

[mikedavem]

Si je comprends bien cela veut dire qu'une LUN n'est pas entièrement dédié à SQL Server ?

Comment avez vous configuré les VDisks ? (Mode Striped, Sequential ou Image) pour les VDisk concernant SQL Server ?

++

[Mothership]

Tout à fait, les 10 LUNs de la baie sont proposées au SVC.
Via la console SVC nous attribuons les disques aux différents serveurs, répartis sur l'ensemble des LUNs

[dbaffaleuf]

Hello,

Juste pour revenir sur la partie mémoire,

Que donne une valeur moyenne du compteur perfmon 'Page Life Expectancy' (comme demandé par elsuket un peu plus haut) ? S'il est inférieur à 300 secondes, alors potentiellement une page ne reste pas suffisamment longtemps dans le buffer pool.

Merci,

David B.

[Mothership]

Bonjour,

je ferai quelques moyennes plus parlantes d'ici demain midi, le redémarrage du weekend dernier a provoqué l'arrêt de mes traces.

Pour le moment sur 1H30 d'exécution (ce qui est faible) et sur une période faible à moyenne d'activité
MOYENNE(Process(_Total)\Working Set) : 971614092.7
MOYENNE(BufferManager\buffer cache hit ratio):99.745
MOYENNE(BufferManager\Page Lige expectancy):2878.57
MOYENNE(BufferManager\Total pages) : 3651584
Total server Memoy : 29 212 672 (sur 32 000 000)

[dbaffaleuf]

2800 secondes donc pas de problème de ce côté là. Il faut commencer à s'inquiéter quand ça tombe sous les 300 secondes... Bien sûr il faut suivre celà sur plusieurs jours, semaines, mais je ne suis pas convaincu qu'il y ait un pb de mémoire là dedans...

David B.

[Mothership]

En regardant l'activité "forte" de ce matin, j'obtiens les moyennes suivantes :

Process(_Total)Working set : 772953727,5
Buffer cache hit ratio : 99,71498989
life expectancy : 1359,901568

EN regardant plus précisément les variations
5h - 5h20 : arrivée des utilisateurs / life expectancy 12900
5h20 - 8h25 : activiée de préparation / life expectancy entre 800 - 1500
8h25 : chute du life expectancy à 277 - 400 pendant 10 minutes
8h35 - 9h20 : life expectancy à 600 - 1800
9h20 - 9h30 : 250 - 600
9h30 - 10h30 : 600-1100
10h30 - 10h40 : 400-600
10h40 - 13h00 : 800 - 2000

Le fait de tomber en dessous du seuil de 300 cité par dbaffaleuf pourrait il nous indiquer qu'a certaines périodes, un peu de RAM en plus serait nécessaire ? On pourrait penser que oui.

Concernant la partie Disque et sur la même période :
Disque O:\ (BCK) % disque time : environ 15 - 25 lors des sauvegardes des journaux de TRAN
Disque O:\ (BCK) AVG disque Queue Length : autour de 0

Disque N (TRAN)% disque time : 7.25 % (quelques pics jusqu'a 300% pendant cette période)
Disque N (TRAN)% AVG disque Queue Length :0.07

Disque M (DB)% disque time : 385.87 (des pics jusqu'a 13000)
Disque M (DB)% AVG disque Queue Length : 3.86

Disque L (DB sys )% disque time : 3.26 (pics jusqu'a 500)
Disque L (DB sys)% AVG disque Queue Length : 0.03

Coté CPU utilisation : Average - Maximum
CPU0 : 6 - 26
CPU1 : 6 - 43
CPU2 : 9 - 44
CPU3 : 34 - 69
CPU4 : 0 -19
CPU5 : 1 - 25
CPU6 : 5 - 45
CPU7 : 42 - 71
CPU8 : 6 - 21
CPU9 : 7 -23
CPU10 : 10 - 48
CPU11 : 40 - 65
CPU12 : 0 - 35
CPU13 : 0 - 15
CPU14 : 4 - 36
CPU15 : 40 - 66
On peut voir qu'un coeur par CPU est fortement utilisé "tout le temps" et que lors de lourdes tâches (maintenances sur indexes par exemple) les autres CPU prennent le relai.

Quelqu'un a une idée ?
Je vois en "pas bon" disque M:
J'hésite sur la partie RAM lors des pics d'activités
CPU : OK pour moi

[dbaffaleuf]

Le fait de tomber en dessous du seuil de 300 cité par dbaffaleuf pourrait il nous indiquer qu'a certaines périodes, un peu de RAM en plus serait nécessaire ? On pourrait penser que oui.

Ce serait mettre la solution avant le besoin. La question c'est pourquoi on aurait besoin d'autant de mémoire à ce moment là. Tu as presque fait tout le travail en isolant la plage horaire, reste à trouver le ou les traitement(s) qui provoquent cette chute de PLE (un Table Scan ou un Index Scan qui charge toutes les pages de l'objet dans le cache et qui potentiellement pousse les plus anciennes vers la porte par exemple).

David B.