3

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).

Thanks.

  • 4
    Recovery model is irrelevant here as DELETE is always fully logged. You should check yor WAITS while execute your delete in both cases (see sys.dm_os_waiting_tasks ) – sepupic Jan 15 '19 at 9:04
0

Going to guess here... transaction log file sizes

In the test system was small and grew when you tested the first time with LOW
When you re-run without LOW, then log file was already sized correctly.

Easy to verify: check the "lastwaittype" in sys.sysprocesses (deprecated but useful) to see if you gave LOG related waits affecting the SPID doing the DELETE.

Otherwise double check that the 2 executions are identical

  • same data
  • any triggers enabled
  • ditto foreign keys, indexes
  • foreign key cascades
  • ...

Also see Why does an UPDATE take much longer than a SELECT?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.