Discussion :

[WebPac]

Bonjour, j'ai fait une application qui utilise SQL Serveur comme base de donnée de stockage.

Quand on y insère en une fois plus d'un million d'enregistrements, SQL Serveur prend de plus en plus de mémoire pour monter jusqu'à 300Mo. Une fois les insertions terminées, il garde toute cette mémoire.
J'ai vérifié avoir fermé la requête et la connexion une fois les données insérées. J'ai aussi vérifié que la transaction était fermée à la fin de l'exécution.

Est-ce un fonctionnement normal de SQL Serveur de garder toute cette mémoire ? Et si non, comment peut-on lui demander de la rendre ?

Merci.

[KiLVaiDeN]

Salut,

Selon mon expérience, c'est un fonctionnement normal de SQL Serveur.

Je n'ai pas l'impression qu'il y ait de moyen de lui demander de retourner cette mémoire, mais je me rappelle que la consommation mémoire augmentait et n'étais pas libérée. Cependant, elle est libérée ensuite petit à petit, si tu observes bien le gestionnaire des taches.

Peut être que fermer et réouvrir SQL Serveur par le biais d'un script serait une solution ?

A+

[Fabien Celaia]

Avez-vous configuré votre SQL Server en mémoire dynamique ? Avec un maximum ? Ou avez-vous tout laissé par défaut ? Dans ce dernier cas, partant du principe que plus il a de mémoire et plus il se porte bien ()= plus le cache de données est important), il se sert. A vous de le limiter.

[WebPac]

Salut, merci de m'avoir répondu si vite.

Avez-vous configuré votre SQL Server en mémoire dynamique ? Avec un maximum ? Ou avez-vous tout laissé par défaut ? Dans ce dernier cas, partant du principe que plus il a de mémoire et plus il se porte bien ()= plus le cache de données est important), il se sert. A vous de le limiter.

J'ai tout laissé par défaut, je ne suis pas administrateur de base de donnée, je ne m'y connais pas vraiment. Aurriez-vous des liens qui expliqueraient comment faire tout ça ?

En fait, le vrai problème est que j'ai un utilisateur qui a 2+4 Go ( vive + virtuelle ) de mémoire et qui désire insérer 42 millions d'enregistrements. Et lors de l'insertion il a le message d'erreur :

Out of memory

qui vient du fait que toute la mémoire est utilisée et je me vois mal lui demander d'augementer encore sa mémoire virtuelle.

[WebPac]

Avez-vous configuré votre SQL Server en mémoire dynamique ? Avec un maximum ?

En fait ma question est : est-ce qu'en limitant la mémoire totale utilisée par SQL Serveur lui permettra tout de même de travailler normalement même avec 45 millions d'enregistrements à insérer ?

[rudib]

Bonjour,

il n'y a pas trop de raison d'avoir un out of memory avec 2 GB de RAM. Je n'ai jamais vu SQL Server mourir de manque de RAM. Etes-vous sûr que le message "out of memory" ne vient pas d'un autre process ?

Le problème que vous pourriez rencontrer en insérant 45 mio de lignes est plus du côté du log de transaction, qui risque de se remplir pas mal. Si vous voulez essayer de limiter la charge, peut-être pouvez-vous tourner votre INSERT dans une boucle, ou importer les données en plusieurs fois si vous importez d'un fichier ou d'une source externe.

[WebPac]

il n'y a pas trop de raison d'avoir un out of memory avec 2 GB de RAM. Je n'ai jamais vu SQL Server mourir de manque de RAM. Etes-vous sûr que le message "out of memory" ne vient pas d'un autre process ?

Pour l'instant, ce message n'apparaît que pendant l'insertion des données dans SQL Serveur, ce qui ne garanti forcément que cela vienne de lui. Cela peut venir de mon application qui fait l'insertion mais il est sûr que cela vient de cette insertion.

Le problème que vous pourriez rencontrer en insérant 45 mio de lignes est plus du côté du log de transaction, qui risque de se remplir pas mal.

Je fais des commit tous les 1000 enregistrements afin de ne pas trop charger ce travail.

peut-être pouvez-vous tourner votre INSERT dans une boucle, ou importer les données en plusieurs fois si vous importez d'un fichier ou d'une source externe.

