Discussion :

[lebracfr]

Bonjour,

Je ne comprend pas bien comment évolue la taille d'un fichier de données dans SQL server.
J'essaye actuellement de déplacer des redondances des données dans des tables annexes et je suis surpris de constater que lors de la suppression d'une colonne (les données ayant été déplacée dans un autre table), la taille du fichier ne diminue pas.

J'ai poussé un peu plus mes recherches et si j'effectue une requête d'update sur mon fichier (900 000 lignes) afin de passer les chaines vides à NULL, mon fichier prends 300 MO, ensuite si j'effectue la requête inverse de '' à NULL mon fichier prends à nouveau 300 MO.

Quelqu'un aurait-il une explication ?

D'avance merci.

[El-Macho59]

Bonjour lebracfr,

Parlez vous du fichier .mdf ou .ldf ?

[SQLpro]

Bonjour,

Je ne comprend pas bien comment évolue la taille d'un fichier de données dans SQL server.
J'essaye actuellement de déplacer des redondances des données dans des tables annexes et je suis surpris de constater que lors de la suppression d'une colonne (les données ayant été déplacée dans un autre table), la taille du fichier ne diminue pas.

C'est tout à fait normal. Le coût d'un agrandissement de fichier, comme celui d'un rétrécissement s'avère très cher en ressource et très consommateur en temps. Donc, pour des raisons d'optimisation, SQL Server ne rétrécit jamais la taille d'un fichier, sauf à être masochiste et à placer sa base en mode AUTOSHRINK ce qui est absolument a évité sauf si vous êtes sur de l'embarqué (cas du smartphone par exemple).

J'ai poussé un peu plus mes recherches et si j'effectue une requête d'update sur mon fichier (900 000 lignes) afin de passer les chaines vides à NULL, mon fichier prends 300 MO, ensuite si j'effectue la requête inverse de '' à NULL mon fichier prends à nouveau 300 MO.

Quelqu'un aurait-il une explication ?

D'avance merci.

C'est à nouveau normal. En faisant un UPDATE vous modifier potentiellement l'ordre des données dans un index et la place occupée par les données dans les "slot" de ligne et dans les pages.

En sus, le fichier du journal de transaction, copie toutes les données avant modification afin de garantir la bonne fin des opérations. Si le serveur tombait en panne au moment d'exécuter la mise à jour, le fait d'avoir journalisé les données dans le journal de transaction, permet au système de retrouver automatiquement un point d'intégrité avec :

• soit toutes les valeurs modifiées
• soit aucune valeur modifié

C'est le principe d'atomicité des transactions, c'est à dire un fonctionnement en "tout ou rien".

Enfin, comme vous n'indiquez pas quel fichier vous constatez grossir, il est difficile de vous aider plus…


PS : lisez les cours que j'ai écrit à ce sujet : https://sqlpro.developpez.com/


A +

[lebracfr]

Bonjour,

Merci pour ces premiers éléments de réponse.
En fait, je consulte la "taille du fichier" dans le SQL Management studio, propriété de la table, onglet "stockage" et c'est la ligne "espace de données" qui enfle à chaque modification.

[Invité]

En fait, je consulte la "taille du fichier" dans le SQL Management studio, propriété de la table, onglet "stockage" et c'est la ligne "espace de données" qui enfle à chaque modification.

Vous confondez un fichier de données avec la taille de votre table. Un fichier de données contient plusieurs objets comme des tables, indexes...

[lebracfr]

En effet, je me suis mal exprimé.

Merci SQLPro, j'avais déja lu vos cours (très intéressants et très agréables à lire) et ma question initiale vient de vos conseils en optimisation concernant le modèle de données. Je travaille actuellement sur un modèle fortement redondant et j'aimerai appliquer vos recommandations en vue d'arriver aux formes normales.
En l'espèce, je travail sur une table de personnes qui contient diférentes colonnes (tel, fax, mobile) et surtout une colonne mail (char 250 !) qui n'est que très rarement valorisée et que je souhaite donc déportée dans une table annexe. J'étais alors surpris de constater que ma table ne gagnait pas en taille.

Du coup, puisque SQLserver ne rétrécie jamais un table pour des raisons de performance, j'imagine que la taille non rendue au système et alors disponible pour les futures insertions ?

En vue de remodéliser, serait-il plus opportun dans ces condition de créer une nouvelle table vierge et d’insérer les données plutôt que de modifier la structure de la table actuelle ? Cette solution pose également la question des PK qui changeront de valeur m'obligeant à corriger toutes mes liaisons.

[Invité]

char 250 !

Lol ! Essaie NCHAR(4000) à la place, ça prendra encore plus de place pour rien
Sérieusement remplace ça tout de suite par VARCHAR(250)

[SQLpro]

... surtout une colonne mail (char 250 !)…

Oui effectivement mieux vaudrait du VARCHAR(256)...

Du coup, puisque SQLserver ne rétrécie jamais un table pour des raisons de performance, j'imagine que la taille non rendue au système et alors disponible pour les futures insertions ?

Tout à fait !

En vue de remodéliser, serait-il plus opportun dans ces condition de créer une nouvelle table vierge et d’insérer les données plutôt que de modifier la structure de la table actuelle ? Cette solution pose également la question des PK qui changeront de valeur m'obligeant à corriger toutes mes liaisons.

Dans tous les cas vous aurez besoin de PK/FK....

Enfin, en défragmentant vos tables et index, vous pourrez restituer de la place…. C'est un travail ordinaire d'un plan de maintenance (DBA).

A +