1057
Chapter 47: Managing Transactions, Locking, and Blocking
47
Monitoring Locking and Blocking
SQL Server provides many different avenues for viewing and investigating locking prob-
lems. Locking may be investigated through sp_lock, sys.dm_tran_locks, SQL Profi ler,
PerfMon, and Extended Events, just to name a few.Viewing Blocking with Management Studio Reports
With Management Studio, transaction information for a server or database may be seen
using the Standard Reports, available from the server or database context menu, which
pull data from the dynamic management views. The transaction-related reports include All
Transactions, All Blocking Transactions (shown in Figure 47-2), Top Transactions by Age,
Top Transactions by Blocked Transaction Count, Top Transactions by Lock Count, Resource
Locking by Object, and User Statistics.FIGURE 47-2
Management Studio’s All Blocking Transactions Report is a quick way to view key transaction
locking and blocking information.Viewing Blocking with Activity Monitor
Activity Monitor (see Figure 47-3) includes some useful bits of information that you can use
to identify performance issues inside of SQL Server, including locking and blocking infor-
mation. It’s available on the toolbar and in the Object Explorer’s server context menu.c47.indd 1057c47.indd 1057 7/31/2012 10:23:28 AM7/31/2012 10:23:28 AM
http://www.it-ebooks.info