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.
 
 
Search WWH ::




Custom Search