0

I am going to migrate a large amount of data from one table to other table. The target data source table has foreign key constrains, so I am not able to use DELETE with OUTPUT clause, but need to have a buffer table instead.

The data source table is not going to be read by other users, but the new table (in which I am inserting records will be frequently used during the operation) - so, I need to do the migration without blocking the other users activity.

The database is under FULL recovery model, SQL Server 2012 and transaction log backup is made on every 15 minutes (the transaction log growth during the operation is not an issue anyway).

I have the following T-SQL statement:

CREATE TABLE #DataBuffer
(
    ....
);

DECLARE @RowCount INT;

WHILE 1 = 1 
BEGIN 

    BEGIN TRY

          BEGIN TRANSACTION

            TRUNCATE TABLE #DataBuffer;

            DELETE TOP (4000) 
            FROM [dbo].[OldSourceTable]
            OUTPUT [deleted]. ...
            INTO #DataBuffer (...)
            WHERE ... ;

            INSERT INTO [dbo].[NewSourceTable] (...)
            SELECT ...
            FROM #DataBuffer;

            SET @RowCount = @@ROWCOUNT;

            COMMIT TRANSACTION

            IF @RowCount = 0 
            BEGIN
                BREAK
            END

    END TRY

    BEGIN CATCH 

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION
        END

    END CATCH

END

and I am wondering which is the best value of rows to be deleted in a batch delete statement?

I have seen different variants and the only thing I have found as recommendation was from here:

If you delete 4000 or more rows in a single command, the whole table will be locked (or the whole partition if you enabled that on partitioned table). DO NOT delete 10000 rows in a batch if the table needs to be available for other users! So, delete e.g. 2000 rows in a single delete...

But I was not able to find a confirmation in the MSDN or elsewhere about this.

  • The data source table has foreign key constrains, so I am not able to use DELETE with OUTPUT clause This is not correct. The target table cannot have a foreign key, but the source table can. – Mark Sinkinson Apr 20 '15 at 9:36
  • @MarkSinkinson Yes, I am sorry - it is the target table. – gotqn Apr 20 '15 at 9:39
  • Your edit has now broken your script as you'll be breaking out of the WHILE loop without committing your transaction. – Mark Sinkinson Apr 20 '15 at 9:42
  • MS says that Lock Escalation occurs at 5000 records: msdn.microsoft.com/en-us/library/ms184286.aspx (look at: Lock Escalation Thresholds section). I would keep below that (please note, page is for 2008R2). Some similar discussion: stackoverflow.com/questions/4679222/… – MHSQLDBA Apr 20 '15 at 9:46
1

Note that regardless of whether you trigger a table lock, a 1000+ record INSERT transaction will lock many pages in your indexes, and has a high risk of causing deadlocking or other problems. The good news is that your post does not seem to provide a business case for taking on a such a large transactional operation.

The data source table is not going to be read by other users...

Is it crucial for the business to ensure that a large block records has been transferred in a large batch, such that anyone accessing the source table will not see them at the exact point in time anyone access the target time will be able to see them? Or, is it necessary for users of the target table to see large blocks of records appear in the target table all exactly at the same time?

If no to both of those, I second Spörri's below statement...

If you have a clustered index on an identity column, which is not fragmented and you are deleting records from a range thats old you can safely ask the engine to lock the extends that the old records are using and delete ~64Kb at a time without fearing of blocking readers.

Now 64KB is not a very large value. If your records are each 1000 bytes, that means you are talking at most 64 records. As such, I recommend you do these transactions one record at a time. If the migration terminates on an error, one of these records will get rolled back, and you can certainly pick up where you left off because the source table and target table will be in alignment. This will ensure maximum availability for your users.

1

The only correct answer here is "It Depends". There are so many factors that come into play that you will just have do many runs with different parameters try to find what are the optimal settings for your system, and you can also play with locking hints if your table has indexes that are optimal for the search condition.

There are some rules of thumb.. if you have a clustered index on an identity column, which is not fragmented and you are deleting records from a range thats old you can safely ask the engine to lock the extends that the old records are using and delete ~64Kb at a time without fearing of blocking readers. This can help you but is not exact science and offrow data will make this different:

SELECT OBJECT_NAME(i.OBJECT_ID) AS tableName
, i.name AS indexName
, SUM(p.row_count) AS rows
, SUM(p.in_row_data_page_count) AS dataPages
, SUM(p.row_count) / SUM(p.in_row_data_page_count) AS rowsPerPage
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS p
ON i.OBJECT_ID = p.OBJECT_ID
And i.index_id = p.index_id
WHERE OBJECT_NAME(i.OBJECT_ID) Not Like 'sys%' and i.type = 1
GROUP BY OBJECT_NAME(i.OBJECT_ID)
, i.name
, i.type_desc
HAVING SUM(p.in_row_data_page_count) > 100
ORDER BY rowsPerPage;

This will give you average number of rows in the clustered index. You are safe to lock on a single page which will be fast and give you good concurrency. That number times 8 will most likely give you an EXTENT lock with the same benefits and after that you'll have to play with the numbers to see when you will start to lock the table. Also it helps to have READ_COMMITED_SNAPSHOT active.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.