sqlservr.exe pic 100% quotidien

[wadcyr8_197]

Bonjour,

N'étant pas DBA et peu familiarisé avec SQL Serveur, je vous remercie de votre indulgence. (désolé, mais les bases c'est déjà trop high level pour moi )

Je vous expose le problème :
Alerté par des utilisateurs rencontrant depuis plusieurs jours un ralentissement violent de leur application à partir de 11h et pendant 20 bonne minutes.

en regardant les différents système, nous avons constaté (via task manager) un pic CPU à 100% correspondant à cette période et dû au processus sqlservr.exe.

Je cherche donc à savoir ce qui peut provoquer ce pic CPU chaque jour.

pour décrire le système :
serveur bases de données mutualisées
VM , Win2008R2, SQL Server 2008 R1 (actuellement à 4vCPU, 6Go de ram, à 2vCPU et 4Go de ram au début du problème, on teste ce qu'on peut)
SQL Server 2008 R2.

il y a 7 instances sur le serveur, des petites bases assez peu sollicitées

auriez vous des idées sur comment trouver ce qui provoque ce pic ?

Pour info, j'ai un job de maintenance log qui se déclenche sur toutes les instances, mais comme il a lieu toutes les heures à heure fixe, je ne pense pas que cela vienne de lui...

je suis déjà allé voir les view sys.dm_exec_query_stats et sys.dm_exec_requests mais je n'ai rien trouvé qui tombe dans ces horaires.

quelqu'un aurait des idées ? peut être aussi sur comment collecter un maximum de données demain lors de la prochaine itération du problème...

MErci d'avance

[dbaffaleuf]

Au tout début du pic faire:

Code :

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

Pendant le problème faire:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
    owt.session_id,
    owt.wait_duration_ms,
    owt.wait_type,
    owt.blocking_session_id,
    owt.resource_description,
    es.program_name,
    est.text,
    est.dbid,
    eqp.query_plan,
    es.cpu_time,
    es.memory_usage
FROM sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es
    ON owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
    ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan (er.plan_handle) eqp
WHERE es.is_user_process = 1;
GO

A la fin du problème, faire:

Code :

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
WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    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', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
    )
SELECT
    W1.wait_type AS WaitType,
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
    CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
    CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
    W1.WaitCount AS WaitCount,
    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
    CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
    CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold;
GO

et aussi:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT top 20 S.text 'SQLtext', STAT.execution_count 'Plan reuse (total executions)', STAT.plan_generation_num 'Plans generations', 
STAT.creation_time 'Last compile time',STAT.last_execution_time 'Last execution time',
STAT.total_worker_time/1000 'Total CPU time (s)', STAT.total_worker_time/1000/STAT.execution_count 'CPU time/exec (s)',
STAT.total_elapsed_time/1000 'Total Elapsed (s)',  STAT.total_elapsed_time/1000/STAT.execution_count 'Total Elapsed/exec (s)',
STAT.total_logical_reads 'Total Logical Reads', STAT.total_logical_reads/STAT.execution_count 'Logical Reads/exec', 
STAT.total_logical_writes 'Total Logical Writes', STAT.total_logical_writes/STAT.execution_count 'Logical Writes/exec', 
P.query_plan 'Last query Plan'
FROM sys.dm_exec_query_stats STAT WITH (NOLOCK)
CROSS apply sys.dm_exec_sql_text(STAT.sql_handle) S
CROSS apply sys.dm_exec_query_plan(STAT.plan_handle) P
ORDER BY STAT.total_worker_time/1000 DESC;
GO

et nous poster tout ça

[SQLpro]

Envoyé par wadcyr8_197

pour décrire le système :
serveur bases de données mutualisées
VM , Win2008R2, SQL Server 2008 R1 (actuellement à 4vCPU, 6Go de ram, à 2vCPU et 4Go de ram au début du problème, on teste ce qu'on peut)
SQL Server 2008 R2.

il y a 7 instances sur le serveur, des petites bases assez peu sollicitées

À coté des requêtes fort intéressantes de david, je dirais ceci :

1) 7 instances sur une même machine en production c'est stupide. Chaque instance se concurrence pour les ressources CPU / RAM et disque et il est possible d'arriver à une quasi famine de ressources d'une instance envers l'autre, sauf si vous avez sciemment paramétré chaque instance pour partager les CPU, la RAM et dans une moindre mesure les disques afin qu'aucune instance ne se marche sur les pieds...

