LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon