Defining and Setting Deadlock Priority

Dave Bland explains how you can use DEADLOCK_PRIORITY to control which process gets rolled back:

Before getting into how to set the DEADLOCK_PRIORITY, let’s quickly go over what the options are.  There are two ways to set the priority of a process. The first option is to use one of the keywords, LOW, NORMAL or HIGH. The second approach is to use a numeric value between -10 and 10.  The lowest value will be chosen as the victim.  For example, LOW will be the victim of the other process is HIGH and -1 will be the victim if the other process is greater than -1.

As I recall, index operations (like rebuilds) are automatically set to a low priority.

Related Posts

Detecting and Analyzing Deadlocks

Max Vernon has a couple scripts to analyze deadlocks in SQL Server: Deadlock detection and analysis in SQL Server is important for the overall health of affected applications. This post shows how to setup an Extended Events session to capture deadlock details. It also provides a stored procedure to capture details from the Extended Events […]

Read More

Rewriting Expensive Updates

Erik Darling takes us through an experiment: Let’s also say that bad query is taking part in a modification. UPDATE u2SET u2.Reputation *= 2FROM Users AS uJOIN dbo.Users AS u2ON CHARINDEX(u.DisplayName, u2.DisplayName) > 0WHERE u2.Reputation >= 100000;AND u.Id <> u2.Id; This query will run for so long that we’ll get sick of waiting for it. […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

May 2019
MTWTFSS
« Apr Jun »
 12345
6789101112
13141516171819
20212223242526
2728293031