Reducing SQL Server Locks from SQL Server Performance

1. Use appropriate locking level

you can override how SQL Server performs locking on a table by using the SP_INDEXOPTION command. Below is an example of code you can run to tell SQL Server to use page locking, not row locks, for a specific table:

SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO
This code turns off both row and page locking for the table, thus only table locking is available.

2. Keep all Transact-SQL transactions as short as possible.
3. An often overlooked cause of locking is an I/O bottleneck.
4. To help reduce the amount of time tables are locked, which hurts concurrency and performance, avoid interleaving reads and database changes within the same transaction.
5. Any conditional logic, variable assignment, and other related preliminary setup should be done outside of transactions
6. Encapsulate all transactions within stored procedures
7. If you have a client application that needs to "check-out" data


for more information in detail please review:

SET DEADLOCK_PRIORITY (Transact-SQL)

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | | @deadlock_var | @deadlock_intvar }

::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }


LOW

Specifies that 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. The current session will not be the deadlock victim if the other sessions have deadlock priority set to an integer value less than -5. It also specifies that 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.

NORMAL

Specifies that 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. It also specifies that 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. NORMAL is the default priority.

HIGH

Specifies that 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.



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. LOW maps to -5, NORMAL to 0, and HIGH to 5.

@deadlock_var

Is a character variable specifying the deadlock priority. The variable must be set to a value of 'LOW', 'NORMAL' or 'HIGH'. The variable must be large enough to hold the entire string.

@deadlock_intvar

Is an integer variable specifying the deadlock priority. The variable must be set to an integer value in the range (-10 to 10).


Remarks

Deadlocks arise when two sessions are both waiting for access to resources locked by the other. When an instance of SQL Server detects that two sessions are deadlocked, it resolves the deadlock by choosing one of the sessions as a deadlock victim. The current transaction of the victim is rolled back and deadlock error message 1205 is returned to the client. This releases all of the locks held by that session, allowing the other session to proceed.

Which session is chosen as the deadlock victim depends on each session's deadlock priority:

* If both sessions have the same deadlock priority, the instance of SQL Server chooses the session that is less expensive to roll back as the deadlock victim. For example, if both sessions have set their deadlock priority to HIGH, the instance will choose as a victim the session it estimates is less costly to roll back.
* If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim.

SET DEADLOCK_PRIORITY is set at execute or run time and not at parse time.

DECLARE @deadlock_var NCHAR(3);
SET @deadlock_var = N'LOW';

SET DEADLOCK_PRIORITY @deadlock_var;
GO


for more detail :

Understanding sp_who SQL Server procedure status

sp_who Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.


1. dormant. SQL Server is resetting the session.
2. running. The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches.
3. background. The session is running a background task, such as deadlock detection.
4. rollback. The session has a transaction rollback in process.
5. pending. The session is waiting for a worker thread to become available.
6. runnable. The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.
7. spinloop. The session's task is waiting for a spinlock to become free.
8. suspended. The session is waiting for an event, such as I/O, to complete.