Hiya,
We use a scheduled clean-up task to delete data from a SQL Server 2019 table when it has become obsolete. During high loads, we regularly run into time-outs in a second process which is attempting to insert data into the same table.
In the SQL Profiler screenshot below, you can see clean-up process 73 performing a ‘SELECT FOR DELETE’ statement. That uses the UPDLOCK hint, which result in a lock escalation on (a page beneath) the IN_ICM_EVENT table .
Process 69 (which is actually a second rules service) attempts to do an insert into that same table, but times out after 10 seconds due to the lock held by the clean-up.
We know the data being deleted is not going to be updated (or even selected) by any other process, so we don’t need the lock held by the clean-up process.
Does anyone know if there is any way to force the rules engine to forego that lock?
Best answer by robert.hellinx
View original
Sign up for the USoft Community