Finding blocking/locking queries

Findingblocking/locking queries in MS SQL (mssql)

 

Source:

How to identify blocking in SQL Server 2005 and2008

....

 Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock. This forces the second connection to be blocked until the first connection completes.

 

There are number of ways to find out the details of the system processes IDs (spids) involved in blocking. I have tried to cover some of the options in this tip to include:

• sp_who2 System Stored Procedure
• sys.dm_exec_requests DMV
• Sys.dm_os_waiting_tasks
• SQL Server Management Studio Activity Monitor
• SQL Server Management Studio Reports
• SQL Server Profiler
  

 

Source:SQL Server 2008 R2: Unlock the Locks



Block Busting
SQL Server Activity Monitor can help you identify locking and blocking issues. Closely watch the Wait Time, Wait Type, Wait Resource and Blocked By values for listed processes.
Most of the Activity Monitor process information comes from the following dynamic management views:

• sys.dm_os_tasks
• sys.dm_os_waiting_tasks
• sys.dm_exec_requests
• sys.dm_exec_sessions
• sys.dm_resource_governor_workload_group
  

You can get a clearer picture of locking and blocking with the sys.dm_tran_locks view. This returns information about active lock requests that have been granted or are waiting to be granted. The sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests give you information about active connections, sessions and requests, respectively.
Zero in on sys.dm_exec_requests (described in detail in the MSDN Library). A request with a "sleeping" status has completed execution, and is likely awaiting a command from the application. A request with a "running" or "runnable" status is currently processing a query. A request with a "suspended" status is waiting for a lock, latch or other event.
The wait_type column, as the name implies, returns the type of wait. If the value is greater than zero, the SPID is currently waiting. Look to the wait_time and wait_resource columns for more information. If the request is blocked, wait_time shows the duration in milliseconds. The wait_resource shows the resource for which the SPID is waiting. Note also that blocking_session_id shows the ID of the session that is blocking the request, or a negative value with information about the blocking resource owner.