Setting deadlock priority in SQL Server

Introduction

Sometimes critical jobs might terminated because they faced a deadlock situation and was choosen as the deadlock victim by SQL Server. The other process which was allowed to continue may be a stored procedure call from a web page, and a failure on that would have impacted a single user's session. But the failed job might impact all users of the application. In these scenarios it is ideal to run the critical jobs with a higher deadlock priority so that they are not chosen as deadlock victims.

DEADLOCK_PRIORITY

We can set this connection property to appropriate level and SQL Server will terminate one with lowest priority. This feature is available from SQL Server 2005 onwards.

By setting the deadlock priority to high we can prevent our script being terminated as deadlock victim as shown below.

SET DEADLOCK_PRIORITY HIGH

-- Your script here

SET DEADLOCK PRIORITY NORMAL

You can give a numeric priority from -10 to 10 as well incase you want to set different priorities for a set of scripts which are expected to execute simultaneously with a chance of deadlock. 

Please refer to http://technet.microsoft.com/en-us/library/ms186736.aspx for more details.