Discussion :

[Jinroh77]

Bonjour,
Sur une base de dev utilisée pour de l'alimentation de cubes Olap, je cherche à vider une table.
Cette table comporte, entre autre, un champ qui me permet via une jointure de filtrer une partie de celle-ci.

Je cherche donc à supprimer dans cette table une partie des lignes ce qui sature le journal de transactions.

Quelques détails :
La base fait 25 Go avec 5Go de journaux, en mode simple.
La table contient plus de 5 millions de lignes et je cherche en supprimer 99%.
Je ne peux pas utiliser de truncate et boucler sur un nombre de ligne, pour supprimer par partie, me dérange particulièrement.
J'exécute via un package SSIS à plusieurs moments de mon alimentation sur des tables différentes.
J'ai un index cluster sur le champ utilisé pour ma jointure.
Un sp_spaceused sur la table me renvois 817 Mo de data et 5 Mo d'index.

Code :

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

1
2
3
4
5
6
delete Ir.FtPv 
from Common.DimSource src
JOIN Ir.FtPv ft
	ON ft.SourceId = src.SourceId
where
	src.Code = 'S'

Utilisant un plan faisant un Clustered Index Seek sur ma table de faits, un nested loops avec la table DimSource puis il termine par un Clustered Index Delete.


J'ai pour le moment du mal à convaincre les DBA d'augmenter la taille du journal pour cette base de dev et à part faire un truncate manuel avant mon alimentation, je n'ai pas beaucoup de solution pour le moment

[Jinroh77]

Pas d'autres idées d'optimisation ou autre ?

[mikedavem]

Bonjour,

Quel est ton mode de récupération pour la base concernée ?

++

[Jinroh77]

Désolé, oublié de préciser mais sur la dev comme l'homolo nous sommes en simple.

[elsuket]

Bonjour,

Dans ce cas c'est que votre journal n'est pas configuré pour grossir automatiquement.
Vous pouvez le voir dans les propriétés de la base de données, ou en interrogeant la table sys.database_files, et en regardant la colonne growth, éventuellement en combinaison avec is_percent_growth.
Quelles valeurs avez-vous ?

@++

[mikedavem]

D'autres pistes :

Est ce qu'il y a d'autre index autre que l'index cluster sur la table ?
Une désactivation de ceux-ci pourrait limiter la quantité d'informations dans le journal.

As tu pensé à inverser la logique de mise à jour de ta table ?
Tu dis que tu as environ 99% de données à supprimer. Alors dans ce cas tu pourrais copier les 1% de données non supprimés dans une table, d'opérer ensuite un TRUNCATE (journalisation mimime) et enfin de restaurer les données sauvegardés dans la table ...

++

[Jinroh77]

Bonjour,

Dans ce cas c'est que votre journal n'est pas configuré pour grossir automatiquement.
Vous pouvez le voir dans les propriétés de la base de données, ou en interrogeant la table sys.database_files, et en regardant la colonne growth, éventuellement en combinaison avec is_percent_growth.
Quelles valeurs avez-vous ?

@++

Les fichiers du journal de transactions ne sont malheureusement pas en autogrowth. Le DBA voulant justement éviter que celui-ci enfle trop. D'où nos blocages.
Il estime que ce journal devrait faire au max 33% de la taille de la base. Alors pour le moment, à part lui demander d'augmenter artificiellement la taille de la base...

D'autres pistes :

Est ce qu'il y a d'autre index autre que l'index cluster sur la table ?
Une désactivation de ceux-ci pourrait limiter la quantité d'informations dans le journal.

Cet index est le seul de la table et ne représente pas 5go de données, ni lui, ni la table. Je ne comprends pas comment cette seule transaction utilise les 5go du journal ?

As tu pensé à inverser la logique de mise à jour de ta table ?
Tu dis que tu as environ 99% de données à supprimer. Alors dans ce cas tu pourrais copier les 1% de données non supprimés dans une table, d'opérer ensuite un TRUNCATE (journalisation mimime) et enfin de restaurer les données sauvegardés dans la table ...
++

C'est ce que l'on pense à faire, mais l'idée est de pouvoir conserver un processus générique et au plus simple. Ce n'est qu'une petite partie de l'ensemble de notre processus.


Merci pour vos idées.

[mikedavem]

Ton DBA ne te simplifie pas la tâche ... et par conséquent restreint tes possibilités

Il faut voir la quantité de données que tu supprimes car cela peut être tout à fait normal que ton journal grossisse de cette manière. Il faut bien enregistrer dans le journal tout ce qui est fait par ta suppression (Suppression des données, mise à jour de l'index, verrous posés etc ....) en cas de rollback par exemple.
De plus vu que tu fais cela en une seule transaction, ce qui ne te permet pas de vider le journal au fur et à mesure ...

++

[SQLpro]

en 2005 vous pouvez boucler sur un DELETE TOP 100000... suivi d'un CHECKPOINT pour forcer la libération du journal, jusqu'à ce que @@ROWCOUNT = 0

Quelque chose comme :

Code :

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

1
2
3
4
5
6
7
WHILE EXISTS(SELECT * FROM MaTable WHERE ???)
BEGIN
   DELETE TOP 100000 FROM MaTable
   WHERE ???;
   IF @@ROWCOUNT = 0 BREAK;
   CHECKPOINT;
END;

A +

[Jinroh77]

Pratique ce "delete top"
Avec des parenthèses autour de la valeur du top et c'est parfait.
Je peux supprimer mes millions de lignes sans trop modifier mes process.

Extra

[mikedavem]

Je ne peux pas utiliser de truncate et boucler sur un nombre de ligne, pour supprimer par partie, me dérange particulièrement

Je croyais que cette solution ne t'allait pas ??

++

[Jinroh77]

Je n'avais pas en tête un delete top de cette forme et pensait devoir passer par une boucle plus complexe que cela.

J'aurai du préciser.

[mikedavem]

Pas grave, c'était juste au moins pour savoir si nous nous avions bien compris ..

Bonne suppression en tout cas