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

Version imprimable

18/01/2008, 10h37
ClemRz

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.
18/01/2008, 10h46
rudib

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 ?

Voici un dixieme de la requete :

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

18/01/2008, 11h53
phili_b

Des full outer join à répetitions !!!! 8O Ben on a un début de causes de ton problème. Et en plus c'est un dixème de la requête !!! :aie:

Il faut revoir ta requête.
18/01/2008, 12h02
ClemRz

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.
18/01/2008, 12h10
phili_b

Citation:

Envoyé par ClemRz

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.
18/01/2008, 12h33
rudib

Citation:

Envoyé par ClemRz

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.
18/01/2008, 13h49
ClemRz

Citation:

Envoyé par phili_b

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

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.
18/01/2008, 14h04
phili_b

Citation:

Envoyé par ClemRz

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.
18/01/2008, 14h10
serge0934

pour étailler ce que t'on dis rudib et phil_b;

essaies de mettre a la fin un where restrictif du type

Code:

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
18/01/2008, 22h54
SQLpro

Code:

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:

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 +