L'insertion de fait automatiquement en fonction des données sources des utilisateurs, il serait laborieux de découper l'insertion en plusieurs parties et le choix de la taille des différentes insertions serrait assez arbitraire (il faudrait prendre en compte le nombre d'enregistrements, le nombre de colonnes, la mémoire de la machine...) et ferrait beaucoup de développement.

En fait, ce que j'ai pu remarquer, c'est qu'à chaque insertion, SQL Serveur prend plus de ressources et ne les rend pas toutes, ainsi il augmente progressivement. Ce qui peut faire que les autre applications n'ont plus de ressources pour fonctionner et font des "Out of Memory". Je ne cherche pas à désigner SQL Serveur comme étant le coupable, mais j'aimerais savoir si c'est un fonctionnement classique de SQL Serveur ou si c'est mon application qui ne sait pas lui demander de rendre de la ressource.

PS : pour récupérer des ressources, redémarrer le SQL Serveur sans fermer le processus ne rend pas ces ressources, il faut arrêter le processus et relancer SQL Serveur, mais ce n'est qu'une solution de bricolage que les utilisateurs finaux n'accepteront pas.

[KiLVaiDeN]

Salut,

Pour optimiser les insertions je pense que tu peux procéder à un BULK INSERT, en fournissant un fichier contenant tous tes inserts; c'est je pense optimisé, et ça consomme moins de mémoire.

Utilises-tu la même connection pour faire tes insertions à partir de ton application ? Je veux dire par là, que si ça se trouve, ton application a un problème au niveau de la gestion des connections ( quel langage d'application ? )

[WebPac]

J'utilise Delphi pour l'application.

La connexion de la base de donnée source est dans ce cas aussi SQL Serveur mais il se peut très bien que d'autres clients aient d'autres bases de données sources.

Pour optimiser les insertions je pense que tu peux procéder à un BULK INSERT,

Ca veut dire que je dois faire une étape intermédiaire ? Charger les données sources dans un fichier puis insérer les données du fichier dans SQL Serveur ?
Cela risque à priori de doubler les temps.

[KiLVaiDeN]

Je ne suis pas sûr du tout que ça double le temps.

J'ai souvent vu le cas d'un script SQL beaucoup plus rapide qu'une application manageant ce même processus. Donc tu pourrais être gagnant si le nombre d'actions sur la base est important.

Dans ton application : Utilises-tu un pool de connection ? Pour chacun de tes inserts (à chaque itération), fais-tu une création d'objet ? Peut-être est-ce là une perte en performance.

Je ne sais pas trop quoi d'autre te dire à ce sujet. Bonne chance

A+

[rudib]

Comme normalement SQL Server tourne plutôt sur des machines dédiées, il n'est pas gênant qu'il prenne les ressources. Pour la mémoire, en effet je ne l'ai jamais vraiment vu la rendre. Bref, c'est un comportement classique. Microsoft recommande en général de dédier son serveur à SQL. Si tu travailles avec de tels volumes de données, ça commence sans doute à être une bonne idée. Sinon tu peux touojurs limiter le max memory de SQL Server. Tu souffriras alors de problèmes de lenteur en lecture (manque de cache de données).
Une autre source de lenteur peut venir du disque. Si tu lis ta source, et écris le log de transaction et les data sur le même disque, il risque d'y avoir un peu de contention. Avec qq indicateurs de perfmon tu peux suivre un peu ça (jette un oeil sur mon site à http://www.babaluga.org/doku.php/sql...stration/index pour quelques pistes).

Si tu peux placer ton appli Delphi sur une autre machine, c'est peut-être une bonne solution, ou de laisser SQL gérer l'insertion des données, par bulk insert ou DTS.

Ah, ok, j'ai compris : tu veux d'abstraire du type de SGBD. Pour SQL Server, le bulk insert va être bien plus rapide.
Autre question: as-tu des index ou des contraintes (check, clé étrangères..) sur ta table. Pour les index et les contraintes, il vaut mieux les dropper et le recréer après l'insert. En cas de trigger, tu peux les disabler avec un ALTER TABLE

[SQLpro]

Ajouter 45 millions de lignes en une seule transaction c'est sans doute cela qui cause cet out of memory.

Tout dépend de comment vous faîtes l'ajout.

Si c'est à partir d'un fichier texte, vous pouvez parcéliser ce fichier en multiples lots.
Intéressez vous à la commande BULK INSERT et à l'option KILOBYTES_PER_BATCH, sinon prpgrammez une boucle qui insère n ligne (par exemple 5000 lignes). Le mieux étant de calculer que le nombre de ligne représente un multiple de l'extension qui est de 64 Ko.

A +

[WebPac]

Bonjour et merci de vouloir m'aider.

En fait, c'est un oubli de ma part, la base de donnée qui reçoit les 45 millions de lignes est en SQL Serveur, mais il est possible que l'utilisateur choisisse une autre base de donnée, il peut aussi prendre Firebird/Interbase, Oracle et MySQL, donc il ne m'est pas possible de faire un développement spécifique pour SQL Serveur.

Je fais l'ajout des 45 millions de ligne en 1 fois mais en faisant plusieurs transactions, chaque transaction prenant 1000 enregistrements.