Database Reference
In-Depth Information
As you see, there is a clustered index scan involved. Now you have enough data for analysis. Select queries
scanned the entire table. Because both processes were using READ COMMITTED transaction isolation level, the
queries tried to acquire shared (S) lock on every row from the table and, at some point, were blocked by the exclusive
(X) locks held by another session. It did not matter that those rows did not have the CustomerId that the queries were
looking for. In order to evaluate this predicate, queries had to read those rows, which required acquiring shared (S)
locks on them.
The best way to solve this deadlock situation is by adding a non-clustered index on the CustomerID column,
as shown in Listing 19-8. This would eliminate the clustered index scan and replace it with an Index Seek operator,
as shown in Figure 19-19 .
Listing 19-8. Adding non-clustered index
create nonclustered index IDX_Orders_CustomerID on Delivery.Orders(CustomerId)
Figure 19-19. Execution plan for the query with non-clustered index
Instead of acquiring the shared (S) lock on every row of the table, the query would read only the rows that belong
to a specific customer. It would dramatically reduce the number of shared (S) locks that need to be acquired, and it
would prevent the query from being blocked by the other sessions that held exclusive (X) lock on the rows that belong
to different customers.
In some cases you can have intra-query parallelism deadlocks—when the query that runs with parallelism
deadlocks itself—but those cases are rare and usually introduced by a bug in SQL Server rather than application
or database issues. You can detect those cases when a deadlock graph has more than two processes with the same
SPID and resource-list has exchangeEvent and/or threadPoll listed as the resources without any lock resources
associated with them. When it happens, you can work around the problem by reducing degree of parallelism for the
query with MAXDOP hint. There is also the chance that the issue has already been fixed in the latest service pack or
cumulative update.
Here are the other methods that can be used to obtain the deadlock graph:
Trace Flag 1222: This trace flag saves deadlock information to SQL Server Error Log files.
It is a perfectly safe method to use in production. We can enable it for all sessions with DBCC
TRACEON(1222,-1) command or by using startup parameter -T1222
You can create event notification that fires when deadlock occurs
xml_deadlock_report extended event
Capturing
system_health extended event session. That session is enabled by default and
captures deadlock information. This could be a great place to start troubleshooting if no other
collection methods are enabled.
Examining
 
Search WWH ::




Custom Search