Attente de ressource Memoire long

**chloeb** · 10/05/2013, 01h33

Bonjour,

Voici mon problème... Depuis quelques temps, je remarque que mes sites se bloquent sur des pages ayant des événements qui nécessitent un rechargement de la page avec une nouvelle connexion sql (par exemple le changement de page d'un catalogue). La page ne repond plus et il faut attendre un certains temps avant de pouvoir faire une action sur la page qui bloque. (j'espère que je me suis fait comprendre!!!)

J'ai lancé l'outil "Moniteur d'activité" sur SQL Management studio et j'observe que quand une page bloque, dans la partie "Attentes de ressources", j'ai "Memory" qui grimpe en flèche au niveau du temps d'attente (sans avoir de requête coûteuse qui s'affiche). Quand enfin le temps d'attente revient à 0, mon site se débloque.

Comment éradiquer ce phénomène et à quoi ceci est dû?

Merci pour votre aide!

**dbaffaleuf** · 10/05/2013, 10h30

Avant l'exécution de la tentative de connexion, lancer la commande ci-dessous:

Code :

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

1
2
dbcc sqlperf('sys.dm_os_wait_stats','clear');
GO

Faire un test de connexion et une fois le test terminé lancer la commande ci-dessous:

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
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
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', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH'))
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE);
GO

Et poster le résultat

thx

**chloeb** · 10/05/2013, 11h16

Voici le résultat que j'obtient

wait_type wait_time_s pct running_pct
RESOURCE_SEMAPHORE 827.31 95.35 95.35
SOS_SCHEDULER_YIELD 14.49 1.67 97.02
SQLTRACE_FILE_WRITE_IO_COMPLETION 10.71 1.23 98.26
OLEDB 3.04 0.35 98.61
WRITELOG 1.75 0.20 98.81
PREEMPTIVE_XE_CALLBACKEXECUTE 1.72 0.20 99.01

**dbaffaleuf** · 10/05/2013, 15h42

OK, c'est un problème de mémoire nécessaire lors de la compilation d'une requête.

Il faudrait pour identifier clairement quelle est la requête en question, lancer deux requêtes pendant l'établissement de la connexion (pas à la fin mais bien pendant l'attente):

1) -- Déterminer les conditions d'attente (coût du plan, temps d'attente, type de gateway, etc...)

Code :

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

1
2
select * from sys.dm_exec_query_memory_grants;
GO

2) -- Déterminer le texte et le plan de la requête en attente

Code :

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

1
2
3
4
5
6
7
8
select SQ.text ,WTA.*,  QP.query_plan from sys.dm_os_waiting_tasks WTA
join sys.dm_exec_sessions SES on SES.session_id = WTA.session_id
join sys.dm_exec_requests REQ on REQ.session_id = SES.session_id
cross apply sys.dm_exec_sql_text(REQ.sql_handle) SQ
cross apply sys.dm_exec_query_plan(REQ.plan_handle) QP
where SES.is_user_process=1
and WTA.wait_type = 'RESOURCE_SEMAPHORE';
GO

**CherifIB** · 10/05/2013, 15h46

Bonjour,
essaye d'identifier et examiner les requêtes qui attendent une allocation de mémoire :

Code :

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

1
2
3
4
5
SELECT text,query_plan, requested_memory_kb, granted_memory_kb, used_memory_kb 
FROM 
SYS.DM_EXEC_QUERY_MEMORY_GRANTS mgr
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sql_handle) txt
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(mgr.plan_handle) qp

**chloeb** · 10/05/2013, 16h00

Je n'ai aucun résultat avec les requêtes que vous m'avez donné...

J'ai du mal à comprendre car quand le site bloque bien souvent il n'y a aucune requête coûteuse en attente (sur activity monitor).

**chloeb** · 10/05/2013, 17h10

Je viens de récupérer une erreur sql quand le site a bloqué :

