Dealing with Locking, Blocking, and Deadlocking - Microsoft SQL Server 2012 Performance Tuning - page 389

To prevent/minimize deadlock issues as much as possible, here are some precautions that
you can take while developing your code:
F Make sure that your transactions are as small as possible.
F Try to use lower-level isolation, as a lower level of isolation increases the data
concurrency.
F When possible, you can use the NOLOCK query hint to minimize blocking.
F Normalize your database design properly, so that appropriate related data is
distributed between multiple tables through relationships.
F Create an index on the required columns, so that tables don't have to be scanned.
Whole table scans can also increase the locking issues.
F Access database objects in your transaction in the same order as everywhere else in
your application.
Detecting deadlocks with Trace Flag 1204
In the previous recipe, Detecting deadlocks with SQL Server Profiler, , we learned to detect
deadlocks using SQL Server Profiler. This can be useful when deadlocks occur regularly in a
specific pattern and you are able to reproduce them by executing certain part of application
code that you know produces the deadlock. For this type of investigation, you simply start an
SQL Trace session, reproduce the deadlock condition, and analyze the queries.
However, when deadlocks occur irregularly, without any specific pattern, it becomes hard
for you to investigate them because you do not know in which case they occur. Thus, it also
becomes difficult for you to reproduce them. To troubleshoot such irregular deadlocks, you
might prefer not to keep a trace session running and wait for deadlocks to occur for hours.
This recipe will show you how you can configure SQL Server so that whenever deadlocks
occur, SQL Server logs the deadlock-related information into the SQL Server error log. Once
the server is configured in this way and someone comes to you and complains about any
deadlock occurrence, you can simply analyze the error log to investigate whether any
deadlock occurred in the database and if so, where.
Getting ready
In this example, we will learn how to configure SQL Server by setting TRACE Flag 1204 at
instance level.
We will be using the same code that was used in the previous recipe, Detecting deadlocks
with SQL Server Profiler, , to produce a deadlock condition. So, the prerequisites are the same
as those for the previous recipe.