IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration SQL Server Discussion :

sqlservr.exe pic 100% quotidien


Sujet :

Administration SQL Server

  1. #1
    Membre à l'essai
    Inscrit en
    Mai 2005
    Messages
    44
    Détails du profil
    Informations forums :
    Inscription : Mai 2005
    Messages : 44
    Points : 13
    Points
    13
    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 éprouvé
    Profil pro
    Inscrit en
    Février 2008
    Messages
    758
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2008
    Messages : 758
    Points : 1 069
    Points
    1 069
    Par défaut
    Au tout début du pic faire:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    dbcc sqlperf('sys.dm_os_wait_stats',clear);
    GO
    Pendant le problème faire:

    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
    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 : 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
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    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
    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/ * * * * *

  4. #4
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Points : 12 891
    Points
    12 891
    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
    Membre à l'essai
    Inscrit en
    Mai 2005
    Messages
    44
    Détails du profil
    Informations forums :
    Inscription : Mai 2005
    Messages : 44
    Points : 13
    Points
    13
    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
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    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
    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/ * * * * *

Discussions similaires

  1. Réponses: 0
    Dernier message: 12/08/2011, 15h13
  2. [WS 2003] A quoi me sert sqlservr.exe ?
    Par jinpol dans le forum Windows Serveur
    Réponses: 0
    Dernier message: 12/01/2010, 15h36
  3. Problème SQLservr.exe à 100% CPU
    Par morientes104 dans le forum Administration
    Réponses: 3
    Dernier message: 06/03/2009, 15h10
  4. [SQL SERVER 8] sqlservr.exe bloqué à plus de 1 Go
    Par aloisio11 dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 04/03/2008, 09h36
  5. svchost.exe prend 100% de la charge du uc
    Par aaron4444 dans le forum Windows XP
    Réponses: 2
    Dernier message: 17/09/2006, 15h40

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo