Microsoft® SQL Server® 2012 Bible

1059

Chapter 47: Managing Transactions, Locking, and Blocking

47

FIGURE 47-4

SQL Server Profi ler can monitor and display the blocked and blocking code in XML.

This means that the server checks every second for blocked statements, and for any statement

that has been blocked for longer than 1 second, the blocked process report event fi res. Due to

when the last check was done, a statement may be blocked for more than the threshold value

before it is reported. This can be seen by setting the threshold to a large value, say 10 seconds.

The result is a complete XML-formatted disclosure of the blocked and blocking process

(refer to Figure 47-4). Saving this trace to a fi le and analyzing it in total is an excellent

locking and blocking debugging technique.

Querying Locks with the DMVs

The dynamic management view sys.dm_exec_requests reports several interesting facts

about current executing sessions, including the blocking session id:

SELECT session_id, blocking_session_id

FROM sys.dm_exec_requests

WHERE blocking_session_id > 0

c47.indd 1059c47.indd 1059 7/31/2012 10:23:29 AM7/31/2012 10:23:29 AM

http://www.it-ebooks.info