Blog

DELETE ou UPDATE en masse (patterns)


Contexte

Le temps de maintenance des index/stats est exponentiel avec plus récemment une fâcheuse tendance à déborder sur les plages de travail. Quand on y regarde de plus près, il y a une table qui sort clairement du lot. On l’avait oublié la “tablounette” des débuts qui a bien grandit depuis. Il est loin le temps des mistrals gagnants où on lui racontait une histoire pour qu’elle puisse s’endormir le soir… Aujourd’hui, il faut compter plusieurs heures pour effectuer des réorganisations d’index sur la table concernée… Voyons voir, combien il y a de lignes… Après le milliard de lignes, c’est quoi ? Ou plutôt après le billion ? Ha quand même ! La question de la rétention, personne ne se l’est vraiment posée. Pour ce qui est d’une purge, quand on a commencé à y penser, c’était en mode procrastination. Le jour où on se décide finalement à la lancer, double effet Kiss Cool : la PROD s’écroule sous le feu d’un DELETE sans fin. Voici quelques patterns pour vous assister dans cette tâche laborieuse.

Expert SQL Server - DELETE ou UPDATE en masse (patterns) - SQL Server  - FwtnR0C

Problématique récurrente

  • On a une table qui contient 500 000 000 000 (…) lignes sur laquelle on doit impérativement lancer une purge. Comment on procède pour ne pas pénaliser la PROD ?

Les questions à se poser

  • Est-ce que la volumétrie à conserver est supérieur à la volumétrie à purger ?

Expert SQL Server - DELETE ou UPDATE en masse (patterns) - SQL Server  - delete1

  • Est-ce qu’il y a des dépendances ?

Expert SQL Server - DELETE ou UPDATE en masse (patterns) - SQL Server  - dependencies

  • Est-ce que l’indexation est suffisamment couvrante ?

Expert SQL Server - DELETE ou UPDATE en masse (patterns) - SQL Server  - index

Création d’une base test

En premier lieu, on cré une base lambda contenant 2 tables, dont une dans laquelle on injecte des données générées aléatoirement, celle à purger.

Options de la purge

Nous utiliserons 3 patterns permettant de gérer les éléments suivant :

  1. Appliquer une rétention (@RetentionDays d’où découle @MaxDateToDelete)
  2. Limiter le traitement dans le temps (@MaxDurationMinutes d’où découle @MaxDurationTime)
  3. Supprimer les données par paquet pour limiter les effets du locking (@DeletePackageSize)
  4. Faire une pause entre chaque paquet de données à supprimer dans le même esprit que le point précédent (WAITFOR DELAY)
  5. Recourir à la commande SWITCH en fonction du volume de données à purger

Pattern 1

La volumétrie à conserver est supérieur au volume de données à purger et il n’y a aucune dépendance (FK) liée à la table à nettoyer

Pattern 2

La volumétrie à conserver est supérieur au volume de données à purger et il y a des dépendances (FK) liée à la table à nettoyer. On va donc passer par une table temporaire pour isoler les données à purger et les matcher avec les dépendances de la table principale. La création de la base n’inclue pas ces dépendances (RandomDataDepend1, RandomDataDepend2, RandomDataDepend3), on va donc faire comme si elles existaient…

Pattern 3

La volumétrie à conserver est inférieur au volume de données à purger et il n’y a pas de dépendances (FK) liée à la table à nettoyer. On aura recours à un SWITCH qui contrairement à une idée reçue n’est pas réservé aux tables partitionnées. Le principe est de transférer la totalité des données de la table principale vers une autre et de procéder à un INSERT des données à conserver dans la table principale. Le but étant de diminuer le volume de données à traiter et donc la durée de traitement.

Attention : La structure des tables source et destination doit être strictement identique et la destination doit être vide.

L’option INSERT_IDENTITY est utilisée dans le cas présent pour forcer l’insertion dans une colonne IDENTITY.

Une réindexation pourra être lancée juste après la purge.

Pour conclure, cette logique de suppression peut aussi s’appliquer pour une mise à jour en masse.

Auteur

Expert SQL Server - DELETE ou UPDATE en masse (patterns) - SQL Server  - avatar_ninja_tete-150x150
Sarah Béquet
Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
error: