How many records should be deleted when batch delete is used? 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 &gt; 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 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) &gt; 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.