Surconsommation mémoire - SQL Server 2014

Bonjour,

J'ai une instance SQL Server 2014 (SP1) qui consomme de la RAM au delà de ce que je lui ai autorisé dans "Max memory".
Je lui ai autorisé 78000Mo sur un Cluster Windows 2012 R2 (instance SQL clusturisée donc) mais au moment ou j'écris ce post elle consomme 79052Mo.
Dernièrement, j'ai dû basculer d'un noeud vers l'autre afin de relancer le service SQL clusturisé mais je voudrais éviter de basculer d'un noeud à l'autre trop souvent.

Comment je peux faire pour identifier ce qui fait que SQL Server dépasse la limite du Max mémory que je lui ai fixé, svp ?

Pour vous permettre une première analyse, j'ai lancé un DBCC MEMORYSTATUS :
Process/System Counts

• Available Physical Memory : 17313972224
• Available Virtual Memory : 140563811475456
• Available Paging File : 32569753600
• Working Set : 82965630976
• Percent of Committed Memory in WS : 100
• Page Faults : 21460163
• System physical memory high : 1
• System physical memory low : 0
• Process physical memory low : 0
• Process virtual memory low : 0

Memory Manager

• VM Reserved : 167906300
• VM Committed : 79853284
• Locked Pages Allocated : 0
• Large Pages Allocated : 0
• Emergency Memory : 1024
• Emergency Memory In Use : 16
• Target Committed : 79872000
• Current Committed : 79853288
• Pages Allocated : 51728896
• Pages Reserved : 0
• Pages Free : 25810160
• Pages In Use : 5643016
• Page Alloc Potential : 70235384
• NUMA Growth Phase : 2
• Last OOM Factor : 0
• Last OS Error : 0

le depassement de la RAM n'est pas enorme
Que se passe t il si la RAM depasse un peu ? Ca swappe sur le disque ?
vous avez laissé combien de RAM a l'OS windows ?

VM Committed : 79853284 Ko
79853284 / 1024 = 77981,72 Mo
Donc ça ne dépasse pas.

Le cluster se compose de 2 machines virtuelles configurées comme ceci :
- OS : Windows 2012 R2
- Disques :
C:\ 100Go (réservés au système)
D:\ 100Go (réservés à l'installation des logiciels)
E:\ 50Go (réservés exploitation)
V:\ 128Go (réservés à la VRAM)
- Disques spécifiques du cluster et de l'instance SQL (= disques partagés entre les noeuds du cluster) :
S:\ 2To (réservés au fichier de bases de données .mdf, .ndf)
L:\ 1To (réservé au logs de bases de données .ldf)
T:\ 1Go (réservé aux rôle de transactions distribuées MSDTC)
Q:\ 1Go (réservé au Quorum du cluster)
- RAM : 100 Go

DBCC MEMORYSTATUS du jour :

Process/System Counts

• Available Physical Memory 16556376064
• Available Virtual Memory 140562923618304
• Available Paging File 31795052544
• Working Set 83634253824
• Percent of Committed Memory in WS 100
• Page Faults 23908848
• System physical memory high 1
• System physical memory low 0
• Process physical memory low 0
• Process virtual memory low 0

Memory Manager :

• VM Reserved 168034620
• VM Committed 79803916
• Locked Pages Allocated 0
• Large Pages Allocated 0
• Emergency Memory 1024
• Emergency Memory In Use 16
• Target Committed 79872000
• Current Committed 79803920
• Pages Allocated 44343784
• Pages Reserved 0
• Pages Free 32982064
• Pages In Use 11036328
• Page Alloc Potential 64842072
• NUMA Growth Phase 2
• Last OOM Factor 0
• Last OS Error 0

@rudib : Aujourd'hui encore, la RAM ne dépasse pas en suivant ce calcul 79803916 / 1024 = 77 933,51 Mo
Pourtant quand je regarde le Gestionnaire de tâches de Windows, pour mon processus "SQL Server (Instance nommée)" il m'indique 79 698,1 Mo au moment ou j'écris ce post.

Citation:

---

Envoyé par crashyear

Pourtant quand je regarde le Gestionnaire de tâches de Windows, pour mon processus "SQL Server (Instance nommée)" il m'indique 79 698,1 Mo au moment ou j'écris ce post.

---

Bonjour,

Attention, la mémoire utilisée par le processus "SQL Server" peut refléter plus que "max server memory", en effet, certaines allocations mémoires ne sont toujours pas incluses dans "max server memory", et ce, même dans les versions SQL Server 2012 (incluses) et versions supérieures où les régisseurs de mémoires prennent en compte beaucoup plus de choses etc. qui auparavant n'étaient pas prises en compte dans les versions SQL Server 2008 R2 (incluse) et antérieures.

Donc, un petit conseil, cessez de comparer la mémoire utilisée par le processus "SQL Server" dans le gestionnaire des tâches et "max server memory" !

A+

@hmira : Merci pour ce conseil.
Mais est-il possible d'avoir plus d'explications quant aux allocations mémoires qui ne sont pas prises en compte dans le "max memory server", svp ?
J'ai lu également cette discussion et, il y a ici aussi, quelqu'un qui parlent de composant internes ou externes qui peuvent allouer de la mémoire au delà du seuil paramétré, mais il n'est pas précisé quoi :
https://www.developpez.net/forums/d1...r/#post5819477
Ce que je veux, c'est identifier ce qui me bouffe ma RAM, et comment l'éviter car j'ai déjà eu une saturation mémoire qui a entraîné une coupure de service et j'aimerais éviter que cela se reproduise.

Cela peut effectivement dépasser, les cas sont indiqués dans la documentation suivante : https://support.microsoft.com/en-us/...erver-2012-and

Si tu veux analyser plus loin, laisse tomber DBCC MEMORYSTATUS. Tu peux voir :

1/ l'allocation par clerks pour savoir comment est utilisée ta RAM : sys.dm_os_memory_clerks

2/ l'allocation de la mémoire par SQL Server, encore plus profondément : sys.dm_os_virtual_address_dump

Je n'ai pas de SQL Server sous la main tout de suite pour te faire de belles requêtes avec ça, je reposte ici plus tard au besoin.

Ta machine est en NUMA ?

Aujourd'hui, le processus SQL Server dans le Gestionnaire de tâche est rendu à 80 727 Mo
Le DBCC MEMORYSTATUS "Vm Committed" me renvoi 79853556 / 1024 = 77981,99 Mo

1/ J'ai regardé ce que retourne la "sys.dm_os_memory_clerks" et je constate que cela correspond presque au rapport standard "Consommation de mémoire" sur l'instance (je le met donc en PJ)
Pièce jointe 293049

2/ J'ai aussi regardé "sys.dm_os_virtual_address_dump" mais je ne sais pas comment l'interpréter (si vous avez une requête je suis preneur ;) )

