Discussion :

[FMJ]

Salut,

Je rencontre depuis plusieurs jours et pour la première fois une erreur relative à la taille de la base TEMPDB lors de l'exécution d'une requête de type SELECT. Le contexte de l'erreur est la suivante :

> SGBQ SQL Server 2000 SP4 sur Windows Server 2003 SP2

> La base de donnée concernée pèse à peine 4.5Go sur un disque ayant 50Go de libre (SAS en RAID1)

> Les indexes de cette base ont été recrées il y a quelques jours et la base a été également compressée

> Les deux tables sur lesquelles porte la requête représentent respectivement 1.7 millions de lignes (pour près de 200 champs ... je sais mais c'est pas moi qui ait conçu le MCD) et 0.45 million de lignes (pour 135 champs).

> ces tables sont indexées, pas toujours de façon heureuse mais suffisante pour créer des jointures efficaces entre elles (de toute façon, c'est une base commerciale et je ne peux toucher à sa structure pour des contraintes liées au service d'assistance et de mise à jour de l'éditeur)

> La base TEMPDB est configurée avec une croissance non limitée, à 10% de croissance (database +log) et son mode de récupération est simple. Elle est située sur la même grappe que la base de donnée requêtée. Sa taille est actuellement à peine de 134Mo et 2.6Mo pour le journal (je veux bien mettre en place un plan de maintenance mais cela ne paraît pas des tonnes utile). Si le service SQLserver est rebooté, sa taille ne change pas.
Comme je souhaitais m'assurer que sa croissance n'était pas entravée, j'ai arrêté le service, renommé tempdb.mdf et relancé le service. SQL Server recrée tempdb.mdf avec une taille de 8Mo. Lorsque je relance la requête, la base tempdb grossit durant plus de 4min jusqu'à atteindre les 134Mo (à l'octet prêt) puis le message d'erreur intervient. tempdb.ldf ne bouge pas.

> La requête est la suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT TOP 20 LIGNE.ARTICLE, LIGNE.LIBELLE, SUM(LIGNE.MONTANT) FROM LIGNE INNER JOIN PIECE ON LIGNE.NUMERO = PIECE.NUMERO AND LIGNE.DEPOT = PIECE.DEPOT AND YEAR(PIECE.DATE)=2008
WHERE LIGNE.NATURE= 'TICKET'
GROUP BY LIGNE.ARTICLE, LIGNE.LIBELLE
ORDER BY 3 DESC

> Le message d'erreur est le suivant dans l'analyseur de requête :

(20 ligne(s) affectée(s))
Serveur : Msg 9002, Niveau 17, État 6, Ligne 1
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

> Si je fait des requêtes plus complexes et avec regroupement sur ces tables, sans utiliser TOP N, je n'ai pas de message d'erreur. Celui-ci intervient lors de l'utilisation de la clause TOP.

> J'ai fait un peu (un peu car sur le net il y en a pas mal de littérature sur le sujet) le tour du sujet, sans réellement trouver une situation proche de la mienne. En général, soit la croissance de la base tempdb est malconfigurée, soit il s'agit d'un problème de croissance trop importante, soit il s'agit d'interactions plus complexes.

Mais dans mon cas, la base est simple, tempdb est apparemment bien tunée, la taille de tempdb.mdf et ldf est ridiculement faible (c'est un suspect d'ailleurs), pas de pb de ressources disque. Bref, je ne vois pas d'où pourrait bien venir ce problème.

Merci d'avance de votre aide.

[SQLpro]

Les deux tables sur lesquelles porte la requête représentent respectivement 1.7 millions de lignes (pour près de 200 champs ... je sais mais c'est pas moi qui ait conçu le MCD) et 0.45 million de lignes (pour 135 champs).

Inutile de chercher plus loin : c'est une abhération. Si j'étais vous j'attaquerais en justice la personne qui à modélisé cette ignominie en vertu du principe de non respect des règles de l'art qui rend votre système inexploitable ! 200 colonnes c'est suicidaire surtout pour une telle volumétrie !

[la base tempdb] Elle est située sur la même grappe que la base de donnée requêtée.

C'est pas une bonne idée pour les performances. Mais passons. EN principe les fichiers de la temps DB devraient être séparés de tous les autres fichiers (data et JT) des bases de prod.

La base TEMPDB est configurée avec une croissance non limitée, à 10% de croissance (database +log)

C'est stupide... Taillez les fichier de votre tempdb au maximum. Lisez l'article que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro?ti..._fichiers_et_t

Comme je souhaitais m'assurer que sa croissance n'était pas entravée, j'ai arrêté le service, renommé tempdb.mdf et relancé le service. SQL Server recrée tempdb.mdf avec une taille de 8Mo. Lorsque je relance la requête, la base tempdb grossit durant plus de 4min jusqu'à atteindre les 134Mo (à l'octet prêt) puis le message d'erreur intervient. tempdb.ldf ne bouge pas.

Ce n'est pas comme cela que l'on retaille tempdb. Il faut faire un ALTER DATABASE tempdb MODIFY FILE... puis relancer le service SQL Server.
Essayez de mettre 300 Go pour la base et 200 Go pour le jt.

Si je fait des requêtes plus complexes et avec regroupement sur ces tables, sans utiliser TOP N, je n'ai pas de message d'erreur. Celui-ci intervient lors de l'utilisation de la clause TOP.

Lorsque vous faites une requête TOP N avec un ORDER BY il est nécessaire de trier TOUTES LES LIGNES DE LA TABLE, puis une fois ce tri effectué, aller y pêcher les N premières ligne.
Je doute que la capacité de votre RAM soit en mesure de traiter la totalité des lignes de votre requête, c'est pourquoi SQL Server décide d'utiliser une table temporaire implicitement créée pour résoudre le problème. Or les tables temporaires sont créées dans la temdb. Ce qui créée une transaction qui est journalisées dans la tempdb et explique pourquoi votre JT de temdb part en couille.
Solution n°1 : rajouter du disque + de la RAM (pis allé : reculer pour mieux sauter
Solution n°2 : modéliser correctement votre BD notamment en respectant les formes normales, ce qui vous conduira à de petites tables et solutionnera tous les problèmes d'un coup, y compris les performances !

Mais dans mon cas, la base est simple, tempdb est apparemment bien tunée, la taille de tempdb.mdf et ldf est ridiculement faible (c'est un suspect d'ailleurs), pas de pb de ressources disque. Bref, je ne vois pas d'où pourrait bien venir ce problème.

Je pense que vous comprendrez que vous avez tout faux !!!

A +

[FMJ]

Merci Frédéric pour ces infos et plus généralement pour tout le temps (et la patience ...) que tu consacres à ce forum.

Pour la structure de la base, je ne reviens pas dessus, je ne peux rien modifier. Sauf que je peux créer à côté une nouvelle base ne contenant que les données importantes pour mes stats (par exemple on passerait de 1.7Mx200 on passe à 1M x10 ....)

Pour le disque non dédié à tempdb, comme il y a très peu de requêtes volumineuses (à part celles que je fais mais qui peuvent tourner la nuit), ce ne doit pas très gênant dans notre cas.

Pour la taille de tempdb, je vais tailler les fichiers MDF et LDF à 10Go chacun.

Par contre, aurais-tu une explication logique au fait qu'en croissance illimitée, la taille ne dépasse pas 134Mo pour le MDF et 2.6Mo pour le LDF ?????!!!!!!

Pour la RAM? le server dispose de 4Go qui sont utilisées au max à 55%. LE server est totalement dédié à SQL Server. L'OS actuelle (STD Edition) ne permet par de dépasser les 4Go. Mais comme le server n'y monte jamais ... ,Il y a peut-être également un mauvais tuning de ce côté là.

Pour le top N, je peux utiliser des tables temporaires sur lesquelles je referais des requêtes en TOP N (ce qui donnerais 50 000 lignes x10 et non plus 1.7Mx200).

Je vais faire différents tests et ferai un retour ici.

Merci !

[SQLpro]

la taille ne dépasse pas 134Mo pour le MDF et 2.6Mo pour le LDF ?

C'est lié au mode de journalisation "simple" qui recycle les transactions achevées.

Pour la RAM? le server dispose de 4Go qui sont utilisées au max à 55%. LE server est totalement dédié à SQL Server. L'OS actuelle (STD Edition) ne permet par de dépasser les 4Go. Mais comme le server n'y monte jamais ... ,Il y a peut-être également un mauvais tuning de ce côté là.

Sur du 2005 il faut mettre le tag /3GB dans le boot.ini, comme je l'ais indiqué ici : http://blog.developpez.com/sqlpro?ti...32_bits_et_awe

Merci Frédéric pour ces infos et plus généralement pour tout le temps (et la patience ...) que tu consacres à ce forum.

Tu es gentil de me dire merci alors que je viens te pourrir la vie avec mes critiques !!! ;-)

A +

[FMJ]

Ce ne sont pas des critiques : ce sont des remarques.

Et les remarques, c'est toujours positif et ça aide toujours un max.

Non, non, ça m'impressionne toujours les personnes qui passent énormément de temps (tu n'es pas le seul, heureusement, mais il n'y en a pas tout de même des tonnes) à aider bénévolement la communauté (qui se découvre souvent spontanément des vertus d'échange lorsque les problèmes surgissent) en mettant leurs connaissances, expérience et savoir faire à dispo. Comme ça, sans arrière pensée.
Et je suis encore plus impressionné par la patience déployée face à des questions de débutants qui n'ont pas pris la peine de se documenter 10min sur le sujet.... C'est un truc qui m'agacerait rapidement ....

Cette semaine est un peu hot en terme d'activité pour tester ces modifs de paramétrage (tempdb + RAM). Même ce sera fait la semaine prochaine.

Merci en tout cas pour ton aide.
En tout cas, je teste le paramétrage