[SQL Server 2005] SQL Server se limite en mémoire

Version imprimable

03/11/2010, 11h38
Jinroh77

[SQL Server 2005] SQL Server se limite en mémoire

Bonjour,
Je viens de débarquer sur un nouveau serveur en mauvais état, surchargé.
Il est en cours de migration vers de nouvelles versions etc.

OS : Windows Server 2003 R2 SP2 Entreprise x64.
SQL : Microsoft SQL Server Enterprise Edition (64-bit) v9.00.3054.00
RAM : 32 Go
sp_configure retourne :
max server memory (MB) : 280000
min server memory (MB) : 10000

Avant de ma lancer dans une analyse plus poussée, j'ai une question très vague :
Comment se fait-il que SQL Server, malgré ces paramètres, ne semble n'utiliser que 250 Mo de mémoire ? Il y a-t-il une autre propriété qui puisse limiter cela ?

Il y a pourtant des traitements particulièrement lourd, manipulant beaucoup de données, remplissant plus de 3Go dans des tables temporaires :cry: qui sont en train de tourner ; Le CPU est complètement surchargé.
03/11/2010, 11h48
dbaffaleuf

Citation:

Envoyé par Jinroh77

sp_configure retourne :
max server memory (MB) : 280000
min server memory (MB) : 10000

Merci de renvoyer la sortie complète des deux sp_configure pour éviter toute mauvaise interprétation.

Citation:

Envoyé par Jinroh77

Comment se fait-il que SQL Server, malgré ces paramètres, ne semble n'utiliser que 250 Mo de mémoire ?

Merci de nous indiquer quelle est l'origine de cette lecture de 250Mb. Est-ce un compteur perfmon, task manager , etc...
03/11/2010, 11h52
Jinroh77

Bonjour,
Le retour de sp_configure est la run_value (équivalente à la config_value).

Pour ce qui est de l'utilisation mémoire, c'est uniquement le Task manager.

Merci.

1) Il faudrait regarder le contenu des deux trois derniers ring buffers pour ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER':

Code:

select top 3 timestamp, record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER' order by timestamp desc

Si Notification = GROW, alors pas de problème pour allouer.

2) Si tu as une pression mémoire externe à SQL Server:

Code:

select timestamp, record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_RESSOURCE_MONITOR'

Rechercher des occurences du style:
Code:

1 2 3 4 5 6 7 8 (...) <ResourceMonitor> <Notification>RESOURCE_MEMPHYSICAL_LOW< /Notification> <Indicators>2</Indicators> <NodeId>0</NodeId> </ResourceMonitor> (...)
3) Sampler le compteur perfmon '\SqlServer:Buffer Manager\Page Life Expectancy' sur une ou deux heures et regarder si les valeurs tombent sous les 300 secondes.

4) Y a-t-il d'autres consommateurs mémoire (IIS, java) qui tournent sur cette machine en concurrence avec SQL Server ?

5) Si tu peux nous mettre en PJ le résultat d'un DBCC MEMORYSTATUS, ça doit faire 1200/1300 lignes sur une machine sans NUMA node.

1 pièce(s) jointe(s)

Citation:

Envoyé par dbaffaleuf

1) Il faudrait regarder le contenu des deux trois derniers ring buffers pour ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER':

Code:

select top 3 timestamp, record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER' order by timestamp desc

Si Notification = GROW, alors pas de problème pour allouer.

Sur les 3, j'ai bien : <Notification>GROW</Notification>
Citation:
Envoyé par dbaffaleuf
2) Si tu as une pression mémoire externe à SQL Server:

Code:

select timestamp, record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_RESSOURCE_MONITOR'

Rechercher des occurences du style:
Code:

1 2 3 4 5 6 7 8 (...) <ResourceMonitor> <Notification>RESOURCE_MEMPHYSICAL_LOW< /Notification> <Indicators>2</Indicators> <NodeId>0</NodeId> </ResourceMonitor> (...)
Le serveur ne tourne plus maintenant, je testerai cela la prochaine fois.

Citation:

Envoyé par dbaffaleuf

3) Sampler le compteur perfmon '\SqlServer:Buffer Manager\Page Life Expectancy' sur une ou deux heures et regarder si les valeurs tombent sous les 300 secondes.

Idem, je regarderai à la prochaine charge, mais pour le moment je suis plutôt dans les 3 000.

Citation:

Envoyé par dbaffaleuf

4) Y a-t-il d'autres consommateurs mémoire (IIS, java) qui tournent sur cette machine en concurrence avec SQL Server ?

