I need to delete a lot of users and users data, and in hangfire I created a job that enqueues 5 jobs that each execute stored procedure and passes Ids for deletion(batches of 100):

for (int i = 0; i < 5; i++){
     var batch = peopleForCleanup.Skip(batchSize * i).Take(batchSize).ToList();
     backgroundJobClient.Enqueue(() => PeopleCleanupAsync(batch));
     }

This is the code that is calling stored procedure:

    await using (var con = new SqlConnection(appConString))
                        await using (var cmd = new SqlCommand())
                        {
                            cmd.Connection = con;
                            await con.OpenAsync();
                            cmd.CommandTimeout = 3600; // 30min
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = "DeletePersonData";
                            // Define TVP parameter
                            var tvpParam = new SqlParameter("@PersonIds", SqlDbType.Structured)
                            {
                                TypeName = "dbo.PersonIdTableType",
                                Value = personIdTable
                            };
                            cmd.Parameters.Add(tvpParam);
                            var errorParam = new SqlParameter("@Errors", SqlDbType.NVarChar, int.MaxValue)
                            {
                                Direction = ParameterDirection.Output
                            };
                            cmd.Parameters.Add(errorParam);
                            Stopwatch sw = Stopwatch.StartNew();
                            await cmd.ExecuteNonQueryAsync();
                            sw.Stop();
                            Console.WriteLine("Task = " + Thread.CurrentThread.ManagedThreadId + " TIME == " + sw.Elapsed.ToString());
                            errors = Convert.ToString(errorParam.Value);
                            Console.WriteLine("Errors: " + errors);
                            _logger.LogInformation("DeletePersonData stored procedure executed in " + sw.Elapsed.ToString()); 
}

So I have 5 parallel jobs running and calling this stored procedure to delete data, but for some reason procedures are being executed one by one.

I can confirm that behaviour by running this script where I see blockings:

WITH cteBL (session_id, blocking_these) AS
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, *
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

I have also try to execute stored procedure with: await _appContext.Database.ExecuteSqlRawAsync("EXEC DeletePersonData @PersonIds, @Errors OUT", new[] { tvpParam, errorParam }); but no luck – same result.

I also put MultipleActiveResultSets=True in connection string, and I checked Azure SQL database select [value] from sys.database_scoped_configurations where name = 'MAXDOP' is 8 – which I think is enough for this. I don’t know what am I doing wrong and how can I speed this up.

Thanks