2) la virtualisation de SQL Server pose de multiples problèmes, car avant tout un SGBDR est un OS indépendant de l'OS qui l'héberge.... Dans SQL Server l'OS interne s’appelle SOS (SQL Server OS) mais dans le cas qui vous préoccupe, ce SOS est une bouteille lancée à la mer... Lisez l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p8...virtualisation

3) en 64 bits, le système (Windows) peut devenir naturellement instable, si vous n'avez pas spécifiez à chaque instance de ne pas tout prendre. En effet chaque instance accapare la RAM, même au détriment de l'OS et ce dernier peut être en état de stress et devoir demander à SQL Server de restituer de la RAM... Avec 7 instances, c'est du pur suicide !
Il faut donc restreindre chacune des instances au niveau de la RAM et des autres de façons a ce que l'OS ait en permanence au moins 2 Go de RAM...

4) il faut aussi interdire le ballooning de la VM et même faire du disk pass through autant que possible. De plus sauvegarder une VM ne garantie pas la reprise de SQL Server en cas de restauration sauf à utiliser au moins VSS qui "freeze" les bases le temps du snapshot.

5) utiliser 7 instances pour quelques bases c'est hautement stupide, car chaque instance consomme des ressources non négligeable (RAM, process de tâches de fond, accès disques synchrones pour la journalisation) qu'une seule aurait parfaitement éviter.
Il est tout à fait déconseillé d'utiliser plusieurs instances en production.... comme de créer plusieurs bases pour une même application.

Bref, votre architecture est un excellent exemple de tout ce qu'il faut faire pour planter un système même en ayant une activité quasi nulle !!!

A +

[mikedavem]

en regardant les différents système, nous avons constaté (via task manager) un pic CPU à 100% correspondant à cette période et dû au processus sqlservr.exe.

En milieu virtualisé il faut toujours se méfier de ce que l'on voit côté OS. Tu peux avoir 100% CPU à un moment donné mais 100% de quoi ? Quelle capacité CPU possédait le serveur virtuel à ce moment ?

En plus de ce que donne David, vous pouvez aussi jouer avec les traces côtés serveur et les compteurs de performances (CPU, disque, RAM ...) . Vous pourrez ainsi corréler les pics CPU avec les requêtes de ta base. J'ajouterai à tout cela les compteurs de performance niveau ESX.

++

[wadcyr8_197]

Pour répondre à Frédéric, ton avis est très tranché, j'ai aussi vu des infras où MS et d'autres experts SQL Server avaient recommandé la virtualisation dans des proportions bien plus grande que celles-ci. Personnellement, ce n'est pas trop mon rayon donc je ne porte pas de jugement.

Je ne suis pas encore en mesure de savoir si les quelques précautions que tu cites sont mises en place. Mais si je suis ton raisonnement, il faudrait avoir un serveur physique dédié par application/instance. Pour une DSI avec des moyens limité ce n'est pas forcément évident.

Perso, je débarque, donc je vais attendre un peu avant de tout remettre en cause...


A l'autre David, oui je sais que la vision task manager d'une VM est toute relative, mais comme je n'ai que ceci, j'essaie d'éliminer les causes progressivement. Par ailleurs, je n'ai pas vraiment relevé de pic/saturation côté hôte...

[SQLpro]

Envoyé par wadcyr8_197

Pour répondre à Frédéric, ton avis est très tranché, j'ai aussi vu des infras où MS et d'autres experts SQL Server avaient recommandé la virtualisation dans des proportions bien plus grande que celles-ci. Personnellement, ce n'est pas trop mon rayon donc je ne porte pas

Aucun expert ne vous recommandera la virtualisation pour des raisons de performances !
Or vous postez pour des problèmes de performances.
L'intérêt de la virtualisation réside dans deux axes :
1) faire des économies de licences (MS ne fait payer qu'au CPU ou core physique)
2) mutualiser des ressources pour des petites bases / serveurs, les éditeurs de logiciels ayant tendance à exiger d'avoir une instance propre pour leurs applications alors qu'il est facile de mutualiser plusieurs bases sur un même serveur, à condition que les applications ait été un tant soit peu bien écrites...

De toute façon, déjà la quantité de RAM accordé pour 7 instances en sus de l'OS est anormalement trop faible. Dans un tel cas il faudrait au minimum 18 Go de RAM (2 Go par instance + 4 pour l'OS) et fixer la ram à 2 Go pour chaque instance....

A +