Oui, il y a d'autres choses qui tournent dessus (notamment la raison de la migration) dont du java etc.
C'est vrai que lorsque je regarde la liste des process tournant (tous les utilisateurs) je dois avoir moins de 5Go utilisés, voir même 2.5Go.
Or si je regarde le Physical memory dans le task manager j'ai les valeurs suivantes :
Total : 33 553 388
Avaible : 1 000 000
System Cache : 1 000 000

Où sont passés les autres Mo que je ne vois pas dans la liste des processus ? :aie:

Citation:

Envoyé par dbaffaleuf

5) Si tu peux nous mettre en PJ le résultat d'un DBCC MEMORYSTATUS, ça doit faire 1200/1300 lignes sur une machine sans NUMA node.

En PJ, le rapport, mais sans avoir le système en activité.

03/11/2010, 14h33
Jinroh77

Citation:

Envoyé par Jinroh77

Oui, il y a d'autres choses qui tournent dessus (notamment la raison de la migration) dont du java etc.
C'est vrai que lorsque je regarde la liste des process tournant (tous les utilisateurs) je dois avoir moins de 5Go utilisés, voir même 2.5Go.
Or si je regarde le Physical memory dans le task manager j'ai les valeurs suivantes :
Total : 33 553 388
Avaible : 1 000 000
System Cache : 1 000 000

Où sont passés les autres Mo que je ne vois pas dans la liste des processus ? :aie:

Je viens d'essayer une nouvelle chose...
SQL Server avait autorisation d'utilisé 28 Go, mais n'affichait que 250 Mo.
Il restait alors au système dans les 2Go de libre sur 32Go.

Si je réduis la valeur max de SQL Server à 10 Go, le système se retrouve alors avec près de 20 Go de libre...

Il s'avère donc que c'est bien le moteur qui utilise cette mémoire alors que cela ne semble pas être visible sur le moniteur de tâches :?
Il y a un autre moyen de savoir combien le moteur utilise vraiment de mémoire ?

En vérifiant, j'ai le même comportement sur le serveur cible (Win Server 2008 R2 x64) et je n'ai sur chaque serveur qu'1 seule instance de SQL.
03/11/2010, 15h28
mikedavem

Bonjour,

C'est normal que tu ne vois pas la totalité de la mémoire occupée par SQL via le gestionnaire des tâches. Ce dernier reflète la mémoire utilisée par le processus sqlservr (Working Set).

La mémoire allouée et verrouillée en mémoire pour SQL Server (qui concerne entre autre le cache des données) n'est plus visible à ce moment là (Cf le rapport DBCC MEMORYSTATUS et AWE allocated).

Dans ce cas tu peux utiliser:
- Le compteur de performance SQL Server:Memory Manager : Total Server Memory(KB)
- La DMV sys.dm_os_sys_info et les colonnes bpool_*

++
03/11/2010, 15h51
Jinroh77

Citation:

Envoyé par mikedavem

Bonjour,

C'est normal que tu ne vois pas la totalité de la mémoire occupée par SQL via le gestionnaire des tâches. Ce dernier reflète la mémoire utilisée par le processus sqlservr (Working Set).

La mémoire allouée et verrouillée en mémoire pour SQL Server (qui concerne entre autre le cache des données) n'est plus visible à ce moment là (Cf le rapport DBCC MEMORYSTATUS et AWE allocated).

Dans ce cas tu peux utiliser:
- Le compteur de performance SQL Server:Memory Manager : Total Server Memory(KB)
- La DMV sys.dm_os_sys_info et les colonnes bpool_*

++

Merci pour cette précision.
C'est donc à ce AWE allocated que je dois me fier pour retrouver la mémoire utilisée au total par le service.

Merci.
03/11/2010, 16h29
dbaffaleuf

SQL Server est en 32 ou 64 bits alors ?
03/11/2010, 16h38
mikedavem

Citation:

OS : Windows Server 2003 R2 SP2 Entreprise x64.
SQL : Microsoft SQL Server Enterprise Edition (64-bit) v9.00.3054.00

64 bits à mon avis :)

++
03/11/2010, 16h58
dbaffaleuf

Alors c'est quoi le rapport avec AWE ? Admettons que le compte de service ait le privilège Lock Pages in Memory (ce qui est seulement une hypothèse dans le cas présent), le memorystatus renverrait l'utilisation de la mémoire verrouillée comme de l'AWE ?? Ca me paraît vraiment bizarre, 'awe enabled' est ignoré en 64 bits.
03/11/2010, 17h09
Jinroh77

