Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 6 sur 6
  1. #1
    Invité régulier
    Inscrit en
    mai 2005
    Messages
    44
    Détails du profil
    Informations forums :
    Inscription : mai 2005
    Messages : 44
    Points : 6
    Points
    6

    Par défaut sqlservr.exe pic 100% quotidien

    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

  2. #2
    Membre Expert
    Profil pro David BAFFALEUF
    Inscrit en
    février 2008
    Messages
    757
    Détails du profil
    Informations personnelles :
    Nom : David BAFFALEUF
    Localisation : France

    Informations forums :
    Inscription : février 2008
    Messages : 757
    Points : 1 066
    Points
    1 066

    Par défaut

    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
    David B.

  3. #3
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 563
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 563
    Points : 30 071
    Points
    30 071

    Par défaut

    Citation Envoyé par wadcyr8_197 Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  4. #4
    Expert Confirmé Sénior
    Avatar de mikedavem
    Homme Profil pro David BARBARIN
    Architecte de base de données
    Inscrit en
    août 2005
    Messages
    4 613
    Détails du profil
    Informations personnelles :
    Nom : Homme David BARBARIN
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : août 2005
    Messages : 4 613
    Points : 9 943
    Points
    9 943

    Par défaut

    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.

    ++

  5. #5
    Invité régulier
    Inscrit en
    mai 2005
    Messages
    44
    Détails du profil
    Informations forums :
    Inscription : mai 2005
    Messages : 44
    Points : 6
    Points
    6

    Par défaut

    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...

  6. #6
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 563
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 563
    Points : 30 071
    Points
    30 071

    Par défaut

    Citation Envoyé par wadcyr8_197 Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •