Skip to main content

Set (customize) deadlock priority for a specific session (script/TSQL/stored procedure) in SQL Server…


By default, when a deadlock occurs, SQL Server rolls back the least expensive (which will take minimum time and resource to rollback) session. However this may not be a desirable situation in some specific case. You may have to set priority of a specific transaction (session) in a way that it will not rollback even if it is least expensive to rollback in a deadlock situation.

To satisfy this requirement you can use SET DEADLOCK_PRIORITY option in your code as per below examples:

Example to set deadlock priority to HIGH/LOW or NORMAL:

-- Declaration and script components before SET statements
SET DEADLOCK_PRIORITY HIGH; -- Replace HIGH with NORMAL or LOW to set it to NORMAL or LOW
GO
--Script continues…
 
Example to set deadlock priority using numeric priority:
 
-- Declaration and script components before SET statements
SET DEADLOCK_PRIORITY 5; -- Replace 5 with any value from -10 to 10 as per prioroty
GO

--Script continues…


Please refer below for explanation of the deadlock priority options with this code:

LOW:  

a. The current session
will be the deadlock victim if it is involved in a deadlock and other sessions involved in the deadlock chain have deadlock priority set to either NORMAL or HIGH or to an integer value greater than -5.
b. The current session
will not be the deadlock victim if the other sessions have deadlock priority set to an integer value less than -5.
c. The current session is
eligible to be the deadlock victim if another session has set deadlock priority set to LOW or to an integer value equal to -5. (If both of the involved session’s deadlock priority is LOW, then the lease expensive session to rollback till be deadlock victim)

NORMAL: NORMAL is the default priority for SQL Server.
           
a. The current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to HIGH or to an integer value greater than 0, but will not be the deadlock victim if the other sessions have deadlock priority set to LOW or to an integer value less than 0.
b. The current session is
eligible to be the deadlock victim if another other session has set deadlock priority to NORMAL or to an integer value equal to 0. (If both of the involved session’s deadlock priority is NORMAL, then the lease expensive session to rollback till be deadlock victim. This is default behavior of SQL Server)

HIGH:

  1. The current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to an integer value greater than 5, or is eligible to be the deadlock victim if another session has also set deadlock priority to HIGH or to an integer value equal to 5.

numeric-priority:
It is an integer value range (-10 to 10) to provide 21 levels of deadlock priority. It specifies that the current session will be the deadlock victim if other sessions in the deadlock chain are running at a higher deadlock priority value, but will not be the deadlock victim if the other sessions are running at a deadlock priority value lower than the value of the current session.
It also specifies that the current session is eligible to be the deadlock victim if another session is running with a deadlock priority value that is the same as the current session.

For more details and passing this priority as a variable values, you may refer to http://msdn.microsoft.com/en-us/library/ms186736.aspx

Comments

Popular posts from this blog

How to kill a negative SPID (like SPID -2) in SQL Server?

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error: Msg 6101, Level 16, State 1, Line 1 Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048 This is because of an orphaned distributed transaction ID.  Please follow below steps to kill it: Step 1: -- Find the UOW Number select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number> --  <SPID Number>  is -2 most likely. Step 2: -- Copy the UOW number from Step one KILL ‘<UOW Number>’ This will kill the negative SPID resolving the issue.  However please note following points: 1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release

DMV/TSQL to find out basic hardware information of the SQL Server including when SQL Server started.

Please use below code: However, please be advised that it can not tell correct information around virtualization.  For example, it will show Hypervisor even if SQL runs on a physical OS where Hyper-V is on. So use this query only when you do not have sufficient access on underlying Windows Operating system to get these information directly. -- Basic hardware information for SQL Server (sys.dm_os_sys_info) /* This query is courtesy of https://sqlserverperformance.wordpress.com/. All credits goes to original author. */ SELECT cpu_count AS [Logical CPU Count] , scheduler_count , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , physical_memory_kb / 1024 AS [Physical Memory (MB)] , committed_kb / 1024 AS [Committed Memory (MB)] , committed_target_kb / 1024 AS [Committed Target Memory (MB)] , max_workers_count AS [Max Workers Count] , affinity_type_desc AS [Affinity Type] , sqlserver_start_time AS [

‘Trace Skipped Records’ – What is this and how to resolve it while using SQL Server Profiles?

In some very rare case, you may experience a very weired message in profiler’s output as ‘Trace Skipped Records’ while you trace something on SQL Server. Screenshot of similer situation is as below: This is not an error but it comes by design of SQL Server (I believe so). When you are using SQL profiler and return data is too big to fit in the GUI (for me, it is an enormous xml), SQL Server simply prints this message and proceed to next step. Mostlikely this is to save server’s memory and performance. Although not suggested and guranteed, you can try to run a server side trace and dump data in a file which should capture all the data. However, it is strongly not recommended to run a trace on your production server from server side. Microsoft will probally document this limitation in future. More details may be found at https://connect.microsoft.com/SQLServer/feedback/details/304225/msft-edw-profiler-displays-trace-skipped-records-for-large-530kb-batch