SQL SERVER: Calculate optimum batch size for nibbling deletes based on rows per page

CalculateNibblingDeletesBatchSize.sql

-- Calculate optimal batch size based on actual rows per page
DECLARE @RowsPerPage INT
DECLARE @MaxLocks INT = 4000  -- 80% of 5000 threshold
DECLARE @OptimalBatchSize INT

-- Get rows per page
SELECT @RowsPerPage = 
    CASE 
        WHEN SUM(used_page_count) > 0 
        THEN SUM(row_count) / SUM(used_page_count)
        ELSE 100  -- Default assumption
    END
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.TableName')
    AND index_id IN (0, 1)

-- Calculate optimal batch size
SET @OptimalBatchSize = @RowsPerPage * @MaxLocks

SELECT 
    @RowsPerPage AS RowsPerPage,
    @MaxLocks AS MaxPageLocksToAvoidEscalation,
    FORMAT(@OptimalBatchSize, 'N0') AS OptimalBatchSize,
    'Delete up to ' + FORMAT(@OptimalBatchSize, 'N0') + ' rows per batch' AS Recommendation