Discussion :

[KyoshiroKensei]

Bonjour,

Suite à un changement de fonctionnement de notre installation, l'un de nos serveurs SQL doit fonctionner 24/24h , 7/7j tout l'année. Environ 1 fois par mois, ce serveur BDD "fige" avec comme message dans le jdb windows "Mémoire système insuffisante dans le pool de ressources 'internal' pour exécuter cette requête.". Pour résoudre le problème, je redémarre le serveur.

J'aimerai savoir si j'ai un moyen de "libérer" la mémoire utilisée par SQL. J'ai trouvé les commandes DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE, DBCC FREEPROCCACHE mais pas d'info sur leur impact sur SQL. Puis je utiliser ces commandes pendant que sql fonctionne? Est ce que cela va dégrader les performances ou bloquer l'accès pendant l’exécution de ces commandes?

Infos du Serveur:
- Machine virtuelle
- Ballonning non actif
- Windows 2008 64bits
- sql server 2008 R2 64 bits
- aucun applicatifs ni role en dehors de sql sur ce serveur
- 8Go de ram
- 15 bases de données dont 4 répliquées

merci d'avance.

PS:
Je n'avais pas ce problème auparavant car ce serveur redémarrait 1 fois par semaine.

[SQLpro]

Quel est le contenu de sp_configure ?

Si vous n'avez pas limité la RAM utilisée par SQL Server, il peut bouffer toute la mémoire au détriment même de l'OS. Les réglages de SQL Server avec une VM sont généralement plus complexes, et les investigations pour recherches de problématique donnes des résultats biaisés. C'est d'ailleurs ce que j'explique dans mon livre à paraître....

A +

[KyoshiroKensei]

la limite est celle par défaut soit 2Po.

Par contre, je me suis mal exprimé: le serveur fonctionne parfaitement du point de vu windows, par contre sql refuse toute nouvelle connexion ou requête et met à chaque fois dans le jdb le message d'erreur. Il en résulte que mes applications fige car la base n'est pas disponible.

max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 0
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0

[SQLpro]

Ne dépassez pas 4 Go de RAM à SQL Server. Quel est le contenu des evt ?

De toute façon à mon sens 8 Go en 64 bits avec 15. bases dont 4 répliquées, me parait plus que light !
Quel est le volume des bases ?

A +

[KyoshiroKensei]

Les bases font entre 10 mo et 4 go pour un total de 10 giga. Toutes nos applications utilisent uniquement des procédures stockées.

Dans le jdb, on vois des résultats de dbcc memorystatus puis le message d'erreur en continu.

[rudib]

Bonjour,

le message mémoire insuffisante sur une requête se produit rarement. Vous n'avez pas besoin d'utiliser des trucs comme DBCC FREEPROCACHE : SQL server redimensionne automatiquement ses caches pour accorder plus de mémoire à la mémoire de requête. Il peut se produire deux choses :

