SQLServer Locking and deadlocks

In SQLServer environments with a lot of concurrency, locks, blocks and deadlocks are a very common issue.

To avoid negative performance impact, here are some best practices and monitoring tools to use:

1) Use of WITH (NOLOCK)


Where applicable, use WITH (NOLOCK) in the FROM-clause and in JOIN. It avoids locks on the database while performing read-operations.
Even when retrieving a value for values of local parameters inside a stored procedure, use the WITH (NOLOCK) statement.

Example:

SELECT x.a
, y.b
, …
FROM dbo.MyTableX AS x WITH (NOLOCK)
INNER JOIN dbo.MyTableY As y WITH (NOLOCK)
ON …..

The NOLOCK keyword instructs SQL Server not to wait until any exclusive locks are gone. This improves concurrency on the database but, a resultset might contain data that have not been committed.

In rare cases we can encounter this scenario:
Query session A starts to change data for a in table X. Milliseconds later session B starts to read these data with the WITH (NOLOCK) option. Data, as changed by session A are shown to user B. But then user A cancels his action and the transaction is rolled back. At this point user B can see data on his screen that have not been committed.
To avoid problems with uncommitted data (dirty reads) we should not use the WITH (NOLOCK) option when retrieving data that will be used in calculations and batches or on reports.

2) Always check that the columns in your WHERE-clause are covered by an index.


If your statement takes more than 1 second, use the sp_lock command in SQL Server to check on exclusive locks (X)

3) Use of Cursors

Do not use cursors in Stored Procedures. They are too expensive for the DB performance. But sometimes are cursors required.

4) Keep transactions short

Put only the strictly necessary commands in the same transaction, and split up all commands in as much transactions as possible!

5) During transactions, don't allow any user input. Collect it before the transaction begins.

6) Execute inserts/ updates on the same tables in the same order to avoid deadlocks.

7) Choose your clustered index wisely:

a. Choose a very narrow index which is very unique

b. If the clustered index contains more then 1 column, place them in the right “fine graining” order.

c. Avoid lock escalations during the update

8) Avoid page splits:

a. Use a correct fillfactor
b. Again choose your clustered index wisely!!!

9) Use the right monitoring tools when necessary:

a. Profiler traces (DeadLocks + DeadLock chains + Deadlock Graph, lock timeouts).

b. Trace flags: To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data).

DBCC TRACEON (3605,1204,-1)

Be sure to turn off this trace flag when you are done, as this trace can eat up SQL Server's resources unnecessarily, hurting performance.

10) Change the default deadlock behavior when appropriate

You can this by using the following command:

SET DEADLOCK_PRIORITY { LOW NORMAL @deadlock_var }

WHERE:

Low tells SQL Server that the current session should be the preferred deadlock victim, not the session that incurs the least amount of rollback resources. The standard deadlock error message 1205 is returned.

Normal tells SQL Server to use the default deadlock method.

@deadlock_var is a character variable specifying which deadlock method you want to use. Specify "3" for low, or "6" for normal.

This command is set a runtime for a specified user connection