SQL Server Performance – Does Setting DEADLOCK_PRIORITY to LOW Slow Down Stored Procedures?

deadlocksql serversql-server-2012stored-procedures

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.

Best Answer

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?

Related Question