3/ Pour la configuration NUMA, je ne suis pas sur d'y être. Comment puis-je le vérifier/contrôler ?
J'ai trouvé cette requête SQL pour savoir si je suis en NUMA ou pas :

Code:

---

1 2	SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks

---

>>> Résultat (2 lignes) :
memory_node_id
- 0
- 64

Je reviens, j'ai un peu plus de temps :)

Cette requête te donne ce qui est alloué et qui n'est pas du buffer pool :

Code:

---

1 2 3 4 5

SELECT type, virtual_memory_reserved_kb/1024.0 as virtual_memory_reserved_mb FROM sys.dm_os_memory_clerks WHERE TYPE NOT LIKE '%bufferpool%' AND virtual_memory_reserved_kb > 0

---

Mais normalement c'est inclut dans le MAXMEM. Vois-tu une différence ? ou des clerks étranges ?

Bonjour,
Merci pour cette requête.

Voici son résultat :

• MEMORYCLERK_SQLSTORENG : 39.125000
• MEMORYCLERK_SQLCLR : 6156.375000
• MEMORYCLERK_XE_BUFFER : 8.625000
• OBJECTSTORE_LOCK_MANAGER : 256.000000
• MEMORYCLERK_SOSMEMMANAGER : 121.589843

J'ai mis en gras le type qui consomme peut-etre un peu trop de RAM ?

Aujourd'hui DBCC MEMORYSTATUS me retourne :
Process / System Counts

• Available Physical Memory 19020214272
• Available Virtual Memory 140563415097344
• Available Paging File 62780907520
• Working Set 83342467072
• Percent of Committed Memory in WS 100
• Page Faults 22233462
• System physical memory high 1
• System physical memory low 0
• Process physical memory low 0
• Process virtual memory low 0

Memory Manager

• VM Reserved 167905724
• VM Committed 79859960 = 77988,24 Mo alors que mon process windows sqlsevr.exe pour mon instance utilise 79Go de RAM au même moment
• Locked Pages Allocated 0
• Large Pages Allocated 0
• Emergency Memory 1024
• Emergency Memory In Use 16
• Target Committed 79872000
• Current Committed 79859960
• Pages Allocated 53550464
• Pages Reserved 0
• Pages Free 23969184
• Pages In Use 10424400
• Page Alloc Potential 65454000
• NUMA Growth Phase 2
• Last OOM Factor 0
• Last OS Error 0

Fais tu du spatial ? du XML ? As tu des UDT .net ? Des routines .net ???

A +

Tu peux essayer d'identifier les requêtes qui consomment du .NET ainsi :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT st.text, qs.total_worker_time, qs.last_worker_time, qs.min_worker_time, qs.max_worker_time, qs.total_clr_time, qs.last_clr_time, qs.min_clr_time, qs.max_clr_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st WHERE qs.execution_count > 1 AND qs.total_clr_time > 0 ORDER BY qs.total_clr_time DESC;

---

