Rudi Bruchez
Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
LIVRES : Optimiser SQL Server -
Microsoft SQL Server 2012 Security Cookbook - les bases de données NoSQL
e-learning : LinkedIn Learning - Pluralsight
Que donne 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 SELECT 'total' as [type], cacheobjtype, objtype, SUM(CAST(size_in_bytes as bigint)) / 1024 / 1014 as size_in_mb FROM sys.dm_exec_cached_plans GROUP BY cacheobjtype, objtype UNION ALL SELECT '1x', cacheobjtype, objtype, SUM(CAST(size_in_bytes as bigint)) / 1024 / 1014 as size_in_mb FROM sys.dm_exec_cached_plans WHERE usecounts = 1 GROUP BY cacheobjtype, objtype ORDER BY cacheobjtype, objtype, [type]
Rudi Bruchez
Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
LIVRES : Optimiser SQL Server -
Microsoft SQL Server 2012 Security Cookbook - les bases de données NoSQL
e-learning : LinkedIn Learning - Pluralsight
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Rudi Bruchez
Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
LIVRES : Optimiser SQL Server -
Microsoft SQL Server 2012 Security Cookbook - les bases de données NoSQL
e-learning : LinkedIn Learning - Pluralsight
Oui, mais lorsque que n'a plus de mémoire disponible, le cache des plans diminue le premier avec cette option.
A +
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Tu as une référence là-dessus ? C'est assez imprécis, je n'ai jamais entendu parler de ça, et je ne vois toujours pas où il y a un problème. S'il n'y a plus de mémoire disponible, c'est une bonne idée de réduire le cache de plans. De toute manière c'est ce que fait SQL Server depuis le 2005 SP2. Référence de Kalen Delaney : http://sqlblog.com/blogs/kalen_delan...-can-have.aspx
En résumé pour ce thread en ce qui me concerne, l'option Optimize for adhoc workload n'a pas de contre-indication médicale, et je me permets de la recommander sans hésitation.The change actually computes a plan cache size at which SQL Server recognizes that there is memory pressure, and it will start removing plans from cache. The plans to be removed are the cheap plans that have not been reused, and this is a GOOD THING
Autre opinion (de Glen Berry) sur le sujet : http://www.sqlservercentral.com/blog...tion-settings/
In my opinion, you should always enable this setting on SQL Server 2008 and above. I really cannot think of a good reason not to do this.
Rudi Bruchez
Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
LIVRES : Optimiser SQL Server -
Microsoft SQL Server 2012 Security Cookbook - les bases de données NoSQL
e-learning : LinkedIn Learning - Pluralsight
Revenons aux basiques ...
Que disent les event log Windows et errolog SQL au moment des plantages.
Pour ceux qui sont déjà passés, est-ce que ces plantages sont intervenus à des horaires spécifiques ? jour / nuit / forte charge ? Estc-e que cela arrive tout le temps au même heures "après 22 jours ..."
Christophe
Christophe LAPORTE | Independent Consultant & Trainer
SQL Server Certified Master | Azure Solution Architect
Bonjour,
Réponse pour rudib:
type cacheobjtype objtype size_in_mb
1x Compiled Plan Adhoc 322
total Compiled Plan Adhoc 784
1x Compiled Plan Prepared 878
total Compiled Plan Prepared 1006
1x Compiled Plan Proc 60
total Compiled Plan Proc 550
1x Compiled Plan Trigger 0
total Compiled Plan Trigger 32
1x Extended Proc Proc 0
total Extended Proc Proc 0
1x Parse Tree Check 0
total Parse Tree Check 0
total Parse Tree UsrTab 0
total Parse Tree View 133
Réponse pour Christophe:
Log SQL: alternance de trace de la commande memory status et messages suivant:
There is insufficient system memory in resource pool 'internal' to run this query.
There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory.
Log Windows : message en permanence
Mémoire système insuffisante dans le pool de ressources 'internal' pour exécuter cette requête.
Une erreur d'allocation de mémoire s'est produite au cours de l'établissement de la connexion. Réduisez la charge de mémoire qui n'est pas essentielle ou augmentez la quantité de mémoire système. La connexion a été fermée
Il n'y a pas dh'eure de plantage particulière. Les derniers plantages ont eu lieu le 20/01/2014 à 08h49, le suivant 16/02/2014 à 01h, le suivant le 11/03/2014 à 09h. Je n'ai pas les heures de précédent plantages, mais le temps entre chaque plantage est de l'ordre de 3 semaines.
Pour l'option, sur les plans en caches, comme dit dans mon précédent post, si je l'active, je peux "potentiellement" évité de consommé 1,6go inutilement. J'ai bien envie de testé cet option lors du prochain plantage (sans doute la semaine prochaine.)
Il n'y a pas plus d'info dans le log d'erreur de SQL Server ? Il me semble qu'il peut y placer aussi des infos sur la mémoire juste après ce genre de message ?
On en revient grosso modo à la même chose : quelque chose te mange la mémoire, soit dans SQL Server, soit à l'extérieur. Donc il faut surveiller en interne et du côté Windows, avec l'idée que cela peut être :
- un processus hors SQL Server qui a une fuite de mémoire
- quelque chose dans SQL Server qui prend de la mémoire, comme des appels à des serveurs liés, des curseurs, des requêtes full text, du code .NET
Je mentionne la requête full text parce qu'il y a un problème connu avec des requêtes qui utilisent des index full text et qui génère exactement le message que tu reçois : http://support.microsoft.com/kb/982854/en-us.
Donc, que faire ?
- Installe le dernier service pack si ce n'est pas fait, il y a eu qq bugs dans SQL Server 2008 qui peuvent déclencher ce message (je ne sais pas pour 2008 R2, mais il faut avoir le dernier SP quand même)
- Mettre un MIN et un MAX server memory dans SQL Server comme je te le disais dans un post précédent. Cela permettra à SQL Server de conserver de la mémoire en cas de pression de la part de Windows (MIN), et de ne pas étrangler Windows (MAX)
- Surveiller les allocations mémoire dans SQL Server, code fourni dans un post précédent
- Surveiller la mémoire des autres processus sur la machine, spécifiquement les octets privés des processus. Si un processus voit son nombre d'octets privés augmenter régulièrement, c'est sans doute le signe d'une fuite de mémoire
Le jour où tu as ton problème de mémoire insuffisante dans SQL Server, ne reboote pas tout de suite, tu prends d'abord ton perfmon et tu regardes quels sont les processus qui utilisent la mémoire, et tu regardes le contenu de la mémoire de SQL Server avec la requête fournie précédemment, au besoin en utilisant la DAC. Ou tu lances un DBCC MEMORYSTATUS.
Dernière requête utile :
Cela permettra de voir si la mémoire est prise par des choses internes à SQL Server, ou des choses externes (comme du code .NET dans SQL Server)
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 SELECT TYPE, SUM(single_pages_kb) as single_page, SUM(multi_pages_kb) as multi_page FROM sys.dm_os_memory_clerks GROUP BY TYPE ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESC;
Ce que tu peux faire aussi est de lancer la requête, et DBCC MEMORYSTATUS, un jour avant la date fatidique prévue, pour voir où tu en es. Soit ça vient d'un coup avec un problème ce jour-là, soit petit à petit.
tu peu activer l'option Optimize for adhoc workloads, mais c'est une option d'optimisation de la mémoire, pas de résolution de ton problème. Cette option ne permettra pas de résoudre ton problème.
Rudi Bruchez
Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
LIVRES : Optimiser SQL Server -
Microsoft SQL Server 2012 Security Cookbook - les bases de données NoSQL
e-learning : LinkedIn Learning - Pluralsight
merci, je vais suivre ton conseil.
Par contre, avec la requête fournie, on voit que les plans sont peu réutilisés, donc l'option est valable à mon sens pour mieux utiliser la mémoire. J'ai bien compris que cela ne va pas résoudre le problème.
J'avais oublier de le précisé, mais lorsque ce problème survient, dans le gestionnaire des taches windows, , sql est à 7 gigo consommé pour 8 disponible. C'est pourquoi je ne pense pas qu'un processus extérieur soit concerné.
J'ai également suivi ton 2ième conseil. J'ai référencé les gros consommateurs de mémoire: les procédures stockées avec curseurs (ça va il y en a peu). J'ai déjà identifié certaines d'entre elles comme étant trop utilisées. J'ai donc passé ces procédures en agent sql. (exemple: 100 appli clientes lance toutes les minutes la requête avec curseur, remplacé par un agent qui calcul pour tout le monde et met à jour une table que les appli clientes lisent.)
Pas de requêtes full text, pas de code .net, 5 requêtes en serveur lié, mais je ne peux faire autrement.
Par contre depuis 1 an, nous utilisons les triggers. Est ce consommateur de mémoire?
Dans les logs sql, non, j'ai ces 2 messages en boucles + dbcc memorystatus. Je peux fournir un imprim écran si tu veux.
Pas de printscreen, mais si tu as un résultat de DBCC MEMORYSTATUS dans le log, attache un fichier texte avec le contenu de cette partie (ça doit être long)
Rudi Bruchez
Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
LIVRES : Optimiser SQL Server -
Microsoft SQL Server 2012 Security Cookbook - les bases de données NoSQL
e-learning : LinkedIn Learning - Pluralsight
pufff c lourd d'être déconnecté tout le temps du forum....
en pièce jointe les log de la journée du dernière plantage
je joins le résultat du DBCC MEMORYSTATUS formaté. Je vais jeter un oeil
pb memoire.formaté.txt.zip
Rudi Bruchez
Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
LIVRES : Optimiser SQL Server -
Microsoft SQL Server 2012 Security Cookbook - les bases de données NoSQL
e-learning : LinkedIn Learning - Pluralsight
Après lecture de tout le thread, il y a déjà pas mal de choses de faites.
J'ai regardé rapidement le résultat de DBCC MEMORYSTATUS et je ne vois de valeur choquante mais bon rudib aura peut être pris plus le temps pour regarder en détail.
Par exemple on ne voit pas de choses vraiment anormal avec System physical memory high = 1 mais c'est un instantané.
Est-ce que tu pourrais éventuellement nous donné le résultat de cette requête après un "plantage" :
Je t'invite également à mettre des compteurs de performances Windows avec les objets SQL Server concernant l'utilisation de la mémoire pour voir si quelque chose d'évident en ressort pendant que tout fonctionne ou au moment du crash.
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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50 WITH RingBuffer AS ( SELECT CAST(dorb.record AS XML) AS xRecord, dorb.timestamp FROM sys.dm_os_ring_buffers AS dorb WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' ) SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification, xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess, xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem, DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) AS RmDateTime FROM RingBuffer AS rb CROSS APPLY rb.xRecord.nodes('Record') record (xr) CROSS JOIN sys.dm_os_sys_info AS dosi ORDER BY RmDateTime DESC; WITH RingBuffer AS ( SELECT CAST(dorb.record AS XML) AS xRecord, dorb.timestamp FROM sys.dm_os_ring_buffers AS dorb WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' ) SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification, xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess, xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem, DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) AS RmDateTime, xr.value('(MemoryNode/TargetMemory)[1]','bigint') AS TargetMemory, xr.value('(MemoryNode/ReserveMemory)[1]','bigint') AS ReserveMemory, xr.value('(MemoryNode/CommittedMemory)[1]','bigint') AS CommitedMemory, xr.value('(MemoryNode/SharedMemory)[1]','bigint') AS SharedMemory, xr.value('(MemoryNode/PagesMemory)[1]','bigint') AS PagesMemory, xr.value('(MemoryRecord/MemoryUtilization)[1]','bigint') AS MemoryUtilization, xr.value('(MemoryRecord/TotalPhysicalMemory)[1]','bigint') AS TotalPhysicalMemory, xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]','bigint') AS AvailablePhysicalMemory, xr.value('(MemoryRecord/TotalPageFile)[1]','bigint') AS TotalPageFile, xr.value('(MemoryRecord/AvailablePageFile)[1]','bigint') AS AvailablePageFile, xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]','bigint') AS TotalVirtualAddressSpace, xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]','bigint') AS AvailableVirtualAddressSpace, xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]','bigint') AS AvailableExtendedVirtualAddressSpace FROM RingBuffer AS rb CROSS APPLY rb.xRecord.nodes('Record') record (xr) CROSS JOIN sys.dm_os_sys_info AS dosi ORDER BY RmDateTime DESC;
Il se peut effectivement que cela vienne de plus haut dans la couche virtuelle. Quel hyperviseur utilises-tu ?
++
Effectivement, autant que je puisse en juger, je ne vois pas de signe évident de problème. L'investigation continue, mais il sera bon que tu sois là au moment où cela se produit. Si tu dois redémarrer, conserve aussi la trace par défaut (les fichiers .trc dans le répertoire de log de SQL Server)
Rudi Bruchez
Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
LIVRES : Optimiser SQL Server -
Microsoft SQL Server 2012 Security Cookbook - les bases de données NoSQL
e-learning : LinkedIn Learning - Pluralsight
ok je vais essayé d'enregistrer un max d'informations lors du plantage et d’effectuer la requête fournie après plantage. Cela ne devrait pas tarder vu que je frôle les 7 giga consommés par sql aujourd'hui.
Je vous remercie pour le temps passé sur les logs. "Dommage" qu'ils ne contiennent pas une énorme bourde..
J'utilise esxi v5.1.0
Petite question à rudib: dans votre livre Optimiser SQL Server, est ce que vous expliquer "comment " interpréter les données de fonctionnement fournis par sql?
A chaque fois que je met les mains dans sql, je suis confronté aux même interrogations: comment interprété les données mémoire / cache / temps etc et quelles conclusions en tirer. Qu'est ce qui est normal et qu'est ce qui ne l'est pas? De formation analyste développeur au départ, je fais de plus en plus d'administration de BDD. BIen qu'ayant suivi des formations, je n'est jamais trouvé de réponse. Votre livre contient il des éléments de réponse? (il semble plébiscité par les lecteurs en tout cas sur "mes" sites habituels).
J'essaie autant que possible d'expliquer ces choses, j'avais envie de faire un livre un peu avancé sur le sujet, il y a dans le livre pas mal d'infos sur les indications données par SQL Server et sur son fonctionnement interne. J'ai aussi monté dernièrement une formation de diagnostic et résolution de problèmes, mais je pense que le bouquin devrait déjà te donner pas mal de pistes.
Ensuite,Pour aller encore plus loin il y a des livres en anglais (genre SQL Server internals ...) et des formations en vidéo sur Pluralsight. Si tu comprends l'anglais je te recommande les formations en vidéo chez Pluralsight.
Par rapport à ce que tu dis : c'est normal que SQL Server prenne 7 Go, il consomme toute la mémoire qu'on lui alloue pour ses caches et ne la rend jamais. C'est une bonne chose : il l'utilise à bon escient. Par contre c'est important en 64 bit de le limiter avec le max server memory, comme je l'avais mis dans une des premières réponses.
Rudi Bruchez
Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
LIVRES : Optimiser SQL Server -
Microsoft SQL Server 2012 Security Cookbook - les bases de données NoSQL
e-learning : LinkedIn Learning - Pluralsight
oui pour les 7 gigas. Je savais que c'etait normal pour les raisons que tu mentionnes. Ce que je voulais dire c'est que j'approche du point de plantage au niveau de la consommation mémoire
Suite au plantage la semaine prochaine, je limiterai la taille mémoire comme indiqué.
1) mettez en place le collecteur de données
2) il se peut que cette fuite mémoire soit la conséquences de données verrouillés avec une consommation transactionnelle du genre snapshot
Investiguez donc sur les blocage et les transactions ouvertes.
Faites par exemple un DBCC OPENTRAN sur toutes vos bases toutes les 5 minutes et stockez ça dans une table
Je sais y'en a qui vont crier, mais j'aime bien !!!
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10 CREATE TABLE msdb.dbo.TRANLOG (TL_ID INT IDENTITY PRIMARY KEY, TL_DATEHEURE DATETIME DEFAULT GETDATE(), TL_KEY sysname, TL_DATA VARCHAR(1000)); GO INSERT INTO msdb.dbo.TRANLOG (TL_KEY, TL_DATA) EXEC ('DBCC OPENTRAN WITH TABLERESULTS ;') GO
A +
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Bonjour,
J'ai profité d'une baisse d'activité ce matin pour configurer la mémoire min à 3go, la memoire max à 7go. Je vous tiens au courant.
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager