Hey, this seems harmless.
Seriously, don’t SET DEADLOCK_PRIORITY HIGH.
Even setting it to low can be a bad idea. I’ve heard people say, “Well, I’ve got a big process that runs nightly, but if it gets in anybody’s way, I would rather just kill my process than slow someone else down.” Keep in mind that SQL Server is still doing all that work until it hits the deadlock – and if we’re talking about an hours-long process, there can still be hours of work involved in rolling back your process to let someone else win the deadlock.
When you find yourself going near the DEADLOCK_PRIORITY option, take a step back and question every decision that brought you to this point.
9 Comments. Leave new
Now, I will now never forget this concept, only because of this brilliant meme 🙂
No easy button???
You mean we actually have to work?
lol
I think that there is a situation where setting the deadlock priority does make sense. We have a process that is refreshing databases in a lower lifecycle from a prod backup. After the database is restored, there are updates needed before the environment is ready for use.
Since we cannot get the application to be shut down when this automated process runs, and until the refresh is complete then the environment is really offline, we want to make sure that the refresh completes at the expense of everything else. So we set the deadlock priority to high for all of the updates in the refresh process.
Honestly, if people can’t keep their mitts off of the lifecycle while we are reloading the databases, then they deserve to have their process killed. 🙂
If collision with user queries are the issue, I’d set the database to RESTRICTED_USER or maybe rename it to something else during the restore and subsequent updates before I messed with deadlock priority. Deadlock priority also won’t help you with user queries blocking those post-restore updates you’re speaking of.
Now THAT was funny. I’d charge you for the new keyboard I’m going to have to buy after spewing coffee on it but it was SO worth the laugh! Nicely done!
Hahaha, live to serve.
DEADLOCK PRIORITY is one of those setting that if you think you need to touch it, you should probably leave it well alone.
We are always setting DEADLOCK_PRIORITY to LOW on initial connection in every application which supports retry logic on 1205 error in the DAL layer.
I find the sysops missunderstanding of the feature quite hilarious frankly. Bet you never thought there can be a reason for its existence which sounds logical :))