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

MS SQL Server Discussion :

Probleme : tempdb.mdf sature le disque du serveur sql.


Sujet :

MS SQL Server

  1. #1
    Membre habitué
    Profil pro
    Inscrit en
    Décembre 2007
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2007
    Messages : 14
    Par défaut Probleme : tempdb.mdf sature le disque du serveur sql.
    Bonjour,

    J'ai un probleme sur un serveur SQL 2003 : lorsque je lance une requete sql un peu compliquee, l'activite de sqlservr.exe devient tres importante (casi 100% CPU) et le fichier tempdb.mdf gonfle jusqu'a saturation du disque dur (il ocupe la 30ene de Go restant sur le disque) et l'activite de sqlservr.exe ne redescend pas (plantage du serveur sql). La requete sql ne repond pas.
    Il faut noter que le serveur est une machine virtuelle.
    Lorsque je redemarre le service sqlservr.exe, le fichier tempsb.mdf se resorbe et n'occupe plus que 8 Mo.

    Je ne peux malheureusement pas donner beaucoup plus de details, je ne suis pas expert en serveur SQL, mais s'il y a quelqu'un qui a deja ete confronte a ce probleme et qui aurait des pistes a me donner je suis prenneur !!

    Merci beaucoup !

    Clem.

  2. #2
    Membre Expert
    Avatar de rudib
    Homme Profil pro
    Fakir SQL Server & NoSQL
    Inscrit en
    Mai 2006
    Messages
    2 573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Fakir SQL Server & NoSQL

    Informations forums :
    Inscription : Mai 2006
    Messages : 2 573
    Par défaut
    Bonjour,

    Tout tient probablement dans ce que tu appelles "une requête SQL un peu compliquée".
    Peux-tu la poster (en suivant ces règles simples), et nous donner une idée de la volumétrie de ta base ?

  3. #3
    Membre habitué
    Profil pro
    Inscrit en
    Décembre 2007
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2007
    Messages : 14
    Par défaut
    Voici un dixieme de la requete :

    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
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    SELECT DISTINCT
     TABLE_P.LIBELLE,
     TABLE_P.CODE,
     TABLE_DOP.CODE,
     TABLE_R.LIBELLE,
     TABLE_R.STATUT_RESSOURCE,
     TABLE_DPO.CODE,
     (case substring(TABLE_LOP.CODE,1,4)
     
    when 'ETUD' then substring(TABLE_LOP.CODE,1,4)
     
    when 'DEVE' then substring(TABLE_LOP.CODE,1,4)
     
    when 'RECC' then substring(TABLE_LOP.CODE,1,4)
     
    when 'MURI' then substring(TABLE_LOP.CODE,1,4)
     
    else TABLE_LOP.CODE
     
    end),
     TABLE_LOP.L_COURT,
     TABLE_LOP.L_LONG,
     TABLE_I.C,
     TABLE_ICA.A,
     TABLE_P.NCP,
     TABLE_PH.L_LONG,
     TABLE_AC.L_LONG,
     TABLE_T.L_LONG,
    TABLE_JAP.J_1_R,
    TABLE_JAP.J0_R
    TABLE_JAP.J2_R
    TABLE_AVC.NA,
    TABLE_AVC.C,
     TABLE_P.A,
     left(TABLE_P.CODE,1),
     TABLE_P.TYPE_P,
     TABLE_T.C,
     ( sum( TABLE_PPP.C_01 +  TABLE_PPP.RAF_01 +  TABLE_PPP.C_02 +
     TABLE_PPP.RAF_02 +  TABLE_PPP.C_03 +  TABLE_PPP.RAF_03 +  TABLE_PPP.C_04
    +  TABLE_PPP.RAF_04 +  TABLE_PPP.C_05 +  TABLE_PPP.RAF_05 +
     TABLE_PPP.C_06 +  TABLE_PPP.RAF_06 +  TABLE_PPP.C_07 +  TABLE_PPP.RAF_07
    +  TABLE_PPP.C_08 +  TABLE_PPP.RAF_08 +  TABLE_PPP.C_09 +
     TABLE_PPP.RAF_09 +  TABLE_PPP.C_10 +  TABLE_PPP.RAF_10 +  TABLE_PPP.C_11
    +  TABLE_PPP.RAF_11 +  TABLE_PPP.C_12 +  TABLE_PPP.RAF_12) ) / 20,
     ( sum( TABLE_PPP.C_01 +  TABLE_PPP.C_02 +  TABLE_PPP.C_03 +
     TABLE_PPP.C_04 +  TABLE_PPP.C_05 +  TABLE_PPP.C_06 +  TABLE_PPP.C_07 +
     TABLE_PPP.C_08 +  TABLE_PPP.C_09 +  TABLE_PPP.C_10 +  TABLE_PPP.C_11 +
     TABLE_PPP.C_12) ) / 20,
     ( sum( TABLE_PPP.RAF_01 +  TABLE_PPP.RAF_02 +  TABLE_PPP.RAF_03 +
     TABLE_PPP.RAF_04 +  TABLE_PPP.RAF_05 +  TABLE_PPP.RAF_06 +
     TABLE_PPP.RAF_07 +  TABLE_PPP.RAF_08 +  TABLE_PPP.RAF_09 +
     TABLE_PPP.RAF_10 +  TABLE_PPP.RAF_11 +  TABLE_PPP.RAF_12) ) / 20,
     sum( TABLE_PPP.C_01 +  TABLE_PPP.C_02 +  TABLE_PPP.C_03 +
     TABLE_PPP.C_04 +  TABLE_PPP.C_05 +  TABLE_PPP.C_06 +  TABLE_PPP.C_07 +
     TABLE_PPP.C_08 +  TABLE_PPP.C_09 +  TABLE_PPP.C_10 +  TABLE_PPP.C_11 +
     TABLE_PPP.C_12),
     sum( TABLE_PPP.RAF_01 +  TABLE_PPP.RAF_02 +  TABLE_PPP.RAF_03 +
     TABLE_PPP.RAF_04 +  TABLE_PPP.RAF_05 +  TABLE_PPP.RAF_06 +
     TABLE_PPP.RAF_07 +  TABLE_PPP.RAF_08 +  TABLE_PPP.RAF_09 +
     TABLE_PPP.RAF_10 +  TABLE_PPP.RAF_11 +  TABLE_PPP.RAF_12),
     sum( TABLE_PPP.REF_01 +  TABLE_PPP.REF_02 +  TABLE_PPP.REF_03 +
     TABLE_PPP.REF_04 +  TABLE_PPP.REF_05 +  TABLE_PPP.REF_06 +
     TABLE_PPP.REF_07 +  TABLE_PPP.REF_08 +  TABLE_PPP.REF_09 +
     TABLE_PPP.REF_10 +  TABLE_PPP.REF_11 +  TABLE_PPP.REF_12),
     sum( TABLE_PPP.C_01 +  TABLE_PPP.RAF_01 +  TABLE_PPP.C_02 +
     TABLE_PPP.RAF_02 +  TABLE_PPP.C_03 +  TABLE_PPP.RAF_03 +  TABLE_PPP.C_04
    +  TABLE_PPP.RAF_04 +  TABLE_PPP.C_05 +  TABLE_PPP.RAF_05 +
     TABLE_PPP.C_06 +  TABLE_PPP.RAF_06 +  TABLE_PPP.C_07 +  TABLE_PPP.RAF_07
    +  TABLE_PPP.C_08 +  TABLE_PPP.RAF_08 +  TABLE_PPP.C_09 +
     TABLE_PPP.RAF_09 +  TABLE_PPP.C_10 +  TABLE_PPP.RAF_10 +  TABLE_PPP.C_11
    +  TABLE_PPP.RAF_11 +  TABLE_PPP.C_12 +  TABLE_PPP.RAF_12)
    FROM
    TABLE_JAP RIGHT OUTER JOIN TABLE_P ON
    (TABLE_JPL.PLANNING=TABLE_P.ID_PLANNING)
      FULL OUTER JOIN TABLE_LOP ON
    (TABLE_P.ID_PLANNING=TABLE_LOP.PLANNING)
      FULL OUTER JOIN TABLE_PH ON
    (TABLE_PH.LOT_OPERATIONNEL=TABLE_LOP.ID_LOT_OP)
      FULL OUTER JOIN TABLE_AC ON (TABLE_AC.PHASE=TABLE_PH.ID_PHASE)
      FULL OUTER JOIN TABLE_T ON (TABLE_T.ACTIVITE=TABLE_AC.ID_ACTIVITE)
      FULL OUTER JOIN  TABLE_PPP ON ( TABLE_PPP.TACHE=TABLE_T.ID_TACHE)
      FULL OUTER JOIN TABLE_R ON
    (TABLE_R.ID_RES= TABLE_PPP.RES)
      ... 
    ...

  4. #4
    Expert confirmé
    Homme Profil pro
    Big Data / Freelance EURL
    Inscrit en
    Mars 2003
    Messages
    2 124
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Big Data / Freelance EURL

    Informations forums :
    Inscription : Mars 2003
    Messages : 2 124
    Par défaut
    Des full outer join à répetitions !!!! Ben on a un début de causes de ton problème. Et en plus c'est un dixème de la requête !!!

    Il faut revoir ta requête.

  5. #5
    Membre habitué
    Profil pro
    Inscrit en
    Décembre 2007
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2007
    Messages : 14
    Par défaut
    Tout d'abord, merci beaucoup pour l'interet que vous portez a mon probleme, ca fait du bien de se sentir soutenu !

    Je doute que cette requete soit defectueuse car elle retourne des resultats coherents quand elle est lancee sur un autre serveur. Meme si effectivement elle pourrait etre optimisee... Il me semble que le probleme vienne plus de la config de SQL sur le serveur ... car il y a 2 mois cette requete fonctionnait ! .. et graduellement il falait un delais de reponse de plus en plus important jusqu'a maintenant ou l'on ne peut plus rien en tirer ...

    Si je ne suis pas clair n'hesitez pas a poser des questions .. !

    Merci encore !

    Clem.

  6. #6
    Expert confirmé
    Homme Profil pro
    Big Data / Freelance EURL
    Inscrit en
    Mars 2003
    Messages
    2 124
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Big Data / Freelance EURL

    Informations forums :
    Inscription : Mars 2003
    Messages : 2 124
    Par défaut
    Citation Envoyé par ClemRz Voir le message
    Je doute que cette requete soit defectueuse car elle retourne des resultats coherents
    Je trouve cette argumentation bancale . Cette requête peut certes ramener les résultats que tu attends à 100%, mais ça n'empêche pas qu'elle peut être défectueuse dans la façon dont elle est écrite, sans parler de problèmes éventuels de modélisation des tables, et donc poser de gros problèmes de performance.

    Preuve qu'il y a un souci, c'est que tu rajoutes un DISTINCT au début du SELECT pour contrecarrer les effets de bord des FULL OUTER JOIN qui doivent certainement générer des lignes en trop que tu dois dédoublonner ensuite.

  7. #7
    Membre Expert
    Avatar de rudib
    Homme Profil pro
    Fakir SQL Server & NoSQL
    Inscrit en
    Mai 2006
    Messages
    2 573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Fakir SQL Server & NoSQL

    Informations forums :
    Inscription : Mai 2006
    Messages : 2 573
    Par défaut
    Citation Envoyé par ClemRz Voir le message
    Il me semble que le probleme vienne plus de la config de SQL sur le serveur ... car il y a 2 mois cette requete fonctionnait ! .. et graduellement il falait un delais de reponse de plus en plus important jusqu'a maintenant ou l'on ne peut plus rien en tirer ...
    Quelle est ton estimation de l'augmentation de volume de ta base de données depuis 2 mois ? Sur les tables touchées par ta requête, bien sûr.

  8. #8
    Membre habitué
    Profil pro
    Inscrit en
    Décembre 2007
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2007
    Messages : 14
    Par défaut
    Citation Envoyé par phili_b Voir le message
    ... elle peut être défectueuse dans la façon dont elle est écrite...
    En fait, ce n'est pas moi qui ai ecrit cette requete, elle est generee automatiquement par un outil BO (Business Object)... je ne suis pas en mesure de la retravailler.

    Citation Envoyé par rudib Voir le message
    Quelle est ton estimation de l'augmentation de volume de ta base de données depuis 2 mois ? ...
    L'augmentation du volume des tables est tres faible, genre moins d'un pour cent en l'espace de deux mois sur la totalite de la base ...

    S'il y a d'autres pistes que je peux eclairer n'hesitez pas a poser des questions ...

    Merci !!

    Clem.

  9. #9
    Expert confirmé
    Homme Profil pro
    Big Data / Freelance EURL
    Inscrit en
    Mars 2003
    Messages
    2 124
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Big Data / Freelance EURL

    Informations forums :
    Inscription : Mars 2003
    Messages : 2 124
    Par défaut
    Citation Envoyé par ClemRz Voir le message
    En fait, ce n'est pas moi qui ai ecrit cette requete, elle est generee automatiquement par un outil BO (Business Object)... je ne suis pas en mesure de la retravailler.
    Je connais très bien BO. C'est donc l'univers qu'il faut modifier, s'il est mal conçu, et peut-être même la modélisation de la base, sur laquelle il s'appuit, qui devrait être en étoile pour éviter les full outer join.

    Mais si tu n'as pas la possibilité de modifier ni l'univers, ni la modélisation mais que c'est la même requête qui est lancé tout les mois de façon identique, à part des critères, alors tu peux soit la lancer à l'extérieur de BO et l'optimiser, soit créer une table d'agrégat à partir de ta requête ou, dans le pire des cas qui est déconseillé, utiliser une vue dont la requête serait optimisée, ou encore utiliser du SQL à la carte dans BO.

    Mais quelque soit la façon dont on voit le problème les responsables sont les full outer join.

    Mais si on cherche d'autres causes qu'est-ce qui a changé d'autres ? Par exemple est-ce que cette requête était lancée la nuit et maintenant elle est lancée en pleine journée ? Ou alors elle est en concurence avec un batch de nuit ? Est-ce que le serveur de base de données n'as plus de place disque et swap.

  10. #10
    Membre émérite
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Août 2006
    Messages
    730
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Août 2006
    Messages : 730
    Par défaut
    pour étailler ce que t'on dis rudib et phil_b;

    essaies de mettre a la fin un where restrictif du type

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE TABLE_LOP.CODE='ETUD'
    pour limiter les lignes.

    Lance ta requête sans le distinct du début, regarde le nombre de lignes retournées, puis relance là avec le distinct.
    regarde le coefficient de doublons (nombres lignes 1°requête/nombres lignes 2°requête) qui peut expliquer ton saut a plus de 30gigas

    En plus, un RIGTH OUTER ne doit pas être utlisé, il faut inverser les tables jointées et utiliser un LEFT OUTER

  11. #11
    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
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Il faut noter que le serveur est une machine virtuelle.
    ce n'est déjà pas très bon, les SGBDR fonctionnant de manière atypique et court circuitant beacoup de ce que fait un OS traditionnel. La virtualisation est donc un bon moyen de descendre les perfs de SQL Server...

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Lorsque je redemarre le service sqlservr.exe, le fichier tempsb.mdf se resorbe et n'occupe plus que 8 Mo.
    Tout à fait logique la base tempdb est recréée à chaque démarrage du serveice SQL Server.

    Maintenant pour votre problème il est probablement normal. Il semble que votre requête ais pris de plus en plus de ligne dans la figure du fait de la montée en charge et des outer joins. Au bout d'un moment le volume de la requête peut-être telle qu'elle ne tien plus dans la worktable qui est placée dans la tempdb.

    Vérifiez déjà dans votre plan de requête qi vous avez une worktable. notez la taille des lignes à chaque étape et tentez de voir quel volume cela va engendré dans la tempdb.

    De toute façon et à mon sens, travailler de grands volumes de données en serveur virtule et en limitant la taille des disque à des valeurs basses est un non sens absolu !
    En principe il ne faudrait jamais sur un serveur physique avoir des disques pleins à plus de 60%

    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: 6
    Dernier message: 05/08/2011, 18h10
  2. Réponses: 3
    Dernier message: 26/07/2007, 14h33
  3. [MSDE] Probleme SQLAgent ne voit pas mes instances/serveur
    Par papouAlain dans le forum MS SQL Server
    Réponses: 4
    Dernier message: 08/03/2006, 13h04
  4. [Socket] Probleme entre un Client C et un serveur JAVA
    Par bpy1401 dans le forum API standards et tierces
    Réponses: 3
    Dernier message: 28/02/2006, 08h40

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