... mais cela dit ça ne me choque pas, sur ma machine actuellement j'ai
MEMORYCLERK_SQLCLR 9228.875000
alors que je sais qu'il n'y a pas d'activité de ce côté. C'est sans doute juste .NET qui est chargé en mémoire.

Oui, il y a en effet une DLL qui a été développée en interne et qui utilise du .NET
Elle est déclarée dans plusieurs bases de données de mon instance en tant qu'assembly.
Est-ce que vous savez s'il est possible de définir ou d'estimer la taille mémoire que peut utiliser cette DLL ?

Regarde si tu as quelque chose dans ceci :

Code:

---

SELECT * FROM sys.dm_clr_appdomains;

---

Par contre ça doit être compris dans le max mem :
http://itknowledgeexchange.techtarge...server-memory/

Ce qui échappe au maxmem, ce sont des allocations directes à la mémoire, depuis un code comme une dll, une proc étendue par exemple, qui fait un VirtualAlloc().

https://blogs.msdn.microsoft.com/sql...l-server-2012/

Tu verrais des DLL avec ceci :

Code:

---

1 2 3

SELECT base_address, description, name FROM sys.dm_os_loaded_modules WHERE company NOT LIKE N'Microsoft%';

---

Bonjour,
Voici le résultat de la requête :

• appdomain_address: 0x0000001359AA4340
• appdomain_id : 2
• appdomain_name : MaBase.dbo[runtime].1
• creation_time : 2017-07-18 21:05:41.330
• db_id : 36
• user_id : 1
• state : E_APPDOMAIN_SHARED
• strong_refcount : 1
• weak_refcount : 1
• cost : 172456
• value : 11534336
• compatibility_level : 100
• total_processor_time_ms : 3656
• total_allocated_memory_kb : 45748
• survived_memory_kb : 0

45 Mo ? Tu peux aussi regarder ici

Code:

---

1 2 3

SELECT * FROM sys.dm_os_memory_objects WHERE TYPE LIKE '%clr%'

---

mais comme je te disais, tout ça est dans la limite du maxmem depuis SQL Server 2012 ... Il faudrait vraiment avoir une DLL qui fasse des appels directs à la mémoire. Ça arrive, il y a des trucs comme Sophos qui installent leur DLL
https://community.sophos.com/product...-my-sql-server

Oui apparemment 45Mo seulement...

La requête que tu viens de me transmettre me retourne les résultats que j'ai mis dans un fichier Excel ci-joint.
Pièce jointe 295981

J'ai mis en surlignage rouge dans le fichier Excel la ligne qui me renvoie la plus grande valeur : 22044672bytes = 22Mo c'est bien ça ?

@Rudib : je viens de télécharger ton outil "Sql Trismegiste", c'est super ! Merci.

En complément de ce qui a dit par rudib, pour analyser plus en détails les "VirtualAlloc" il existe un outil gratuit VMMap de la suite Windows Sysinternals
Windows Sysinternals (Sysinternals Suite)
https://technet.microsoft.com/en-us/sysinternals/bb842062.aspx
ou
Windows Sysinternals (VMMap)
https://technet.microsoft.com/en-us/sysinternals/vmmap

Comme le montre la figure ci-dessous, il faut se focaliser sur la ligne "Private Data" et la colonne "Private WS" dont ci-dessous les descriptions respectives en anglais uniquement (désolé, je n'ai pas la traduction en français !).

Private data
Private data memory is memory allocated by VirtualAlloc and not suballocated either by the Heap Manager or the .NET run time. It cannot be shared with other processes, is charged against the system commit limit, and typically contains application data.

Private WS
The amount of physical memory assigned to the type or region that cannot be shared with other processes.

Remarque : Dans notre exemple le Total --> "Private WS" soit 49039 K correspond un peu à ce que l'on peut observer dans le Gestionnaire des tâches "Mémoire (jeu de travail privé) ou dans le moniteur de ressource Onglet "Mémoire" colonne "Privé" du processus sélectionné.

Pièce jointe 296444

A+

Citation:

---

Envoyé par crashyear

@Rudib : je viens de télécharger ton outil "Sql Trismegiste", c'est super ! Merci.

---

Oui, je confirme, SQLTrismegisie, développé par Rudi Bruchez, est un excellent outil, pour l'analyse et le diagnostique d'une instance SQL Server.

Parmi les fonctionnalités (liste non exhaustive) que personnellement j'apprécies, on peut citer :
- La possibilité de définir ses propres "Hermetica" et donc de personnaliser voire réécrire le "Corpus Hermeticum" tel que défini et conceptualisé par Rudi Bruchez
- La possibilité d'exporter tous les résultats de l'audit de l'instance SQL Server, dans un fichier archive (.zip)
- Etc.

Remarque : En rédigeant de nouveaux "Hermetica", d'aucuns pourront se prendre pour Hermès ! Ils auront tort, ils n'en auront jamais la stature :D

A+