Database Reference
In-Depth Information
■
Note
We will talk about extended events in Chapter 28, “extended events.”
Summary
The process of troubleshooting blocking issues in a system requires you to detect queries involved in the blocking,
find the root cause of the problem, and address the issue.
The
sys.dm_tran_locks
data management view provides you with information about all of the active lock
requests in the system. It can help you detect blocking situations in real time. You can join this view with other DMVs,
such as
sys.dm_exec_requests
,
sys.dm_exec_query_stats
,
sys.dm_exec_sessions
, and
sys.dm_os_waiting_tasks
to obtain more information about the sessions and queries involved in the blocking conditions.
SQL Server can generate a
blocking process report
that provides you with information about blocking, which
you can collect and retain for further analysis. You can use SQL Traces, Extended Events, and Event Notifications to
capture it.
In most cases, blocking occurs due to excessive scans introduced by nonoptimized queries. You should analyze
the execution plans of both blocking and blocked queries, and detect and optimize inefficiencies.
Another common issue that results in blocking is incorrect transaction management in the code, which includes
run-away transactions and interaction with users in the middle of open transactions among other things.