I have a Stored Procedure that I have written that deletes rows from a table in the database. When I will run the Stored Procedure in the Production environment it will run whilst another program is inserting rows into the same table. In case there is a deadlock I want my Stored Procedure to be the one that is killed.
In the Stored Procedure I have set DEADLOCK_PRIORITY to LOW.
When doing testing in my test environment I am finding that having this option set it makes the deletion run a lot slower. In the testing environment the process that inserts records is not running so, in theory, there is no locking issue.
I am finding that when the DEADLOCK_PRIORITY is set to LOW it is taking 1.5 hours to delete ~500,000 rows. When the DEADLOCK_PRIORITY is not set (ie using the default setting) the deletion of the rows only takes around 15 minutes.
Is there a reason why there is such a difference?
The database is using Simple Recovery model in the test environment. (In the Production environment it is using the FULL recovery model).