- votre requête utilise beaucoup de mémoire pour faire du tri ou un hash, et la mémoire de requête (qu'on appelle aussi workspace memory) n'est pas suffisante. Donc, première question : est-ce que cela se produit sur une requête lourde ? Et si oui, pouvez-vous poster le plan d'exécution réalisé (en XML), pour qu'on puisse voir s'il y a un problème de ce type.

- la mémoire de SQL Server est limitée, ou mangé de l'intérieur par une activité peu recommandable (des curseurs en pagaille, du code .NET, ...). Dans ce cas, vous pouvez analyser les objets en mémoire avec la requête suivante :

Code :

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

1
2
3
4
SELECT mo.type, SUM(mo.pages_allocated_count * mo.page_size_in_bytes) / 1024 / 1024.00 as mb_en_memoire
FROM sys.dm_os_memory_objects mo
GROUP BY mo.type
ORDER BY mb_en_memoire DESC;

Que donne le résultat ?

[KyoshiroKensei]

bonjour,

comme dis dans mon premier post, il ne s'agit pas d'un requete sql particulière. A l'aube des 22 jours de fonctionnement sans redémarrage de mon serveur sql (serveur sans autre fonction ni role) le serveur sql coupe des connexions et refuse l’exécutions de requêtes suite à un manque de place de dans le cache mémoire. Je n'ai pas ce problème sur mes autres serveurs car eux sont redémarrés toutes les semaines.

J'utiliserai votre requete lors du prochain plantage. L'objectif du dbcc freecache est de libérer la mémoire sans redémarrer le serveur pour limite l'indisponibilité.

[SQLpro]

bonjour,

comme dis dans mon premier post, il ne s'agit pas d'un requete sql particulière. A l'aube des 22 jours de fonctionnement sans redémarrage de mon serveur sql (serveur sans autre fonction ni role) le serveur sql coupe des connexions et refuse l’exécutions de requêtes suite à un manque de place de dans le cache mémoire. Je n'ai pas ce problème sur mes autres serveurs car eux sont redémarrés toutes les semaines.

Ceci est particulièrement stupide. On de doit JAMAIS arrêter un serveur SQL... Voici ce que je dit dans mon ouvrage à paraître :

"
13.2.12 - Arrêt du serveur

Il ne faut jamais arrêter un serveur SQL en production. Outre de vider le cache, ceci fait perdre toutes les statistiques d’exécution accumulées depuis le démarrage du serveur. C’est pourquoi il faut toujours chercher une solution « on line ». C’est la raison même d’exister de la version Datacenter qui permet l’ajout ou le retrait à chaud de CPU et de RAM.
De la même manière arrêter le service SQL Server est déraisonnable. Cherchez les causes de votre problème à coup de requête ceci aura le double avantage de permettre à la fois le diagnostic et la résolution...

PERFORMANCES : tout arrêt de SQL Server ou de la machine pose des problèmes de performance. Dans SQL Server, toutes les opérations peuvent se faire à chaud. Avec la bonne version de Windows, même certaines opérations hardware peuvent s’effectuer alors que la machine fonctionne. N’arrêtez donc jamais ni la machine ni le service SQL Server.
"

J'utiliserai votre requete lors du prochain plantage. L'objectif du dbcc freecache est de libérer la mémoire sans redémarrer le serveur pour limite l'indisponibilité.

Libérer de la mémoire est aussi une opération stupide. SQL Server gère le cache au mieux...

Visiblement vous ne tenez pas compte des remarques postées pour vous aider. Vos méthode d'administration de vos seveur SQL sont particulièrement idiotes. Formez vous à l'administrationSQL Server et profitez des conseils données gratuitement ici au lieu de vous enferrer dans votre façon grotesque de faire....

Vos problèmes doivent être investigué à chaud; SQL Server conserve de nombreuses données d'historique de fonctionnement pour diagnostic, En arrêtant le serveur ou en purgeant la RAM, vous vous privez stupidement des données les plus précieuses pour savoir ce qui s'est passé !

Il est assez lamentable de voir comment un outil aussi pointu que SQL Server peut être gâché par la bêtise de pratiques ignobles, l'enfermement dans l'inculture et l'ignorance des conseils...

A +

[KyoshiroKensei]

Il me semble avoir été poli et respectueux jusqu'à présent. Puisque vous me répondez de manière agressive, je vais faire de même.

A part faire de la pub pour vos livre quels conseils avez vous donnez?
Votre 1er post:

Si vous n'avez pas limité la RAM utilisée par SQL Server, il peut bouffer toute la mémoire au détriment même de l'OS. Les réglages de SQL Server avec une VM sont généralement plus complexes, et les investigations pour recherches de problématique donnes des résultats biaisés. C'est d'ailleurs ce que j'explique dans mon livre à paraître....

--> rien d'utile si ce n'est ce que je sais déjà: difficile d'analyser la mémoire en virtualisation car donnée pas fiable.

Votre 2ème post:

Ne dépassez pas 4 Go de RAM à SQL Server. Quel est le contenu des evt ?

De toute façon à mon sens 8 Go en 64 bits avec 15. bases dont 4 répliquées, me parait plus que light !
Quel est le volume des bases ?

-->> J'ai tenu compte de cette remarque et regarde pour ajouter de la mémoire même si vous vous contredisez : vous conseillez 4go pour sql. Il reste donc 4go juste pour windows. Vu qu'il consomme à peine 1,5go, je vois pas en quoi ajouter de la mémoire non utilisée changera quelque chose, mais bon testons.


Un 3ème post qui n'a pas pris le temps de lire mon problème et qui répond à coté en partie. Je conserve sa requete pour voir ce qui ce passe lors du prochaini plantage. J'ajouterai qu'il y a parfois un gouffre entre la théorie et la pratique.

Et puis dans le monde réel, je n'ai pas 30mn pour dépanner. Un serveur BDD indisponible, c'est une partie d'usine a l’arrêt donc de l'argent perdu. Enfin, j’hérite d'un historique de BDD mis en place par des personnes qui ne sont plus là, donc je fais ce que je peux pour maintenir et améliorer l'existant.

Au final vous m'insultez sans connaitre. De plus si (je vous cite), " SQL Server gère le cache au mieux..", alors je n'aurais pas de problème de mémoire au bout d'un mois de fonctionnement.

Si ma méthode est stupide, vous l’êtes tout autant dans votre attitude. En informatique, il n'y a pas que des bonnes ou des mauvaises façons. Le monde n'est pas manichéen. IL y a aussi des pratiques adaptées à chaque situation. Mon premier problème est de prévenir ces plantages en attendant une solution trouvée suite à l'analyse. Personnellement, je penses que le problème vient plus de la virtualisation que de SQL lui même.

J'ajouterai bien une flopée de mot exprimant clairement le coté pitoyable de votre personnalité, d'autant plus de la part d'un "soi disant" expert. Voyez, au début de la conversation, j'étais près à vous demander quels bouquins vous aviez ecrit pour les consulter. Maintenant, ce n'est plus le cas.

Quand à la formation, comme beaucoup, j'apprend sur le tas faute de formation fournie mais une véritable discussion se toujours mieux que de la simple lecture d'un livre.

[rudib]

J'utiliserai votre requete lors du prochain plantage.

N'attendez pas le prochain plantage. Si quelque chose mange petit à petit la mémoire de SQL Server, vous verrez l'évolution à l'aide de la requête sur sys.dm_os_memory_objects. J'ai eu le cas chez un client un jour : du code planifié qui ouvrait un curseur et qui ne le refermait jamais. Les curseurs restaient ouverts et consommaient petit à petit toute la mémoire.

[SQLpro]

Vous vous méprenez. Je ne vous ais ni insulté, ni été agressif envers vous. j'ai été direct et j'ai critiqué votre façon de faire. Je ne suis ni adepte du consensus mou et du politiquement correct et dis que que je pense, que cela vous choque ou pas, je m'en fous.
Ne vous connaissant pas personnellement, il m'est impossible de vous juger et même si je vous connaissais je me garderais bien de vous juger.
J'ai simplement dit que vos pratiques était stupide et que vous n'écoutiez pas les conseils prodigués.... Par exemple les conseils de rudi bruchez serait sans aucun doute déterminant, Y COMPRIS DANS L'IMMEDIAT pour savoir ce qui se passe dans votre serveur. Relisez ce post !

Quand à la virtualisation, c'est effectivement une source d'emmerdements supplémentaires vu que vous rajoutez une couche. D'ailleurs MS ne garantie pas un serveur SQL sur VM et en cas d'appel à la hot line, ils vous demanderons de commencer par dévirtualiser !

A +

[KyoshiroKensei]

merci rudib, je vais suivre votre conseil. J'avais compris que votre instruction était à utiliser lors du plantage pour analyse ensuite.

Je vais donc surveiller les objets en mémoire de manière régulière et traquer d'éventuelles anomalies.


Sql Pro. Dire d'un pratique qu'elle est stupide sans connaitre le pourquoi et le comment étant déjà un jugement, vous repasserez pour l'absence de jugement. J'accepte les critiques si constructives. Un simple jugement est inutile.
Mais bon passons.

Pour la dévirtualisation, malheureusement, c'est impossible. Par contre je suis surpris de votre remarque sur le fait que Microsoft ne garantie pas sql serveur sur VM. Les "commerciaux" microsoft se sont bien gardé de nous le dire lorsque nous avons acheté les licences.

Nous avons virtualiser pour séparer les les roles DNS/ TSE / Applicatif /BDD. Vous conseillez donc une serveur physique pour la BDD.

J'en revient à ma question initiale, car en attendant de diagnostiquer le problème, je dois le prévenir. Si je ne dois pas redémarrer le serveur, comment prévenir la panne?

[rudib]

Pour prévenir la panne, il faut la diagnostiquer. Donc savoir ce qui se passe dans la mémoire, donc exécuter la requête que je t'ai donnée. Peux-tu poster le résultat de ce jour ?

[KyoshiroKensei]

Résultat ( 16/ 108):
MEMOBJ_STATEMENT 1103.4843750
MEMOBJ_COMPILE_ADHOC 784.5781250
MEMOBJ_XSTMT 402.8125000
MEMOBJ_QUERYEXECCNTXTFORSE 265.3750000
MEMOBJ_EXECUTE 153.0546875
MEMOBJ_PARSE 129.3750000
MEMOBJ_SQLMGR 39.9296875
MEMOBJ_CACHESTORETOKENPERM 32.6875000
MEMOBJ_LOCKBLOCKS 30.5156250
MEMOBJ_SORTTABLE 28.8359375
MEMOBJ_SOSSCHEDULER 20.2812500
MEMOBJ_METADATADB 20.1718750
MEMOBJ_LOCKOWNERS 15.2734375
MEMOBJ_SOSNODE 14.8984375
MEMOBJ_CACHESTORESQLCP 12.1406250
MEMOBJ_SOSSCHEDULERMEMOBJPROXY 8.2968750

Je regarde sur le net lasignification de tout ceci. En attendant, je poste le résultat.

[rudib]

a priori ça paraît pas mal, rien de suspect. Il y a plus de procédures stockées que de requêtes adhoc ?

quelques idées :

1. l'option "optimize for adhoc workloads" est-elle activée sur l'instance ? Si non, mets-la à true
2. mets le max server memory à 7 GO
3. mets le min server memory à 3 GO

pourquoi ?

1. "optimize for adhoc workloads" va t'économiser de la mémoire sur le cache de plans
2. max server memory : tu laisses un peu de RAM à Windows pour respirer
3. min server memory : si le problème vient d'un autre processus que SQL Server, par exemple un service qui a une fuite de mémoire, il ne pourra pas de prendre toute ta RAM

Si le problème vient du point 3 : un autre processus qui a une fuite de mémoire, tu risques par contre d'avoir un problème dans Windows au lieu de l'avoir dans SQL Server. Il faut aussi que tu suives l'évolution de la mémoire par processus à l'aide du moniteur de performances de Windows.

[KyoshiroKensei]

oui, nous travaillons uniquement avec des procédures stockées lorsque c'est possible pour les bénéfices que cela procure. MEMOBJ_STATEMENT c'est le cache des procédures stockées? J'ai du mal à trouver une signification à tout ces termes/abréviations sur le net. As tu un site ou on peut obtenir une explication des valeurs de ces types d'objet mémoire?


"optimize for adhoc workloads" n'est pas active. Je vais l'activer. Je peux faire ça à chaud? Si je gagne sur ce cache, ne vais pas diminuer les performances? (je vais compiler au moins 2 fois chaque procédure avant de mettre en cache.)
J'ai consulter la réutilisation des plans: sur 21403 plans dans dm_exec_cached_plans , seuls 6250 sont réutilisés plus d'une fois. Cela représente 1,6 go en cache qui ne sont pas utile. Pour bonne partie, les plans contiennent des updates sur sysjobschedules de la date de prochaine exécution. Je vais donc tester cette option. Ce qui est dommage, c'est que ça n'a pas d'effet sur les plans déjà en cache.

Serait il possible que l’exécution des agents saturerai ma mémoire? Il est vrai que mes agents s’exécute pas mal de fois par jour et cela expliquerai la dérive sur le temps.

Ok pour la mémoire,je vais configurer ça. J’espère pas vraiment que la cause soit le point 3 car en dehors de windows, je n'ai aucun applicatif sur ce serveur et la virtualisation ne m'aide pas beaucoup pour savoir ce qui se passe en mémoire.

[SQLpro]

...Pour la dévirtualisation, malheureusement, c'est impossible. Par contre je suis surpris de votre remarque sur le fait que Microsoft ne garantie pas sql serveur sur VM. Les "commerciaux" microsoft se sont bien gardé de nous le dire lorsque nous avons acheté les licences.

les commerciaux sont là pour vendre, sans état d'âme. Si les commerciaux étaient techniquement compétent il ferait un autre métier !
D'autre part vous n'avez pas indiqué la nature du système de VM...
Extrait de la politique MS :
"
Pour les clients ayant un contrat de support Microsoft de niveau Premier, Microsoft entreprendra des « efforts commercialement raisonnables » pour prendre en charge les produits logiciels exécutés sur des machines virtuelles VMware. Si cela ne permet pas d’isoler le problème, Microsoft peut demander aux clients de reproduire le problème sur une machine physique pour poursuivre l’analyse.
"

Nous avons virtualiser pour séparer les les roles DNS/ TSE / Applicatif /BDD. Vous conseillez donc une serveur physique pour la BDD.

La virtualisation ajoute une couche de complexité et ne solutionne aucun problème spécifique à SQL Server (la haute dispos est mieux gérée en interne par SQL Server que via un VM..); De plus comme tout bon SGBDR SQL Server est doté d'un OS interne qui "ignore" Windows, sauf au niveau des accès réseau... La virtualisation contrarie donc le fonctionnement de SQL Server et empêche de poser des diagnostics précis.
Voici ce que je dis dans mon livre sur SQL Server :

"
13.2.2 - Machine virtuelle

Comme nous l’avons mentionné au paragraphe 13.1.3 (SQL Operating System - SOS), les couches supplémentaires apportées par la virtualisation (VMWare, Hyper V...) ne font qu’apporter du bruit, donc une perte naturelle de performances (au mieux 2% de pertes mais souvent beaucoup plus, de l’ordre de 8 à 15 % !)... Mais ce n’est pas tout :
• Microsoft n’offre aucune garantie que SQL Server fonctionne correctement dans un système virtualisé (y compris sur Hyper V) et recommande de reproduire les dysfonctionnements sur une machine physique en cas d’appel à la hot line ;
• un grand nombre de compteurs de performances n’offre plus de données fiables (toutes les métriques incorporant le temps, prennent en compte le temps réel ce qui inclut les temps de gestion de la VM, et les temps d’utilisation des autres machines, si plusieurs VM figurent sur la même machine...) ;
• le diagnostic des problèmes de performance devient très difficile à établir car il n’est pas facile de savoir sur quelle couche investiguer et comment ses différentes couches interagissent ;
• la sauvegarde des VM n’est pas fiable pour les bases SQL Server, du fait des opérations d’écriture asynchrones et non sérialisées, sauf à utiliser un outil supplémentaire (VSS par exemple) qui « gèle » les bases le temps de procéder à la copie des fichiers, ce qui rend indisponible la pleine utilisation des bases le temps de procéder au snapshot de la VM ;
• le coût de licence entre l’OS, la VM et SQL Server devient supérieur à celui d’une machine physique sans VM lorsque la solution porte sur des volumétries importantes (plus de 100 utilisateurs, plus de 300 Go de bases de données, plus de 8 CPU, plus de 64 Go de RAM...).

Il existe cependant quelques avantages à utiliser une VM :
• pour mutualiser sur une même machine physique performante de nombreuses petites instances (hébergeant des bases de moins de 10 Go, avec peu d’utilisateurs) ;
• la facilité de gestion de la haute disponibilité pour des ingénieurs systèmes n’ayant aucune connaissance de l’administration de serveurs SQL.
Notons cependant qu’il existe d’autres moyens plus fiables, plus efficaces et moins couteux en ressources pour assurer la haute disponibilité comme pour assurer la reprise du services des données en cas d’incident majeur (« log shipping », réplication des données, mise en miroir des bases, instances en « cluster », AlwaysOn...)

En dehors de ces cas de figure, si vous êtes condamné à utiliser une VM, alors, fixez-vous les objectifs suivants :
• désactivez le « ballooning » ;
• faite du stockage « pass-through » sur un système dédié;
"

J'en revient à ma question initiale, car en attendant de diagnostiquer le problème, je dois le prévenir. Si je ne dois pas redémarrer le serveur, comment prévenir la panne?

Il faut investiguer avec les outils pour ce faire, notamment les DMV, le collecteur de données et les extended events. Et lorsque le problème survient utiliser des requêtes en ligne de commande via sqlcmd.exe en mode DAC.

Par analogie, arrêter le serveur c'est comme supprimer un organe malade d'un patient atteint de maladie plutôt que de tenter de le soigner. Les DMV permettent de remonter des problèmes passés et il faut en surveiller les données pour savoir ce qui ne vas pas. Pour cela il faut préalablement savoir quelles sont les métriques lorsque cela va bien et savoir quel sont les standard des métriques d'un serveur en bon état de marche, d’où une formation admin absolument nécessaire !

A +

[SQLpro]

"optimize for adhoc workloads" n'est pas active. Je vais l'activer. Je peux faire ça à chaud?

NON SURTOUT PAS !!!! Lisez la doc avant de mettre en œuvre une option, vous avez une aide en ligne et MS est l'une des plus riche !!!
C'est pas compliqué de lire la doc !!!!
Extrait :

"
L'option Optimiser pour les charges de travail ad hoc permet d'améliorer l'efficacité du cache du plan pour les charges de travail qui contiennent de nombreux lots ad hoc à usage unique
"
Dans votre cas vous indiquez que vous n'avez que des proc. Donc pas de requête ad hoc... Donc cette option sera catastrophique !

A +

[KyoshiroKensei]

merci j'avai lu la doc. J'ai bien vu que ce n’était pas d'utilité avec les procédures stockées. C'est pour ça que je pose la question sur la pertinence de cette option point de vue performance. Par contre j'ai vu dans les plans en cache un grand nombre de plan utilisé une seule fois, pour un total en mémoire de 1,6 giga. Du coup j'ai modifié mon post dans le sens de rudib.

Par contre, je me suis mal exprimé, je ne l'ai pas activé car pas de modif de ce type en semaine. Mais lors du prochain arrêt technique, je pensais l'essayer. Le système est VMWare et le balloning n'est pas activé. Coté infra, je ne peut pas agir sur l'existant par contre je préconiserai une machine physique à l'avenir.

Du coup vous en pensez quoi?

[SQLpro]

merci j'ai lu la doc. J'ai bien vu que ce n’était pas d'utilité avec les procédures stockées. C'est pour ça que je pose la question sur la pertinence de cette option point de vue performance. Par contre j'ai vu dans les plans en cache un grand nombre de plan utilisé une seule fois, pour un total en mémoire de 1,6 giga. Du coup j'ai modifié mon post dans le sens de rudib.

Par contre, je me suis mal exprimé, je ne l'ai pas activé car pas de modif de ce type en semaine. Mais lors du prochain arrêt technique, je pensais l'essayer. Le système est VMWare et le balloning n'est pas activé. Coté infra, je ne peut pas agir sur l'existant par contre je préconiserai une machine physique à l'avenir.

Du coup vous en pensez quoi?

1) activez le collecteur de données (mais prévoyez de la place sur vos disques)
2) préparez des requêtes systèmes telle que celles de rudi sous forme de fichiers SQL et entrainez vous avec sqlcmd.exe en mode DAC à les lancer
3) lancez ces requêtes maintenant et alimentez une base de données avec les résultats (avec la date) afin de savoir de quoi vous partez et faire des comparaions
4) si le problème survient, ne changez rien au comportement du système :
4.1) investiguez avec les requêtes en mode DAC
4.2) faites une sauvegarde de la base collector

Si vous n'arrivez pas à trouver ce qui cloche, relancez le serveur, puis utilisez la base collector pour voir ce qui s'est passé en la restaurant sur votre poste en local.

Vous pouvez aussi utiliser PAL qui est un bon outil de diagnostic... mais là encore, sans formation, ça vas pas être coton !
http://pal.codeplex.com/

A +