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.
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 ?
- Est-ce qu’il y a des dépendances ?
- Est-ce que l’indexation est suffisamment couvrante ?
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
CREATE DATABASE [SBE] GO USE [SBE] GO CREATE TABLE [dbo].[RandomData1]( [RowId] [int] IDENTITY(1,1) NOT NULL, [SomeInt] [int] NULL, [SomeBit] [bit] NULL, [SomeVarchar] [varchar](10) NULL, [SomeDateTime] [datetime] NULL, [SomeNumeric] [numeric](16, 2) NULL, [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_RandomData1_CreateDate] DEFAULT (getdate()), [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_RandomData1_UpdateDate] DEFAULT (getdate()), CONSTRAINT [PK_RandomData1] PRIMARY KEY CLUSTERED ( [RowId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [dbo].[RandomData1] (SomeInt, SomeBit, SomeVarchar, SomeDateTime, SomeNumeric) SELECT TOP 1000000 ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, ABS(CHECKSUM(NEWID()) % 10) % 2 [SomeBit], CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) [SomeVarchar], DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1, DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) [SomeDateTime], (ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) [SomeNumeric] FROM Master.dbo.SysColumns t1, Master.dbo.SysColumns t2 GO CREATE TABLE [dbo].[RandomData2]( [RowId] [int] IDENTITY(1,1) NOT NULL, [SomeInt] [int] NULL, [SomeBit] [bit] NULL, [SomeVarchar] [varchar](10) NULL, [SomeDateTime] [datetime] NULL, [SomeNumeric] [numeric](16, 2) NULL, [CreateDate] [datetime] NOT NULL, [UpdateDate] [datetime] NOT NULL, CONSTRAINT [PK_RandomData2] PRIMARY KEY CLUSTERED ( [RowId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO |
Options de la purge
Nous utiliserons 3 patterns permettant de gérer les éléments suivant :
- Appliquer une rétention (@RetentionDays d’où découle @MaxDateToDelete)
- Limiter le traitement dans le temps (@MaxDurationMinutes d’où découle @MaxDurationTime)
- Supprimer les données par paquet pour limiter les effets du locking (@DeletePackageSize)
- Faire une pause entre chaque paquet de données à supprimer dans le même esprit que le point précédent (WAITFOR DELAY)
- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
USE SBE GO DECLARE @RetentionDays INT = 365, @DeletePackSize INT = 100000, @MaxDurationMinutes INT = 5, @MaxDateToDelete DATETIME, @MaxDurationTime DATETIME SET @MaxDateToDelete = DATEADD(DAY, (-1 * @RetentionDays), GETUTCDATE()) SET @MaxDurationTime = DATEADD(MINUTE, @MaxDurationMinutes, GETUTCDATE()) --SELECT @MaxDateToDelete, @MaxDurationTime WHILE (EXISTS (SELECT TOP 1 1 FROM [dbo].[RandomData1] WHERE SomeDateTime <= @MaxDateToDelete) AND @MaxDurationTime > GETUTCDATE()) BEGIN BEGIN TRAN DELETE TOP (@DeletePackSize) FROM [dbo].[RandomData1] WHERE SomeDateTime <= @MaxDateToDelete IF @@ERROR > 0 ROLLBACK TRAN ELSE COMMIT TRAN WAITFOR DELAY '00:00:10' END |
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…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
USE SBE GO DECLARE @RetentionDays INT = 365, @DeletePackSize INT = 100000, @MaxDurationMinutes INT = 5, @MaxDateToDelete DATETIME, @MaxDurationTime DATETIME, @Continue BIT = 1 SET @MaxDateToDelete = DATEADD(DAY, (-1 * @RetentionDays), GETUTCDATE()) SET @MaxDurationTime = DATEADD(MINUTE, @MaxDurationMinutes, GETUTCDATE()) --SELECT @MaxDateToDelete, @MaxDurationTime WHILE (@Continue = 1 AND @MaxDurationTime > GETUTCDATE()) BEGIN IF OBJECT_ID('tempdb..#DataToDelete') IS NOT NULL DROP TABLE #DataToDelete SELECT TOP (@DeletePackSize) RowId INTO #DataToDelete FROM [dbo].[RandomData1] WHERE SomeDateTime <= @MaxDateToDelete SET @Continue = IIF(@@ROWCOUNT > 0, 1, 0) --SET @Continue = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END BEGIN TRAN -- Delete dependency : part 1 DELETE d2 FROM #DataToDelete d1 INNER JOIN [dbo].[RandomDataDepend1] d2 ON d1.RowId = d2.RowId -- Delete dependency : part 2 DELETE d2 FROM #DataToDelete d1 INNER JOIN [dbo].[RandomDataDepend2] d2 ON d1.RowId = d2.RowId -- Delete dependency : part 3 DELETE d2 FROM #DataToDelete d1 INNER JOIN [dbo].[RandomDataDepend3] d2 ON d1.RowId = d2.RowId -- Delete from final table DELETE d2 FROM #DataToDelete d1 INNER JOIN [dbo].[RandomData1] d2 ON d1.RowId = d2.RowId IF @@ERROR > 0 ROLLBACK TRAN ELSE COMMIT TRAN WAITFOR DELAY '00:00:10' END |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
USE SBE GO DECLARE @RetentionDays INT = 365, @MaxDateToDelete DATETIME SET @MaxDateToDelete = DATEADD(DAY, (-1 * @RetentionDays), GETUTCDATE()) IF EXISTS (SELECT TOP 1 1 FROM [dbo].[RandomData1] WHERE SomeDateTime > @MaxDateToDelete) BEGIN ALTER TABLE [dbo].[RandomData1] SWITCH TO [dbo].[RandomData2] BEGIN TRAN SET IDENTITY_INSERT [dbo].[RandomData1] ON INSERT INTO [dbo].[RandomData1] (RowId, SomeInt, SomeBit, SomeVarchar, SomeDateTime, SomeNumeric, CreateDate, UpdateDate) SELECT RowId, SomeInt, SomeBit, SomeVarchar, SomeDateTime, SomeNumeric, CreateDate, UpdateDate FROM [dbo].[RandomData2] WHERE SomeDateTime > @MaxDateToDelete SET IDENTITY_INSERT [dbo].[RandomData1] OFF TRUNCATE TABLE [dbo].[RandomData2] IF @@ERROR > 0 ROLLBACK TRAN ELSE COMMIT TRAN --DBCC DBREINDEX ('dbo.RandomData1'); END |
Pour conclure, cette logique de suppression peut aussi s’appliquer pour une mise à jour en masse.
Auteur
- Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
Derniers articles
- 2019.12.02BI & Big DataEtude des contextes en DAX (4/4) : Transition de contexte
- 2019.11.25BI & Big DataEtude des contextes en DAX (3/4) : Contexte de requête
- 2019.11.17BI & Big DataEtude des contextes en DAX (2/4) : Contexte de filtre
- 2019.11.11BI & Big DataEtude des contextes en DAX (1/4) : Contexte de ligne