SQL Server : suppression et mise à jour par lots avec commit en boucle
Modèles T-SQL pour purger ou modifier de grosses tables par lots, avec commit, pause et suivi de progression.
Purger ou mettre à jour des millions de lignes dans une seule transaction fait gonfler le journal de transactions et maintient des verrous longtemps. Cet article présente des modèles T-SQL qui travaillent par petits lots, valident (commit) après chaque lot, attendent brièvement entre deux lots et affichent la progression, ainsi que des requêtes pour suivre l'avancement.
Suppression par lots avec commit à chaque boucle
Supprime les lignes plus anciennes qu'une date limite dans une table de logs, 100 000 lignes à la fois, jusqu'à ce qu'il n'en reste plus. Une cascade est configurée depuis la table parente vers dbo.CategoryLog : les lignes enfants sont donc supprimées automatiquement.
Declare @iMonth int
DECLARE @tInteraction TABLE (ref_Id INT ,interaction_Id CHAR(21))
DECLARE @tTransaction TABLE (interaction_Id char(21),transaction_Id int)
DECLARE @iIteration INT
DECLARE @iRowCount INT
DECLARE @sMessage VARCHAR(200)
DECLARE @start_time DATETIME
DECLARE @end_time DATETIME
DECLARE @elasted_time INT
SET @iIteration = 0;
SET @end_time = DATEADD(hour,-3,SYSUTCDATETIME())
PRINT @end_time
SET @iRowCount = 1;
WHILE ( @iRowCount != 0 )
BEGIN
SET @iMonth = -1 /* Do not forget the minus */
BEGIN TRAN
SET @iIteration = @iIteration + 1
SET @sMessage = '@iIteration ' + CAST(@iIteration AS VARCHAR);
RAISERROR (@sMessage, 0, 1) WITH NOWAIT
SET @start_time = GETDATE()
SET @iRowCount = 0
;WITH CTE AS
(
SELECT TOP
100000 *
FROM
[dbo].[Log]
WHERE
timestamp < @end_time
ORDER BY LogID ASC
)
DELETE FROM CTE
SET @iRowCount= @iRowCount + @@RowCount
--CategoryLog
/*
a cascade has been configured
select count(*)
from dbo.CategoryLog cl
where not exists ( select [LogID] from [dbo].[Log] l where l.LogID = cl.LogID)
delete dbo.CategoryLog
from dbo.CategoryLog cl
where not exists ( select [LogID] from [dbo].[Log] l where l.LogID = cl.LogID)
*/
COMMIT
WAITFOR DELAY '00:00:05';
SELECT @elasted_time= RTRIM(CAST(DATEDIFF(MS, @start_time, GETDATE()) AS CHAR(10)))
SET @sMessage = '@iRowCount ' + CAST(@iRowCount AS VARCHAR) + ' Elapsed time : '+ CAST( @elasted_time AS VARCHAR);
RAISERROR (@sMessage, 0, 1) WITH NOWAIT
END
GO
RAISERROR ... WITH NOWAIT envoie le message immédiatement : la progression est visible dans SSMS pendant l'exécution. Le WAITFOR DELAY entre deux lots laisse les autres sessions et les sauvegardes du journal travailler.
Vérifier l'absence de lignes enfants orphelines après la purge :
select count(*)
from dbo.CategoryLog cl
where not exists (select [LogID] from [dbo].[Log] l where l.LogID = cl.LogID)
Suivre la progression
Nombre de lignes via les statistiques de partition (rapide, sans parcourir la table) :
SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('CategoryLog')
AND index_id < 2
GROUP BY OBJECT_NAME(object_id);
SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Log')
AND index_id < 2
GROUP BY OBJECT_NAME(object_id);
Entrées la plus ancienne et la plus récente, et lignes par catégorie :
select min([timeStamp]),max([timeStamp]),count(*)
from dbo.log;
SELECT count(*), CategoryName
FROM [AppLoggingDb].[dbo].[Log] l
inner join [dbo].[CategoryLog] cl on l.[LogID] = cl.[LogID]
inner join [dbo].[Category] c on c.[CategoryID] = cl.[CategoryID]
group by CategoryName
Volume par jour et par heure :
SELECT CAST(timestamp as date) AS ForDate,
DATEPART(hour,timestamp) AS OnHour,
COUNT(*) AS Totals
FROM [AppLoggingDb].[dbo].[Log]
GROUP BY CAST(timestamp as date),
DATEPART(hour,timestamp)
order by ForDate, OnHour
Enregistrer le nombre de lignes dans le temps
Créer une table qui conserve le nombre de lignes à intervalle régulier :
create table dbo.increaseLog (id int identity(1,1) primary key not null, SchemaName varchar(1000), TableName varchar(1000), timeStamp datetime2)
ALTER TABLE dbo.increaseLog ADD count bigint NULL
Boucle de collecte (un échantillon par minute pendant 300 minutes) :
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
DECLARE @minute int
SET @StartDate = GETDATE()
SET @minute = 300
SET @EndDate = DATEADD(Minute,@minute,getdate())
SET @CurrentDate = @StartDate
WHILE (@CurrentDate < @EndDate)
BEGIN
insert into dbo.increaseLog (SchemaName,TableName,timeStamp,count)
SELECT
s.name AS 'SchemaName'
,o.name AS 'TableName'
,getdate()
,SUM(p.row_count) AS 'RowCount'
FROM sys.dm_db_partition_stats p
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE p.index_id <2 AND o.type = 'U'
AND s.name = 'dbo'
AND o.name = 'Log'
GROUP BY s.name,o.name
ORDER BY s.name,o.name;
WAITFOR DELAY '00:01:00';
RAISERROR('loop', 0, 1) WITH NOWAIT;
set @CurrentDate = getdate();
END
Lire l'évolution (secondes entre deux échantillons et lignes ajoutées ou supprimées) :
SELECT
[timeStamp]
,DATEDIFF(second,[timeStamp], lag([timeStamp],1,getdate()) OVER (ORDER BY id desc) ) as second
,[count] as tableCount
,( lag([count],1,0) OVER (ORDER BY id desc)-[count] ) AS NewRows
FROM [AppLoggingDb].[dbo].[increaseLog]
order by [id] desc
Mise à jour par lots avec curseur et commit à chaque lot
Même principe pour un UPDATE : ici, suppression des espaces de fin d'une colonne identifiant, 10 000 lignes à la fois, pour chaque valeur distincte sélectionnée par un curseur.
DECLARE @BatchSize INT = 10000;
DECLARE @RowCount INT;
DECLARE @Iteration INT;
DECLARE @StartTime DATETIME;
DECLARE @ElapsedTime INT;
DECLARE @Message VARCHAR(200);
DECLARE @CurrentId VARCHAR(50); -- Adjust the data type according to your schema
-- Declare a cursor to iterate through each distinct identifier
DECLARE IdCursor CURSOR FOR
SELECT DISTINCT EntityId
FROM dbo.[LinkedEntity]
WHERE EntityId LIKE '% ' and ltrim(rtrim(EntityId)) in ('12345'); -- Add your filtering criteria if necessary
OPEN IdCursor;
FETCH NEXT FROM IdCursor INTO @CurrentId;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Iteration = 0;
SET @RowCount = 1; -- Initialize to a value greater than 0 to enter the loop
RAISERROR ('Processing EntityId: %s', 0, 1, @CurrentId) WITH NOWAIT;
-- Batch processing for the current identifier
WHILE (@RowCount > 0)
BEGIN
BEGIN TRAN;
SET @Iteration = @Iteration + 1;
SET @Message = 'EntityId: ' + @CurrentId + ' | Iteration: ' + CAST(@Iteration AS VARCHAR);
RAISERROR (@Message, 0, 1) WITH NOWAIT;
SET @StartTime = GETDATE();
-- Update in batches of @BatchSize rows for the current identifier
WITH CTE AS
(
SELECT TOP (@BatchSize) *
FROM dbo.[LinkedEntity]
WHERE EntityId = @CurrentId
AND EntityId LIKE '% ' -- Additional conditions if necessary
)
UPDATE CTE
SET EntityId = LTRIM(RTRIM(EntityId));
SET @RowCount = @@ROWCOUNT;
COMMIT;
SET @ElapsedTime = DATEDIFF(MILLISECOND, @StartTime, GETDATE());
SET @Message = 'Rows updated: ' + CAST(@RowCount AS VARCHAR)
+ ' | Elapsed time: ' + CAST(@ElapsedTime AS VARCHAR) + ' ms';
RAISERROR (@Message, 0, 1) WITH NOWAIT;
-- Wait for a short delay to reduce contention
WAITFOR DELAY '00:00:05';
END
FETCH NEXT FROM IdCursor INTO @CurrentId;
END
CLOSE IdCursor;
DEALLOCATE IdCursor;
RAISERROR ('Processing completed for all identifiers.', 0, 1) WITH NOWAIT;
Remarques
- Ajuster la taille des lots (100 000 pour la suppression, 10 000 pour la mise à jour) et le délai selon le débit du journal et le niveau de contention.
- Les noms de tables, colonnes et bases ci-dessus sont des exemples génériques à adapter à votre schéma.