Delete کردن ایمن و انبوه داده SQL Server با Stored Procedure

Delete ایمن و کارآمد داده‌ها در SQL Server با رویه‌های ذخیره‌شده

یکی از چالش‌های رایج در مدیریت پایگاه داده SQL Server، حذف حجم زیادی از داده‌ها به صورت امن و بدون تأثیر منفی بر عملکرد سیستم است. دستور DELETE استاندارد می‌تواند مشکلاتی از جمله قفل شدن جداول، مصرف بیش از حد لاگ تراکنش (Transaction Log) و کندی عملکرد را ایجاد کند، به‌ویژه اگر بدون عبارت WHERE استفاده شود که منجر به حذف تمام رکوردها می‌شود.

حذف کردن حجم زیادی از داده‌ها با یک دستور DELETE می‌تواند باعث پر شدن لاگ تراکنش شود و حتی اگر عبارت TOP برای محدود کردن تعداد رکوردها استفاده شود، باز هم این عمل به عنوان یک تراکنش واحد ثبت می‌شود که می‌تواند باعث مشکلات عملکردی و افزایش حجم لاگ شود.

برای حل این مشکل، می‌توان از یک رویه ذخیره‌شده (Stored Procedure) استفاده کرد که فرآیند حذف را به بسته‌های کوچک‌تر (batches) تقسیم می‌کند. این رویکرد تضمین می‌کند که حذف داده‌ها به صورت کنترل‌شده و با کمترین تأثیر بر منابع سیستم انجام شود. رویه ذخیره‌شده‌ای که در ادامه ارائه می‌شود، داده‌ها را در یک حلقه (loop) و به صورت گام به گام حذف می‌کند، که به جلوگیری از پر شدن ناگهانی لاگ تراکنش و کاهش قفل شدن منابع کمک می‌کند.

این رویه ذخیره‌شده که usp_SafeDelete نام دارد، به شما امکان می‌دهد تا حذف داده‌ها را با پارامترهای قابل تنظیم از جمله اندازه بسته (Batch Size)، حداکثر تعداد ردیف‌ها برای حذف و زمان انتظار بین بسته‌ها مدیریت کنید. این روش به شما اجازه می‌دهد تا عملیات حذف را در ساعات کم‌ترافیک اجرا کنید و از تحمیل بار اضافی بر سرور جلوگیری نمایید.

در اینجا کد کامل رویه ذخیره‌شده usp_SafeDelete آورده شده است:

CREATE PROCEDURE usp_SafeDelete
    @TableName sysname,
    @PrimaryKeyColumn sysname,
    @BatchSize INT = 10000,
    @MaxRows INT = NULL,
    @WaitTime SMALLINT = 500,
    @PrintOnly BIT = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @RowCount INT = 0;
    DECLARE @TotalRowsDeleted INT = 0;
    DECLARE @DeleteCount INT = 0;
    DECLARE @Start datetime = GETDATE();

    IF @PrintOnly = 1
    BEGIN
        PRINT '--- PrintOnly Mode Active ---';
        PRINT 'Parameters:';
        PRINT '  @TableName = ' + @TableName;
        PRINT '  @PrimaryKeyColumn = ' + @PrimaryKeyColumn;
        PRINT '  @BatchSize = ' + CAST(@BatchSize AS VARCHAR(10));
        PRINT '  @MaxRows = ' + ISNULL(CAST(@MaxRows AS VARCHAR(10)), 'NULL');
        PRINT '  @WaitTime = ' + CAST(@WaitTime AS VARCHAR(10));
        PRINT '';
    END

    -- Check if table exists
    IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = @TableName)
    BEGIN
        RAISERROR('Table %s does not exist.', 16, 1, @TableName);
        RETURN;
    END

    -- Check if primary key column exists
    IF NOT EXISTS (
        SELECT 1
        FROM sys.columns c
        INNER JOIN sys.tables t ON c.object_id = t.object_id
        WHERE t.name = @TableName AND c.name = @PrimaryKeyColumn
    )
    BEGIN
        RAISERROR('Primary key column %s does not exist in table %s.', 16, 1, @PrimaryKeyColumn, @TableName);
        RETURN;
    END

    -- Get initial row count
    SET @SQL = N'SELECT @RowCount = COUNT(*) FROM ' + QUOTENAME(@TableName);
    EXEC sp_executesql @SQL, N'@RowCount INT OUTPUT', @RowCount OUTPUT;

    IF @RowCount = 0
    BEGIN
        PRINT 'Table ' + QUOTENAME(@TableName) + ' is already empty. No rows to delete.';
        RETURN;
    END

    PRINT 'Starting delete operation for table ' + QUOTENAME(@TableName) + ' with ' + CAST(@RowCount AS VARCHAR(50)) + ' rows.';
    PRINT 'Batch Size: ' + CAST(@BatchSize AS VARCHAR(10)) + ', Wait Time: ' + CAST(@WaitTime AS VARCHAR(10)) + 'ms';
    IF @MaxRows IS NOT NULL
        PRINT 'Max Rows to Delete: ' + CAST(@MaxRows AS VARCHAR(10));

    WHILE 1 = 1
    BEGIN
        SET @SQL = N'
        DELETE TOP (' + CAST(@BatchSize AS NVARCHAR(10)) + ') FROM ' + QUOTENAME(@TableName) + '
        WHERE ' + QUOTENAME(@PrimaryKeyColumn) + ' IN (SELECT TOP (' + CAST(@BatchSize AS NVARCHAR(10)) + ') ' + QUOTENAME(@PrimaryKeyColumn) + ' FROM ' + QUOTENAME(@TableName) + ' ORDER BY ' + QUOTENAME(@PrimaryKeyColumn) + ');';

        IF @PrintOnly = 1
        BEGIN
            PRINT N'Executing (PrintOnly): ' + @SQL;
            BREAK; -- Exit loop in PrintOnly mode after showing one command
        END

        EXEC sp_executesql @SQL;
        SET @DeleteCount = @@ROWCOUNT;
        SET @TotalRowsDeleted = @TotalRowsDeleted + @DeleteCount;

        IF @DeleteCount = 0
        BEGIN
            PRINT 'No more rows to delete. Exiting loop.';
            BREAK;
        END

        PRINT 'Deleted ' + CAST(@DeleteCount AS VARCHAR(10)) + ' rows. Total deleted: ' + CAST(@TotalRowsDeleted AS VARCHAR(10)) + '.';

        IF @MaxRows IS NOT NULL AND @TotalRowsDeleted >= @MaxRows
        BEGIN
            PRINT 'Maximum rows (' + CAST(@MaxRows AS VARCHAR(10)) + ') deleted. Exiting loop.';
            BREAK;
        END

        WAITFOR DELAY '00:00:0.' + RIGHT('00' + CAST(@WaitTime AS VARCHAR(3)), 3);
    END

    PRINT 'Delete operation completed in ' + CAST(DATEDIFF(second, @Start, GETDATE()) AS VARCHAR(10)) + ' seconds.';
    PRINT 'Total rows deleted: ' + CAST(@TotalRowsDeleted AS VARCHAR(10)) + '.';