erreur CatalogueInfusion exp : System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.WriteSni()
at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
at System.Data.SqlClient.TdsParser.TdsExecuteSQLBatch(String text, Int32 timeout, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()

Je sais pas si ça peux vous aider à comprendre ce qui se passe!

**dbaffaleuf** · 10/05/2013, 18h27

Peux-tu mettre en PJ le contenu de l'errorlog stp

**chloeb** · 10/05/2013, 19h03

en pièce jointe le fichier errorlog

**mikedavem** · 10/05/2013, 20h20

Visiblement tu as un problème de pression mémoire sur ton serveur au vu des messages suivants :

spid1s,Unknown,A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1519 seconds. Working set (KB): 158752<c/> committed (KB): 369296<c/> memory utilization: 42%.

Peux-tu nous donner plus de précision concernant ton environnement ?

- Quantité de mémoire sur le serveur ?
- Quantité allouée à SQL Server ?
- Quantité de mémoire libre sur le serveur ?
- As-tu mis en place une surveillance des compteurs de performance instance:Buffer Manager:Buffer Cache hit ratio et instance:Buffer Manager

age Life Expectancy ?
- Est-ce que tu es concernée par un serveur SQL virtualisé ?

++

**chloeb** · 10/05/2013, 23h05

Alors c'est un serveur dédié 1&1 : Windows Web Server 2008R2 Service Pack 1
RAM : 4,00GB

Commande DBCC MEMORYSTATUS :
Memory Manager KB
VM Reserved 4238360
VM Committed 1103296
Locked Pages Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

Physical Memory
Available 1875MB
Cached 1869MB
Total 4062MB
Installed 4096MB

Dites moi si vous voulez plus d'infos!

**chloeb** · 11/05/2013, 11h32

Autre chose :

J'ai remarqué depuis quelque temps en observant les performance que l'utilisation de la mémoire physique était de 86%. J'ai donc installé l'outil RAMMAP et j'ai "réinitialisé" la mémoire. Depuis je le fais de temps en temps. Mais si je ne le fais pas au fur et à mesure le pourcentage de mémoire physique utilisé augmente de jour en jour!

Merci beaucoup pour votre aide!!!

**SQLpro** · 12/05/2013, 17h49

4 go c'est très peu.
Est-ce du 32 ou du 64 bits ?

A +

**chloeb** · 12/05/2013, 18h16

ok je vais essayer de voir pour augmenter la ram...

C'est du 64bits

**dbaffaleuf** · 13/05/2013, 10h34

2 choses à faire en 64 bits:

- Mettre une valeur explicite à 'max server memory'
- Verrouiller les pages en mémoire (si j'ai bien compris c'est une machine physique et pas une VM)

SQL Server édition Enterprise ou Standard ?
Si Standard alors il faut placer un traceflag (-T845) au démarrage de SQL Server pour pouvoir verrouiller les pages en mémoire cf http://support.microsoft.com/kb/970070/en-us

**aieeeuuuuu** · 13/05/2013, 11h40

Envoyé par chloeb

le pourcentage de mémoire physique utilisé augmente de jour en jour!

Ceci est tout à fait normal. Lorsque SQL Server accède a des données sur le disque, il les place en RAM. Une fois la requête terminée, il laisse les données en RAM, afin qu'elle soit plus rapidement accessible pour les éventuelles requêtes suivantes qui aurait besoin de ces données.
Si d'autres données sont nécessaires, SQL server utilisera plus de RAM.
Ce n'est que lorsqu'il n'y a plus de RAM disponible que SQL server libérera la place utilisée par les données les plus anciennement utilisées...
En d'autres termes, SQL Server utilise toute la RAM dont il peut disposer.

**chloeb** · 13/05/2013, 22h21

Ok, j'ai fait tout ça.

Du coup, j'ai mis en max server memory 2,5GB (mon serveur ayant une RAM de 4GB). Je sais pas si j'ai bien fait ou si je devrais mettre plus ou moins ??

**dbaffaleuf** · 14/05/2013, 10h08

Il faut mesurer si c'est suffisant ou non. Avec 4Gb seulement, il faut étudier la valeur du compteur 'SQLServer:BufferManager : Page life expectancy' (valeur (sec) sous les 300 secs, et variation)

Code :

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

1
2
3
4
select object_name, counter_name, cntr_value 
from sys.dm_os_performance_counters 
where counter_name = 'Page Life Expectancy'
and object_name = 'SQLServer:Buffer Manager'

Et pour éviter que la mémoire de SQL Server ne soit paginée, il faut la verrouiller en mémoire (d'où ma question Standard ou Enterprise Edition ?)