Le AWE Enable n'a pas été activé sur ce serveur.
Ce que j'expliquais est que le seul moyen d'identifier la quantité de ram utilisée était de voir le rapport de DBCC MEMORYSTATUS ou la DMV.
03/11/2010, 17h10
mikedavem

Je suis d'accord avec toi .. AWE est ignoré en 64 bits mais le fait d'octroyer le droit de verrouillage de pages en mémoire pour le compte SQL Server fait que la commande DBCC MEMORYSTATUS renvoie le nb de pages verrouillés avec le paramètre AWE en 64 bits

Je suppose fortement que ce droit a été paramétré bien entendu.

Cf KB Microsoft
Je cite : (traduction en français mais on comprend :-) )

Citation:

les extensions de fenêtrage d'adresse (AWE, Address Windowing Extensions) affectée : cette valeur indique la mémoire est allouée via le mécanisme AWE sur la version 32 bits du produit. Ou bien, cette valeur indique le montant global de la mémoire est consommée par les pages verrouillées sur la version 64 bits du produit.
03/11/2010, 17h14
dbaffaleuf

Ah OK, merci pour l'info !
03/11/2010, 17h16
Jinroh77

Citation:

Envoyé par mikedavem

Je suis d'accord avec toi .. AWE est ignoré en 64 bits mais le fait d'octroyer le droit de verrouillage de pages en mémoire pour le compte SQL Server fait que la commande DBCC MEMORYSTATUS renvoie le nb de pages verrouillés avec le paramètre AWE en 64 bits

Je suppose fortement que ce droit a été paramétré bien entendu.

Cf KB Microsoft
Je cite : (traduction en français mais on comprend :-) )

Hummm si je comprends bien, c'est un droit particulier qui a été donné (ou non) au compte qui implique que je ne vois pas "correctement" l'utilisation mémoire dans le gestionnaire de tâches ?
03/11/2010, 17h27
mikedavem

Ce n'est pas que tu ne vois pas correctement la mémoire utilisée. Le fait de donner ce droit de verrouillage fait que l'allocation des pages ne peut plus se faire dans le l'espace de travail du processus .. Seules les pages qui peuvent être paginées peuvent être présentes dans cet espace .. d'où les résultats que tu as pu observer via le gestionnaire des tâches.

tu peux vérifier si le compte de service utilisé par SQL Server a ce droit dans les stratégies locales du serveur

++
03/11/2010, 17h37
Jinroh77

le compte de service utilisé pour SQL Server est le "Local System" :cry:
Dans Local Security Policy\Local Policy\User Rights Assignment
J'ai donc ajouté pour le Lock Page in memory, les comptes Local Service et System.

Par contre, j'ai l'impression qu'il faut peut-être redémarrer l'instance ou le serveur car cela n'a encore rien changé.
De plus, dans les infos de cette propriété je trouve :
"Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM)."
:?
12/11/2010, 09h45
Jinroh77

Citation:

Envoyé par Jinroh77

le compte de service utilisé pour SQL Server est le "Local System" :cry:
Dans Local Security Policy\Local Policy\User Rights Assignment
J'ai donc ajouté pour le Lock Page in memory, les comptes Local Service et System.

Par contre, j'ai l'impression qu'il faut peut-être redémarrer l'instance ou le serveur car cela n'a encore rien changé.
De plus, dans les infos de cette propriété je trouve :
"Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM)."
:?

Une petite précision sur ces dernières questions ?

Merci :)
12/11/2010, 11h21
dbaffaleuf

Oui il faut redémarrer l'instance. Le contexte de droits du compte de service ne prend effet qu'au moment du démarrage donc toute modification nécessite un restart. Tu peux vérifier ensuite que les pages sont verrouillées dans l'ERRORLOG de l'instance, il doit y avoir un message du genre:

Code:

"Using locked pages for buffer pool"

Il faut ajuster la valeur de max server memory. Si la machine est dédiée, je laisse 1Gb supplémentaire à l'OS par palier de 16Gb de mémoire, sachant qu'il faut de toutes façons 2 Gb de marge pour gérer 16Gb. Ex:

16Gb => 2Gb de marge.
32 Gb => 3 Gb de marge.
48 Gb => 4 Gb de marge.
64 Gb => 5 Gb de marge.
etc...

Donc avec 32 Gb, il faut laisser 3Gb, soit max server memory = 29 Gb.