END;

این رویه ذخیره‌شده (Stored Procedure) برای حذف ایمن و تدریجی داده‌ها طراحی شده است. در ادامه توضیح هر یک از پارامترهای آن آورده شده است:

• @TableName: نام جدولی که قصد حذف داده‌ها از آن را دارید. این پارامتر از نوع sysname است.

• @PrimaryKeyColumn: نام ستون کلید اصلی (Primary Key) جدول مورد نظر. این ستون برای انتخاب بهینه ردیف‌ها در هر بسته حذف استفاده می‌شود. این پارامتر نیز از نوع sysname است.

• @BatchSize: تعداد ردیف‌هایی که در هر مرحله از عملیات حذف، پاک می‌شوند. مقدار پیش‌فرض آن 10000 ردیف است. با تنظیم این مقدار می‌توانید میزان بار (load) هر تراکنش را کنترل کنید.

• @MaxRows: حداکثر تعداد کل ردیف‌هایی که باید حذف شوند. اگر این پارامتر NULL باشد، رویه ذخیره‌شده تا زمانی که هیچ ردیف دیگری برای حذف باقی نماند، به کار خود ادامه می‌دهد.

• @WaitTime: مدت زمان انتظار (به میلی‌ثانیه) بین هر عملیات حذف بسته (batch delete). مقدار پیش‌فرض 500 میلی‌ثانیه است. این وقفه به کاهش فشار بر سیستم و جلوگیری از قفل شدن منابع کمک می‌کند.

• @PrintOnly: یک پرچم BIT که اگر روی 1 تنظیم شود، رویه ذخیره‌شده فقط دستور DELETE را چاپ می‌کند و آن را اجرا نمی‌کند. این برای آزمایش و مشاهده دستورات قبل از اجرای واقعی مفید است.

مثال‌های کاربردی برای استفاده از رویه ذخیره‌شده usp_SafeDelete:

در اینجا نحوه فراخوانی رویه ذخیره‌شده برای حذف داده‌ها از جدول dbo.MyTable با کلید اصلی MyTableID آورده شده است. این دستور 50000 ردیف را در بسته‌های 10000 تایی، با 200 میلی‌ثانیه تأخیر بین هر بسته حذف می‌کند:

EXEC usp_SafeDelete
    @TableName = 'dbo.MyTable',
    @PrimaryKeyColumn = 'MyTableID',
    @BatchSize = 10000,
    @MaxRows = 50000,
    @WaitTime = 200;

مثالی دیگر برای حذف تمام داده‌ها از جدول dbo.AnotherTable با کلید اصلی AnotherTableID در بسته‌های پیش‌فرض و بدون محدودیت در تعداد ردیف‌ها:

EXEC usp_SafeDelete
    @TableName = 'dbo.AnotherTable',
    @PrimaryKeyColumn = 'AnotherTableID';

استفاده از حالت فقط چاپ (PrintOnly) برای مشاهده دستورات DELETE قبل از اجرای واقعی:

EXEC usp_SafeDelete
    @TableName = 'dbo.MyTable',
    @PrimaryKeyColumn = 'MyTableID',
    @PrintOnly = 1;