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 :

Problème de lenteur : Opération "pompier"


Sujet :

Administration SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre Expert

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    Mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2010
    Messages : 1 278
    Par défaut Problème de lenteur : Opération "pompier"
    Pour une opération "pompier" j'ai un serveur dédié SQL Server 2005 SP2.
    OS = W2K3 SERVER R2 (US)
    1 Disque C : espace libre 8 Go
    1 Disque D : espace libre environ 17 Go
    Mémoire = 4Go
    Processeurs = (je ne me souviens plus exactement, je vais le confirmer demain matin)
    Ce serveur SQL dialogue avec une application de gestion de centre d'appel.
    Et depuis quelques jours des lenteurs inacceptables sont constatées par les utilisateurs de l'application... Je passe les détails...[je vous rassure, tout de suite que je n'ai pas participé à l'installation de ce serveur ]

    Après un audit rapide je constate que :

    1.) Les bases systèmes et les bases utilisateurs sont installées sur le même disque C. Les fichiers .mdf et .ldf sont tous sur le même disque C pour toutes les bases

    ce que je pense faire :
    -->déplacer les .mdf des bases utilisateurs sur le disque D

    2.) Le processus sqlserver.exe utilise un mémoire max d'environ 1,7 Go (visible via le Task manager) et dans le boot.ini l'option /3GB n'est pas activé

    ce que je pense faire :
    -->Mettre /3GB dans le boot.ini
    -->Configurer le max memory server size à 3Go côté SQL SERVER
    -->Un redémarrage de l'OS est-il indispensable pour la prise en compte du paramétrage de /3GB dans le boot.ini ?

    3.) L'utilisation CPU oscille entre 98% - 100 % (visible via le Task manager)
    en exécutant cette requête
    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
    SELECT  
     spid 
    ,hostname 
    ,[status] 
    ,blocked 
    ,open_tran 
    ,waittime 
    ,cmd 
    ,memusage 
    ,cpu 
    ,physical_io 
    ,login_time 
    ,last_batch 
    ,loginame 
    ,DB_NAME(DBID) AS [database] 
    ,[program_name] 
    FROM master.sys.sysprocesses 
    WHERE spid > 50 
    AND spid <> @@SPID
    je constate qu'il y a une centaine de processus non blockés (colonnes blocked =0 et open_tran =0), mais qui sont dormant (colonne status = SLEEPING)
    et dont la date de dernière exécution est environ 1 mois (colonne last_batch) sur les bases utilisateurs.Le détails de quelques un de ces processus montre des DELETE tableUtilisateur WHERE date = 'date_ILYA_1mois'

    Je me demande :
    -->Je me demande ce qu'il faut faire de ces processus dormant !!! dormant mais consomme quand même des ressources (memoire,cpu,io) !!!

    4.) trois des principales bases ont l'option AUTO CLOSE à TRUE

    ce que je pense faire :
    --> les mettre à FALSE

    5.) L'option page verify de toutes les bases Utilisateurs sont à TORN_PAGE_DETECTION

    ce que je pense faire :
    --> les mettre à CHECKSUM

    6.) Pour toutes les bases le mode de récupération est à SIMPLE
    --> je ne change pas

    7.) La reverse engineering (à l'aide de Power AMC) d'une des principales base m'a montré :
    --> 13 tables au total pour cette base
    --> la principale table de cette base possède 132 colonnes
    --> le MPD montre que toutes les tables sont indépendantes : aucune jointure entre les tables !!!

    Ce n'est pas le plus urgent actuellemnt mais j'aimerais quand même avoir votre avis sur ce modèle de données


    Merci d'avance
    Etienne ZINZINDOHOUE
    Billets-Articles

  2. #2
    Expert confirmé
    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 : 46
    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
    Par défaut
    Bonjour,

    -->déplacer les .mdf des bases utilisateurs sur le disque D
    Une bonne chose à faire. Au passage le disque C: est il sur un axe physique différent que le disque D: ?

    -->Un redémarrage de l'OS est-il indispensable pour la prise en compte du paramétrage de /3GB dans le boot.ini ?
    Oui

    L'utilisation CPU oscille entre 98% - 100 % (visible via le Task manager)
    Je suppose que cette valeur est plus ou moins constante dans le temps. La il faut voir ce qui fait monter ta CPU ... avec dans un 1er temps les compteurs perfmon adéquates et les DMV si tu veux pousser l'analyse (Je n'ai pas le temps de mettre la liste ce matin mais je suppose que tu les connais) . Au fait environnemment virtualisé ou pas ?

    ++

  3. #3
    Membre Expert

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    Mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2010
    Messages : 1 278
    Par défaut
    Citation Envoyé par mikedavem Voir le message
    Bonjour,
    Une bonne chose à faire. Au passage le disque C: est il sur un axe physique différent que le disque D: ?
    Au fait il y a un SEUL disque physique. donc j'ai rebroussé chemin pour ce qui concerne le déplacement des data vers la partition logique D
    Citation Envoyé par mikedavem Voir le message
    Je suppose que cette valeur est plus ou moins constante dans le temps. La il faut voir ce qui fait monter ta CPU ... avec dans un 1er temps les compteurs perfmon adéquates et les DMV si tu veux pousser l'analyse (Je n'ai pas le temps de mettre la liste ce matin mais je suppose que tu les connais) . Au fait environnemment virtualisé ou pas ?
    L'environnement n'est pas virtualisé. c'est un serveur physique avec une architecture 32 bits.
    L'utilisation du processeur oscille entre 80 % et 98%

    Une lègère amélioration est observée côté applicatif ...
    Etienne ZINZINDOHOUE
    Billets-Articles

  4. #4
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 998
    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 998
    Billets dans le blog
    6
    Par défaut
    [/QUOTE][quote]
    Citation Envoyé par zinzineti Voir le message
    Pour une opération "pompier" j'ai un serveur dédié SQL Server 2005 SP2.
    OS = W2K3 SERVER R2 (US)
    1 Disque C : espace libre 8 Go
    1 Disque D : espace libre environ 17 Go
    ATTENTION, si l'espace libre sur les disque est faible, les temps de réponse augmente de manière exponentielle. Il n'est pas conseillé que l'espace libre fasse moins de 10%. (en général on alerte à 30% de libre).

    Mémoire = 4Go
    Processeurs = (je ne me souviens plus exactement, je vais le confirmer demain matin)
    Ce serveur SQL dialogue avec une application de gestion de centre d'appel.
    Et depuis quelques jours des lenteurs inacceptables sont constatées par les utilisateurs de l'application... Je passe les détails...[je vous rassure, tout de suite que je n'ai pas participé à l'installation de ce serveur ]
    Y a t-il d'autres applications installées, parce que 98 % de CPU me laisse à panser qu'il doit y avoir un antivirus, un firewall, u serveur web, un serveur d'impression et une usine à gaz qui tourne en //

    Après un audit rapide je constate que :

    1.) Les bases systèmes et les bases utilisateurs sont installées sur le même disque C. Les fichiers .mdf et .ldf sont tous sur le même disque C pour toutes les bases

    ce que je pense faire :
    -->déplacer les .mdf des bases utilisateurs sur le disque D
    Cela n'a d'intérêt que si les disques sont physiquement indépendant. Si ce sont des partitions cela ne sert à rien.

    2.) Le processus sqlserver.exe utilise un mémoire max d'environ 1,7 Go (visible via le Task manager) et dans le boot.ini l'option /3GB n'est pas activé

    ce que je pense faire :
    -->Mettre /3GB dans le boot.ini
    -->Configurer le max memory server size à 3Go côté SQL SERVER
    -->Un redémarrage de l'OS est-il indispensable pour la prise en compte du paramétrage de /3GB dans le boot.ini ?
    OUI

    3.) L'utilisation CPU oscille entre 98% - 100 % (visible via le Task manager)
    en exécutant cette requête
    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
    SELECT  
     spid 
    ,hostname 
    ,[status] 
    ,blocked 
    ,open_tran 
    ,waittime 
    ,cmd 
    ,memusage 
    ,cpu 
    ,physical_io 
    ,login_time 
    ,last_batch 
    ,loginame 
    ,DB_NAME(DBID) AS [database] 
    ,[program_name] 
    FROM master.sys.sysprocesses 
    WHERE spid > 50 
    AND spid <> @@SPID
    je constate qu'il y a une centaine de processus non blockés (colonnes blocked =0 et open_tran =0), mais qui sont dormant (colonne status = SLEEPING)
    et dont la date de dernière exécution est environ 1 mois (colonne last_batch) sur les bases utilisateurs.Le détails de quelques un de ces processus montre des DELETE tableUtilisateur WHERE date = 'date_ILYA_1mois'

    Je me demande :
    -->Je me demande ce qu'il faut faire de ces processus dormant !!! dormant mais consomme quand même des ressources (memoire,cpu,io) !!!
    Voir avec DBCC OPENTRAN quelle est la plus vieille transaction, si très ancienne la killer

    4.) trois des principales bases ont l'option AUTO CLOSE à TRUE

    ce que je pense faire :
    --> les mettre à FALSE
    Oh que OUI !

    5.) L'option page verify de toutes les bases Utilisateurs sont à TORN_PAGE_DETECTION

    ce que je pense faire :
    --> les mettre à CHECKSUM
    cela va légèrement ralentir

    6.) Pour toutes les bases le mode de récupération est à SIMPLE
    --> je ne change pas

    7.) La reverse engineering (à l'aide de Power AMC) d'une des principales base m'a montré :
    --> 13 tables au total pour cette base
    --> la principale table de cette base possède 132 colonnes
    --> le MPD montre que toutes les tables sont indépendantes : aucune jointure entre les tables !!!

    Ce n'est pas le plus urgent actuellemnt mais j'aimerais quand même avoir votre avis sur ce modèle de données
    TOTALEMENT a chier. Lisez les papier sur l'optimisation que j'ai fait. Dans 70% des cas c'est le modèle qui est la cause des mauvaises performances. A l'évidence vus êtes dans le cas le plus tragique : utilisation d'un SGBDR pour gérer des "fichiers COBOL" !


    Merci d'avance
    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/ * * * * *

  5. #5
    Membre Expert

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    Mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2010
    Messages : 1 278
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Y a t-il d'autres applications installées, parce que 98 % de CPU me laisse à panser qu'il doit y avoir un antivirus, un firewall, u serveur web, un serveur d'impression et une usine à gaz qui tourne en //
    Bien vu il y a des choses qui sont installées sur la même machine ! :
    --> IIS est installé
    --> ce IIS héberge des rapports (Reporting Services )
    --> Adobe Acrobat
    --> le FULL text est installé mais n'est pas utilisé !
    --> L'Analysis service installé mais pas utilisé
    --> SQL Server Browser est aussi installé
    Ce que j'ai fait
    -->Arrêter ces services qui ne sont pas utiles
    Citation Envoyé par SQLpro Voir le message
    Cela n'a d'intérêt que si les disques sont physiquement indépendant. Si ce sont des partitions cela ne sert à rien.
    Dans le gestionnaire de disque, j'ai constaté qu'il existe UN SEUL disque physique. C et D sont des partitions logiques d'UN et UN SEUL DISQUE.
    Ce que j'ai fait
    --> J'ai laissé les data à leur place (sur la partition C).

    A propos du processeur voici la config

    Processeur(s): 8 processeur(s) installé(s).
    [01]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
    [02]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
    [03]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
    [04]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
    [05]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
    [06]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
    [07]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
    [08]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
    Après les opérations et redémarrage du serveur :
    1.) la mémoire sqlsrv.exe tourne autour 2,8 Go. Preuve que le /3GB à fonctionner.
    2.) Le CPU oscille entre 80 % et 98%

    Une légère amélioration des temps de réponse est constatée côté applicatif .

    3.) J'ai commencé par regarder les requêtes les plus consommatrices de CPU et les requêtes les plus exécutées sur l'instance et là :
    --> des requêtes paramétrées du style WHERE id LIKE @p ( le paramètre @p est un entier)
    --> des requêtes kilométriques avec des SELECT imbriqués et des WHERE valeur NOT IN (...) avec des séries de NOT LIKE ....

    Malheureusement on peut pas toucher à ces requêtes parce qu'elles sont natives à l'application cliente

    --> J'ai proposé au propriétaire de la solution une purge de la plus grosse table qui contient actuellement plus de 5 millions de lignes (pour à peine 2 mois de mise en production de la solution !!!)

    En tout cas merci pour votre aide
    Etienne ZINZINDOHOUE
    Billets-Articles

  6. #6
    Membre Expert
    Profil pro
    Inscrit en
    Juin 2007
    Messages
    1 056
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juin 2007
    Messages : 1 056
    Par défaut
    une remarque :

    5.) L'option page verify de toutes les bases Utilisateurs sont à TORN_PAGE_DETECTION

    ce que je pense faire :
    --> les mettre à CHECKSUM
    cela va légèrement ralentir
    Je ne suis pas sûr que le CHECKSUM va ralentir car il semble qu'il ait moins de travail à faire que le TORN PAGE :

    When CHECKSUM is specified, the Database Engine calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header.

    When TORN_PAGE_DETECTION is specified, a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.

    cf. http://msdn.microsoft.com/en-us/library/ms190249.aspx

  7. #7
    Membre Expert

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    Mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2010
    Messages : 1 278
    Par défaut
    Citation Envoyé par kagemaru Voir le message
    une remarque :
    Je ne suis pas sûr que le CHECKSUM va ralentir car il semble qu'il ait moins de travail à faire que le TORN PAGE :
    Cette modif peut être faite à chaud ou pas ?

    Merci d'avance
    Etienne ZINZINDOHOUE
    Billets-Articles

  8. #8
    Membre Expert
    Profil pro
    Inscrit en
    Juin 2007
    Messages
    1 056
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juin 2007
    Messages : 1 056
    Par défaut
    qq liens utiles, je crois qu'il dit à peu près tout à propos du page_verify :

    http://blog.capdata.fr/index.php/mod...puis-sql-2000/
    http://blog.capdata.fr/index.php/myt...-que-checksum/

  9. #9
    Membre émérite
    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
    Par défaut
    Citation Envoyé par kagemaru Voir le message
    une remarque :



    Je ne suis pas sûr que le CHECKSUM va ralentir car il semble qu'il ait moins de travail à faire que le TORN PAGE :

    When CHECKSUM is specified, the Database Engine calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header.

    When TORN_PAGE_DETECTION is specified, a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.

    cf. http://msdn.microsoft.com/en-us/library/ms190249.aspx
    +1.

    Par contre je suis un peu sceptique sur la définition de TPD dans MSDN. Une info de Bob Ward du support sur le sujet: "An EX latch prevents writers and readers. For torn-page protection, the engine must actually modify contents of the page because we place a 2-bit signature at every 512 bytes on the page. While we are doing this, we don’t want anyone to write or read the page. If you are using checksum though, we only have to change a specific field in the header. No problem to let someone read the page, but we don’t want someone else to write it." Donc la signature va dans la page elle-même, pas dans l'entête ? A suivre.

  10. #10
    Membre Expert

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : Août 2007
    Messages : 1 216
    Par défaut
    Citation Envoyé par zinzineti Voir le message
    Et depuis quelques jours des lenteurs inacceptables sont constatées par les utilisateurs de l'application... Je
    Je rajouterai d'essayer d'identifier precisement quand les problemes de lenteur sont apparus et ce qu'il s'est passe sur le serveur a ce moment la.
    Car si tout etait stable avant et que les perfs etaient acceptables, il ne faut peut etre pas se lancer dans de grand travaux de re-amenagement bien qu'ils seraient preferables.
    Au client de decider...

  11. #11
    Membre Expert

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : Août 2007
    Messages : 1 216
    Par défaut
    Citation Envoyé par Ptit_Dje Voir le message
    Je rajouterai d'essayer d'identifier precisement quand les problemes de lenteur sont apparus et ce qu'il s'est passe sur le serveur a ce moment la.
    Car si tout etait stable avant et que les perfs etaient acceptables, il ne faut peut etre pas se lancer dans de grand travaux de re-amenagement bien qu'ils seraient preferables.
    Au client de decider...
    Je sais que je me repete...
    Aussi verifier les logs (windows et SQL Serveur).

  12. #12
    Membre Expert

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    Mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2010
    Messages : 1 278
    Par défaut
    Citation Envoyé par Ptit_Dje Voir le message
    Je sais que je me repete...
    Aussi verifier les logs (windows et SQL Serveur).
    Ce qui a changé c'est la volumétrie.
    Au début la taille des backup étaient autour de 5-6 Go
    Et aujourd'hui on est autour de 17 Go .

    A+
    Etienne ZINZINDOHOUE
    Billets-Articles

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Problème "LoadModule ssl_module modules/mod_ssl.so"
    Par ldcarpathes dans le forum Apache
    Réponses: 9
    Dernier message: 24/01/2008, 11h07
  2. Formulaires : problème avec les slashes et les quotes
    Par GarGamel55 dans le forum Langage
    Réponses: 1
    Dernier message: 12/10/2005, 15h59

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